Join Framework table data to Application table data using SQL

[Tom_R]

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.

[MarcR]

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

[Tom_R]

Hi Marc,

What did you use to join the two tables?

Thanks,

[ckattookaran]

You can use LINQ to join two datatables.

[MarcR]

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?

[Tom_R]

Hi Celvin,

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

Thanks,

[ckattookaran]

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]

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’))

[Krishna]

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

[Sai_Maganti]

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

Best

[NickKroppe]

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

[bilbrandon]

Hi, Nick.

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

Thanks,

Bil

[ckattookaran]

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

[ckattookaran]

[Krishna]

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

[adykes]

Hi Krishna, thanks for your response, it is very helpful. I am new to Dashboard Data Set BRs and am wondering how would you go about calling this function in a Data Adapter? Thanks

[Krishna]

Hi Adykes - here we go

{BRName}{Datasetname}{Name1=Value1, Name2=[Value2]} and if you do not have any parameter make it as {}

Hope this helps.

[adykes]

Thank you so much! And thank you for the fast response as well. 

[Krishna]

[adykes]

Sorry, I have another question: In your code, you refer to "JStatus", but I am trying to connect the dots back to the database tables and JStatus does not seem to be a column in the Journal Header application table. FYI, I am trying to use this code to join the SecUser table with the DataEntryAuditSource application table, so I am tweaking the code slightly to fit my purposes, hence the question 🙂 

[Krishna]

The Jstatus see below which is the Journalstatus column in the DB Tables JournalHeader and I am loading into the Datable of JSTATUS. Make Sense ?

ASQL.AppendLine("Select Case When JournalStatus = 4 Then 'Posted' Else 'Unposted' End AS JStatus,
PostedUserID From JournalHeader")

[adykes]

I understand, thank you!

[JackLacava]

An easy and high-level approach to problems like this is discussed in a recent blog post: https://community.onestreamsoftware.com/t5/Blog/The-Dutiful-Dead-or-A-Tale-Of-Mixed-DataSets/ba-p/15...