How to include Member IDs in data export
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...
Forum Discussion
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 🙂)
BR
Sam
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 = FalseCore 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