cancel
Showing results for 
Search instead for 
Did you mean: 

Join Framework table data to Application table data using SQL

Tom_R
New Contributor

SOURCE: ONESTREAM CHAMPIONS

Hi - I am trying to create a journal entry summary report, and am able to get most of my data from the application tables, however the created by, submitted by, etc user data are all just the unique identifiers, and not actual user names. Currently I only know of user names being kept in the Framework database location, in the SecUser table (or similar ones).

Is there specific syntax I can use that will allow me to read from that database location (Framework) while running an SQL query capturing the rest of my data in the application database location through a dashboard data adapter? Or does this need to be done using a business rule to combine two tables?

Thanks.

14 REPLIES 14

MarcR
VIP

Hi Tom,

I tried this last month but OneStream confirmed that this is not possible in the current Azure Cloud. I first run the query on the framework to a datatable and then join that one with the application table. It’s a 2 step approach but works fine.

Marc

Marc Roest
OneStream consultant @Finext

Tom_R
New Contributor

Hi Marc,

What did you use to join the two tables?

Thanks,

You can use LINQ to join two datatables.

Hi Tom, I actually have a really small dataset from the application (i select the scheduled tasks from the task scheduler) and then loop over these results in a Datatable. For every line (i have only 😎 i run a query on the Framework with the item from the application and append that to a DataTable.
However, i cannot imagine that there is no way to join two datatables.

Curious what LINQ means that Celvin states. @Celvin, do you have some more information?

Marc Roest
OneStream consultant @Finext

Tom_R
New Contributor

Hi Celvin,

I have never used LINQ. Can you please provide an example of how it would work?

Thanks,

The code below joins two data tables using a single column.
ancDT table got the Name and Description of a member.
prcDT got the Process time information of the member (who processed the entity, when it was done, et al.)

Now to present the user with the Description of the member along with the process information the following code is used.

dim dt as new DataTable()
dt.Columns.Add("Code1")
dt.Columns.Add("ProcessTime")
dt.Columns.Add("ProcessBy")
dt.Columns.Add("Process")
dt.Columns.Add("WFScenarioName")
dt.Columns.Add("WFTimeName")
dt.Columns.Add("Description")

Dim result
' perform a left outer join using both tables
Dim query =
	From tl_ancDT In ancDT.AsEnumerable ' loop through table 1
	Group Join tl_prcDT In prcDT.AsEnumerable On tl_ancDT.Field(Of String)("Name") Equals tl_prcDT.Field(Of String)("Code1") Into tl_ancDT_tl_prcDT = Group 
	From tl_prcDT In tl_ancDT_tl_prcDT.DefaultIfEmpty() 
	Select New With {Key .Code1 = tl_ancDT.Field(Of String)("Name"),
		   Key .ProcessTime = If(tl_prcDT Is Nothing, DateTime.Parse("1/1/1900"), tl_prcDT.Field(Of DateTime)("ProcessTime")),
		   Key .ProcessBy = If(tl_prcDT Is Nothing, "Admin", tl_prcDT.Field(Of String)("ProcessBy")),
		   Key .Process = If(tl_prcDT Is Nothing, 0, tl_prcDT.Field(Of Boolean)("Process")),
		   Key .WFScenarioName = If(tl_prcDT Is Nothing,String.Empty , tl_prcDT.Field(Of String)("WFScenarioName")),
		   Key .WFTimeName = If(tl_prcDT Is Nothing,String.Empty , tl_prcDT.Field(Of String)("WFTimeName")),
		   Key .Description = tl_ancDT.Field(Of String)("Description")}

' add to return table	
For Each result In query
	If Not String.IsNullOrEmpty(result.Code1)
		If result.ProcessTime.Year > 1900
			dt.Rows.Add(result.Code1, result.ProcessTime, result.ProcessBy , 1, result.WFScenarioName, result.WFTimeName, result.Description)
		Else
			dt.Rows.Add(result.Code1, result.ProcessTime, result.ProcessBy , result.Process, result.WFScenarioName, result.WFTimeName, result.Description)
		End If	
	End If	
Next

kchampion
New Contributor II

Yes, this is possible through a Data Adapter. We have some People Planning reports that pull register data from XFW_PLP_Register for users based on their security groups - if the current logged in user is in a specific security group (Framework database) to access departments in a PLP workflow profile (Application database), then the report returns data that user has access to from XFW_PLP_Register.

Example - the following SQL in a Data Adapter (with Command Type = SQL and Database Location = Application) will pull all data in the XFW_PLP_Register application table if the user is assigned the Administrators security role in the Framework security tables:

Select * from XFW_PLP_Register a
WHERE
(’|Username|’ IN (Select SUA.Name
From OneStream_Framework.dbo.SecGroup SGA With (NOLOCK)
INNER Join OneStream_Framework.dbo.SecGroupChild SGCA With (NOLOCK) On SGA.UniqueID = SGCA.GroupKey
LEFT OUTER Join OneStream_Framework.dbo.SecUser SUA With (NOLOCK) On SGCA.ChildKey = SUA.UniqueID
WHERE SGA.Name = ‘Administrators’))

KrishnaS
New Contributor III

It is working in On-Premise environment not in Cloud. 

Sai_Maganti
Contributor

Have you checked if you are able to get this information from the Auditxx tables in the application database?

Best

NickKroppe
OneStream Employee
OneStream Employee

I would suggest putting the query against the application database in a dashboard dataset rule and then creating your own custom table on the fly with the correct usernames.  You can convert the user keys to user names by leveraging our ability to execute BRApis within the dashboard dataset rule, in this case, consider the BRApi.Security.Admin.GetUser function. This may perform better than performing multiple queries and intensive joins. 

There's an example of this exact concept in the rules chapter on pages 230-231 in the OneStream foundation handbook.

 

Regards,

 

Nick Kroppe

Hi, Nick.

Once the dashboard dataset rule is written, how do you assign it to the data adapter?

 

 

Thanks,

Bil

Use the BusinessRule method, and then the format is {BusinessRule}{FunctionName}{Parameter}

ckattookaran_0-1643407772006.png

 

KrishnaS
New Contributor III

The below is the code to join application and Framework Tables.

 

Select Case args.FunctionType

Case Is = DashboardDataSetFunctionType.GetDataSetNames
Dim names As New List(Of String)()
names.Add("MyDataSet")
Return names

Case Is = DashboardDataSetFunctionType.GetDataSet
If args.DataSetName.XFEqualsIgnoreCase("MyDataSet") Then

'Create a Join DT Table

Dim DT_FInal As New DataTable()
'
DT_FInal.Columns.Add("Name")
DT_FInal.Columns.Add("JStatus")


'FrameWork DB
Dim SQL As New Text.StringBuilder()
SQL.AppendLine("Select UniqueID, Name from SecUser")

Dim Fdt As New DataTable
Using DbConnApp As DbConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
Fdt = BRApi.Database.ExecuteSql(DbconnApp,sql.ToString,False)


'Application DB

Dim ASQL As New Text.StringBuilder()
ASQL.AppendLine("Select Case When JournalStatus = 4 Then 'Posted' Else 'Unposted' End AS JStatus,
PostedUserID From JournalHeader")
Dim Adt As New DataTable
Using ADbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
Adt = BRApi.Database.ExecuteSql(ADbConnApp,ASQL.ToString,False)


'LinQ Query
Dim aa
Dim Query = From fuser In Fdt.AsEnumerable
Join auser In Adt.AsEnumerable
On fuser.Field(Of system.Guid)("UniqueID") Equals auser.Field(Of system.Guid)("PostedUserID")
Select New With{Key .Name=fuser.Field(Of String)("Name"),
Key .JStatus=auser.Field(Of String)("JStatus")}
For Each aa In Query
DT_FInal.rows.Add(aa.Name,aa.JStatus)

Next
Return DT_FInal

End Using
End Using
End If

End Select