Solved

How to include Member IDs in data export

Forum|Forum|1 year ago
March 17, 2025
4 replies
2 views

We are using an extender business rule to run FdxExecuteDataUnit and write the data (with dimensionality) to a file. The file generated seems to be a standard format with a column for each dimension. Does anyone have any ideas on how to include the Member IDs in the file export?

Thank you,

Denise

Extenders

Best answer by sameburn

Hi denisefockler

Here is an example where we extend the FDX result from the FDX Data Unit BRApi. In this example we derive EntityID from the member in the original output. I've added comments also.

				' This is just an example.  Please amend for your own purposes
				
				' Declare StringBuilder (for logging)
				Dim sb As New Text.StringBuilder				

				' Declare variables for FDX (swap out for your variables)
				Dim cubeName As String = "Equipment Division"
				Dim entityMemFilter As String = "E#NAE.Base"
				Dim consName As String = ConsMember.Local.Name
				Dim scenarioMemFilter As String = "S#Actual"
				' Drop DimToken prefix e.g. S#; so we can get Member ID
				Dim scenarioMem As String = scenarioMemFilter.Replace(StageConstants.MasterDimensionTokens.Scenario, String.Empty)
				Dim scenarioTypeId As Integer = BRApi.Finance.Scenario.GetScenarioType(si, ScenarioDimHelper.GetIdFromName(si, scenarioMem)).Id
				Dim timeMemFilter As String = "T#2025M1"
				Dim viewName As String = ViewMember.YTD.Name
				Dim suppressNoData As Boolean = True
				Dim filter As String = String.Empty
				Dim parallelQueryCount As Integer = 8
				Dim logStatistics As Boolean = False
					
				' Execute FDX Data Unit BRApi
				Dim dt As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si, cubeName, entityMemFilter, consName, scenarioTypeId, scenarioMemFilter, timeMemFilter, viewName, suppressNoData, filter, parallelQueryCount, logStatistics)				

				' Enter dimension name (that we want to get ID's for)
				Dim dimName = "Equipment"
				' Now we can use the DimName to get the DimPk for that dimension e.g. Entity (we can do the same steps for any dimension)
				Dim dimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, dimName)
				
				' Use this BRApi GetMembersUsingFilter (normally returns a list of MemberInfo) but we can return result as a Dictionary using linq lambda extension (Dictionaries are better for lookups)
				' Since dictionaries must have unique keys, it is safer to group the result and grab the first instance of that member
				' Remove duplicates boolean set to True on BRApi GetMembersUsingFilter
				Dim memDict As Dictionary(Of String, Integer) = BRApi.Finance.Members.GetMembersUsingFilter(si, dimPk, entityMemFilter, True, Nothing, Nothing).
																GroupBy(Function(lambda) lambda.Member.Name).
															    ToDictionary(Function(lambda) lambda.Key, Function(lambda) lambda.First().Member.MemberId)
				
				' Create new Datatable and clone existing structure (this clones columns, schema, etc from original datatable)
				' If you want to reorder columns in target DataTable, you can declare them manually below in the order you want instead of cloning the original structure
				' Row order is important below and must follow the same order as your columns
				Dim dtNew As DataTable = dt.Clone()
				' Add new derived column
				dtNew.Columns.Add("EntityId", GetType(Integer))

				' Loop original DataTable
				For Each dr As DataRow In dt.Rows()
					'Define rows
					Dim row As DataRow = dtNew.NewRow
					' New row = old row
					row("Cube") = dr("Cube")
					row("Entity") = dr("Entity")
					row("Parent") = dr("Parent")
					row("Cons") = dr("Cons")
					row("Scenario") = dr("Scenario")
					row("Time") = dr("Time")
					row("View") = dr("View")
					row("Account") = dr("Account")
					row("Flow") = dr("Flow")
					row("Origin") = dr("Origin")
					row("IC") = dr("IC")
					row("UD1") = dr("UD1")
					row("UD2") = dr("UD2")
					row("UD3") = dr("UD3")
					row("UD4") = dr("UD4")
					row("UD5") = dr("UD5")
					row("UD6") = dr("UD6")
					row("UD7") = dr("UD7")
					row("UD8") = dr("UD8")
					row("Amount") = dr("Amount")
					' Here is where we derive MemberId using dictionary
					' We set a default value of type Integer (same as MemberId)
					Dim memID As Integer = 0
					' We use TryGetValue (in case key does not exist)
					If memDict.TryGetValue(dr("Entity"), memID) Then
						row("EntityId") = memID 	
					End If
					' Add DataRow to new DataTable
					dtNew.Rows.Add(row)
				Next dr	
				
				' Log first 10 rows of your target Datatable (you can comment out the below code post-development))
				Dim rowCount As Integer = 0
				Dim maxRows As Integer = 10
				
				' Loop DataTable
				For Each row As DataRow In dtNew.Rows()
					
				    If rowCount >= maxRows Then
				        Exit For
				    End If					
									
					For Each col As DataColumn In dtNew.Columns()
						sb.AppendLine(String.Format("Col ➡ {0}, Row ➡ {1}", col.ColumnName, row(col)))
					Next col
					
					rowCount += 1
				Next row	
				
				' Don't forget to comment this out in Production!!!
				BRApi.ErrorLog.LogMessage(si, "FDX Log Updated Result", sb.ToString())

Hope this helps. All DataTable manipulation is standard .Net (so you can google it 🙂)

Sam

rhankey

I often use a CustomCalculate Package to export or extract larger amounts of Cube data, as it gives me far greater precision as to what I output and how. Run the CustomCalculate DM Package for the desired Data Units. For each Data Unit, fetch a DataBuffer of the Cube data to be exported, then cycle through the DataBuffer outputting whatever fields you like and in whatever format you want. If you need to export data from all the data units into a common data file or data table, you will want to output each Data Unit's data to a common Global variable with locking or bulk insert to a common local SQL table so that you don't have multiple parallel threads headbutting. Since you are invoking this from an Extender rule, you would have the Extender rule grab the final Global variable or SQL table and output it as desired. But you could just as easily launch all this from a Dashboard Data Adapter, or whatever you want.

denisefocklerAuthor

rhankey do you have any sample code you could include here? I have business rules today that use data buffers but they are always for performing calcs and not using them to export data to file. (I edited this reply to take out the question about the data unit. I forgot, if I call the BR through a DM Custom Calc then I can define the data unit there).

I am not an IT person. My skills are limited to taking what we have (and was built by integration partner), reusing code and extending it :)

Thank you

Denise

RobbSalzmann

Its strange that OS with their love of Guids, PKs, and Ids would only extract string names in FDX. I suspect its not a big ask for a feature to optionally include ID columns in the FDX library functions.

If you can wait a few more moments while your FDX is running, you could parse the result using a multi-threaded approach in an assembly or BR to add ID columns.

What is the use case for having the IDs in the resulting file? It could be that its better to derive IDs on consumption of the file instead of during construction. Just depends on where performance matters most.

denisefocklerAuthor

RobbSalzmann the specific use case here is exporting data from OneStream to stage in ACDP and use in Power BI. Because Fdx only extracts stored data there is a need to apply the OS hierarchy to the data (which we already have an extract of). We felt the best way to marry the data with the hierarchy was through the member IDs in case anything would ever be renamed. We can not use the new Power BI connector because we are not Saas and our organization does not want to make the investment needed to get the connector. I figured the Fdx would be the quickest way to get the data. Additionally, it is easy to use the function to define the data needed. I am not great with buffers and writing code but I can probably piece something together by copying from existing rules we have.

I appreciate your feedback and will consider above. Feel free to share any other ideas you may have.

Thank you

Denise

sameburn

Hi Denise

You can get a dictionary of any members you need to capture e g key = Name, value = MemberId. Then create a new DataTable and add additional columns as required

Then you can loop through your fdx DataTable and use the dictionary to reference the Member Ids so that you can derive the datarow values to pass into your new column e.g MemberID in your new DataTable

Using a dictionary shouldn't really impact performance from this extra step in your code. I have used this approach before to derive extra columns from fdx data

Hope this helps

Sam

denisefocklerAuthor

sameburn

would you have sample code of a business rule that would execute this sequence of steps or do you know of one in GolfStream?

Thank you

Denise

sameburnAnswer

Hi denisefockler

Here is an example where we extend the FDX result from the FDX Data Unit BRApi. In this example we derive EntityID from the member in the original output. I've added comments also.

				' This is just an example.  Please amend for your own purposes
				
				' Declare StringBuilder (for logging)
				Dim sb As New Text.StringBuilder				

				' Declare variables for FDX (swap out for your variables)
				Dim cubeName As String = "Equipment Division"
				Dim entityMemFilter As String = "E#NAE.Base"
				Dim consName As String = ConsMember.Local.Name
				Dim scenarioMemFilter As String = "S#Actual"
				' Drop DimToken prefix e.g. S#; so we can get Member ID
				Dim scenarioMem As String = scenarioMemFilter.Replace(StageConstants.MasterDimensionTokens.Scenario, String.Empty)
				Dim scenarioTypeId As Integer = BRApi.Finance.Scenario.GetScenarioType(si, ScenarioDimHelper.GetIdFromName(si, scenarioMem)).Id
				Dim timeMemFilter As String = "T#2025M1"
				Dim viewName As String = ViewMember.YTD.Name
				Dim suppressNoData As Boolean = True
				Dim filter As String = String.Empty
				Dim parallelQueryCount As Integer = 8
				Dim logStatistics As Boolean = False
					
				' Execute FDX Data Unit BRApi
				Dim dt As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si, cubeName, entityMemFilter, consName, scenarioTypeId, scenarioMemFilter, timeMemFilter, viewName, suppressNoData, filter, parallelQueryCount, logStatistics)				

				' Enter dimension name (that we want to get ID's for)
				Dim dimName = "Equipment"
				' Now we can use the DimName to get the DimPk for that dimension e.g. Entity (we can do the same steps for any dimension)
				Dim dimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, dimName)
				
				' Use this BRApi GetMembersUsingFilter (normally returns a list of MemberInfo) but we can return result as a Dictionary using linq lambda extension (Dictionaries are better for lookups)
				' Since dictionaries must have unique keys, it is safer to group the result and grab the first instance of that member
				' Remove duplicates boolean set to True on BRApi GetMembersUsingFilter
				Dim memDict As Dictionary(Of String, Integer) = BRApi.Finance.Members.GetMembersUsingFilter(si, dimPk, entityMemFilter, True, Nothing, Nothing).
																GroupBy(Function(lambda) lambda.Member.Name).
															    ToDictionary(Function(lambda) lambda.Key, Function(lambda) lambda.First().Member.MemberId)
				
				' Create new Datatable and clone existing structure (this clones columns, schema, etc from original datatable)
				' If you want to reorder columns in target DataTable, you can declare them manually below in the order you want instead of cloning the original structure
				' Row order is important below and must follow the same order as your columns
				Dim dtNew As DataTable = dt.Clone()
				' Add new derived column
				dtNew.Columns.Add("EntityId", GetType(Integer))

				' Loop original DataTable
				For Each dr As DataRow In dt.Rows()
					'Define rows
					Dim row As DataRow = dtNew.NewRow
					' New row = old row
					row("Cube") = dr("Cube")
					row("Entity") = dr("Entity")
					row("Parent") = dr("Parent")
					row("Cons") = dr("Cons")
					row("Scenario") = dr("Scenario")
					row("Time") = dr("Time")
					row("View") = dr("View")
					row("Account") = dr("Account")
					row("Flow") = dr("Flow")
					row("Origin") = dr("Origin")
					row("IC") = dr("IC")
					row("UD1") = dr("UD1")
					row("UD2") = dr("UD2")
					row("UD3") = dr("UD3")
					row("UD4") = dr("UD4")
					row("UD5") = dr("UD5")
					row("UD6") = dr("UD6")
					row("UD7") = dr("UD7")
					row("UD8") = dr("UD8")
					row("Amount") = dr("Amount")
					' Here is where we derive MemberId using dictionary
					' We set a default value of type Integer (same as MemberId)
					Dim memID As Integer = 0
					' We use TryGetValue (in case key does not exist)
					If memDict.TryGetValue(dr("Entity"), memID) Then
						row("EntityId") = memID 	
					End If
					' Add DataRow to new DataTable
					dtNew.Rows.Add(row)
				Next dr	
				
				' Log first 10 rows of your target Datatable (you can comment out the below code post-development))
				Dim rowCount As Integer = 0
				Dim maxRows As Integer = 10
				
				' Loop DataTable
				For Each row As DataRow In dtNew.Rows()
					
				    If rowCount >= maxRows Then
				        Exit For
				    End If					
									
					For Each col As DataColumn In dtNew.Columns()
						sb.AppendLine(String.Format("Col ➡ {0}, Row ➡ {1}", col.ColumnName, row(col)))
					Next col
					
					rowCount += 1
				Next row	
				
				' Don't forget to comment this out in Production!!!
				BRApi.ErrorLog.LogMessage(si, "FDX Log Updated Result", sb.ToString())

Hope this helps. All DataTable manipulation is standard .Net (so you can google it 🙂)

Sam

RobbSalzmann

Sam provides a good example for using a lookup to get the member id column you need. Here are a few things I might add:

Loop over the rows instead of hard coding them - this makes the code adaptable to changes in columns.
DataTables are mutable, insert the new column into the original instead of cloning to a new one.
Use separate functions and serialization for simplified logging of complex objects like datatables
- Hello OS, when will we get a rational logging framework?
Chained/nested functions are difficult to read and to troubleshoot/debug. Consider calling out objects explicitly to keep things clear. e.g. the section under "Get members using filter and create dictionary for fast lookup"
make building the memberId lookup a seperate, generic method, it sounds like the OP wants todo this for many columns.

Variables:

' Declare variables for FDX (swap out for your variables)
Dim cubeName As String = "Equipment Division"
Dim entityMemFilter As String = "E#NAE.Base"
Dim consName As String = ConsMember.Local.Name
Dim scenarioMemFilter As String = "S#Actual"
im scenarioTypeId As Integer = BRApi.Finance.Scenario.GetScenarioType(si, ScenarioDimHelper.GetIdFromName(si, scenarioMem)).Id
Dim timeMemFilter As String = "T#2025M1"
Dim viewName As String = ViewMember.YTD.Name
Dim suppressNoData As Boolean = True
Dim filter As String = String.Empty
Dim parallelQueryCount As Integer = 8
Dim logStatistics As Boolean = False

Core code:

Dim dt As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si, cubeName, entityMemFilter, consName, scenarioTypeId, scenarioMemFilter, timeMemFilter, viewName, suppressNoData, filter, parallelQueryCount, logStatistics)

' Enter dimension name to get DimPk
Dim dimName As String = "Equipment"
Dim dimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, dimName)

' Build the lookup table using the new method
Dim memberIdLookup As Dictionary(Of String, Integer) = BuildMemberIdLookups(entityMemFilter)

' Add new column to the original DataTable for EntityId
dt.Columns.Add("EntityId", GetType(Integer))

' Loop through the DataTable and populate the new column with looked up member ids
For Each dr As DataRow In dt.Rows()
    dr("EntityId") = LookUpMemberId(dr("Entity").ToString(), memberIdLookup)
Next

' Log the first 10 rows in a separate function for debugging purposes
' Comment this line out in production
DtLogDebug(si, dt, 10)

Helper functions:

' Method to build the member ID lookup table for any dimension
Private Function BuildMemberIdLookups(memberFilter as String) As Dictionary(Of String, Integer)
	Dim lookupMembers As List(Of MemberInfo) = BRApi.Finance.Members.GetMembersUsingFilter(si, dimPk, memberFilter, True, Nothing, Nothing)    
	Dim memberIdLookup As New Dictionary(Of String, Integer)()

    For Each memberInfo As MemberInfo In lookupMembers
        ' Check if the member name already exists in the dictionary to avoid duplicates
        If Not memberIdLookup.ContainsKey(memberInfo.Member.Name) Then
            memberIdLookup.Add(memberInfo.Member.Name, memberInfo.Member.MemberId)
        End If
    Next

    Return memberIdLookup
End Function

' Function to lookup member id from the dictionary
Private Function LookUpMemberId(memberName As String, memberIdLookup As Dictionary(Of String, Integer)) As Integer
    Dim memberId As Integer = -1
    If memberIdLookup.TryGetValue(memberName, memberId) Then
        Return memberId
    End If
    Return memberId ' Returns -1 if not found
End Function

' Function to log the first N rows for debugging
Private Sub DtLogDebug(si As SessionInfo, dt As DataTable, numRowsToLog As Integer)
    Dim rowsToLog = dt.AsEnumerable() _
                        .Take(numRowsToLog) _
                        .Select(Function(row) String.Join(", ", dt.Columns.Cast(Of DataColumn)() _
                                                          .Select(Function(col) $"{col.ColumnName}={row(col)}"))) _
                        .ToList() ' Convert to a List of flattened strings

   Dim serializedRows = Newtonsoft.Json.JsonConvert.SerializeObject(rowsToLog, Formatting.Indented)
   BRApi.ErrorLog.LogMessage(si, serializedRows)
End Sub

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded