Create CSV with Parent/Child relationships across Extended Dimensions
I have recently had the request of creating a Dimensions CSV Export File with Parent/Child Relationships including some member properties. I realised this wasn’t straightforward when Extensibility is used across the dimensionality. Specially with the requirement of presenting the hierarchy with the correct Sort Order. See Example Below:
With the following set up:
The CSV Out Put should be:
I am sharing the solution we have implemented in case to help anyone with the same type of request/requirement. The attached Extender Business rule could be used in a Data Management Step as follows:
It will create CSV file within the User Temp Folder with all the Parent Child Relationships including some properties: Currency (Only For Entity Type Dim), Text1-Text8.
The solution will extract All Dimensions Type, including system dimensions. The code could be easily adapted to work of a Dimension Type List instead. Also, additional properties could be extracted as needed by modifying the Sub Routine GetHirerachyWithPropertiesDataTable.
Credits to Keith Berry (kberry) for providing me with the main core business rule of the solution as well as his help and guidance on this.
Here it is the full Extender Code:
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Namespace OneStream.BusinessRule.Extender.XF_ExportDimsWithProperties
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
'Returns All dimensions in parent/child format including Member Properties
'Fields include Parent, Child, Child Description, RowId, Generation Number, Text1-Text8
Dim sScenarioType As String = args.NameValuePairs.XFGetValue("ScenarioType", String.Empty)
Dim Time As String = args.NameValuePairs.XFGetValue("Time", String.Empty)
Dim ScenarioTypeId As Integer = ScenarioType.GetItem(sScenarioType).Id
Dim TimeId As Integer = BRApi.Finance.Members.GetMemberId(si,dimtypeId.Time,Time)
'Get parent/child Data Table with Required Properties:
Dim dtTotalDim As New DataTable
'Will Extract All Members across All Dimension Types:
Dim topMbr As String = "Root"
'Loop through all Dimensions Type:
For Each sDimType As DimType In DimType.GetAllDimTypes
Dim sDimList As List (Of [Dim]) = BRApi.Finance.Dim.GetDims(si,sDimType.Id)
'Process All Dims By DimType and saves a Data Table with Unique Parent/Child Relationships:
Dim ParentChildTable As New DataTable
For Each sDim As [Dim] In sDimList
Dim sDimTable As DataTable = Me.GetParentChildTable(si,api,sDim.name,sDimType.Id,topMbr)
ParentChildTable.Merge(sDimTable)
Next sDim
'Creates a Unique Parent/Child Data Table:
Dim UniqueParentChildTable As DataTable = ParentChildTable.DefaultView.ToTable(True, {"Parent","Child"})
'Process Relationships Data Table and re-orders members to get the right dimension Sort Order across extended dims:
Me.GetHirerachyWithPropertiesDataTable (si, api, sDimType.Id, topMbr,ScenarioTypeId,TimeId,UniqueParentChildTable,dtTotalDim)
Next sDimType
'Exports and saves in the User Temp Folder:
Me.ExportToCSV(si,globals,"ExportMetadata_AllDimTypes.csv",dtTotalDim)
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Private Function GetParentChildTable (si As SessionInfo, api As FinanceRulesApi, dimName As String, dimTypeId As Integer, topMbr As String) As DataTable
Try
'Initialize table
Dim dtDim As New DataTable
dtDim.Columns.Add("Parent", GetType(String))
dtDim.Columns.Add("Child", GetType(String))
dtDim.Columns.Add("DimName", GetType(String))
dtDim.Columns.Add("Description", GetType(String))
dtDim.Columns.Add("RowId", GetType(Integer))
dtDim.Columns.Add("Gen", GetType(Integer))
'Initialize stack variables
Dim stk As Stack(Of StkMbr) = New Stack(Of StkMbr)
Dim stkItem As StkMbr
Dim dimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, dimName)
Dim rowId As Integer = 1
'Push top member onto stack
Dim stkTop As New StkMbr
stkTop.Name = topMbr
stkTop.Parent = "<root>"
stkTop.Gen = 0
stk.push(stkTop)
'Get child members and place on stack until stack is empty
Do While stk.Count > 0
stkItem = stk.Pop
Dim mbrDesc As String = Brapi.finance.Members.GetMember(si,dimTypeId,stkItem.Name).Description
dtDim.Rows.Add(stkItem.Parent, stkItem.Name, dimName,mbrDesc,rowId,stkItem.Gen)
rowID= rowId + 1
'Get children of current member
Dim mbrId As Integer = BRapi.Finance.Members.GetMemberId(si, dimTypeId, stkItem.Name)
Dim mbrChildren As List(Of Member) = BRApi.Finance.Members.GetChildren(si,dimPk, mbrId, Nothing)
mbrChildren.Reverse
If Not mbrChildren Is Nothing Then
For Each child As Member In mbrChildren
Dim stkChild As New StkMbr
stkChild.Name = child.Name
stkChild.Parent = stkItem.Name
stkChild.Gen = stkItem.Gen + 1
stk.Push(stkChild)
Next
End If
Loop
Return dtDim
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Sub GetHirerachyWithPropertiesDataTable (si As SessionInfo, api As FinanceRulesApi, dimTypeId As Integer, topMbr As String, ScenarioTypeId As Integer, Timeid As Integer, ByVal UniqueParentChildTable As DataTable, ByRef DataTable As DataTable)
Try
'Initialize table
Dim dtDim As DataTable = DataTable
'Initialise Table and Add Colums if Empty:
If DataTable.Rows.Count.Equals(0) Then
dtDim.Columns.Add("SORT_ORDER", GetType(Integer))
dtDim.Columns.Add("DIMENSION", GetType(String))
dtDim.Columns.Add("MEMBER", GetType(String))
dtDim.Columns.Add("MEMBER_DESC", GetType(String))
dtDim.Columns.Add("BASE", GetType(Boolean))
dtDim.Columns.Add("PARENT", GetType(String))
dtDim.Columns.Add("CURRENCY", GetType(String))
dtDim.Columns.Add("Text1", GetType(String))
dtDim.Columns.Add("Text2", GetType(String))
dtDim.Columns.Add("Text3", GetType(String))
dtDim.Columns.Add("Text4", GetType(String))
dtDim.Columns.Add("Text5", GetType(String))
dtDim.Columns.Add("Text6", GetType(String))
dtDim.Columns.Add("Text7", GetType(String))
dtDim.Columns.Add("Text8", GetType(String))
End If
'Initialize stack variables
Dim stk As Stack(Of StkMbr) = New Stack(Of StkMbr)
Dim stkItem As StkMbr
Dim rowId As Integer = 1
Dim MembProperties As New Object
'Defines Member Properties Object based on DimType:
Select Case dimTypeId
Case DimType.Entity.Id
MembProperties = BRApi.Finance.Entity
Case DimType.Account.Id
MembProperties = BRApi.Finance.Account
Case DimType.Flow.Id
MembProperties = BRApi.Finance.Flow
Case DimType.UD1.Id, DimType.UD2.Id, DimType.UD3.Id, DimType.UD4.Id, DimType.UD5.Id, DimType.UD6.Id, DimType.UD7.Id, DimType.UD8.Id
MembProperties = BRApi.Finance.UD
Case DimType.Scenario.Id
MembProperties = BRApi.Finance.Scenario
Case Else
MembProperties = Nothing
End Select
Dim MemberDisplay As New memberDisplayOptions
Dim DimDisplay As New DimDisplayOptions
MemberDisplay.IncludeMemberDim = True
'Push top member onto stack
Dim stkTop As New StkMbr
stkTop.Name = topMbr
stkTop.Parent = "<root>"
stkTop.Gen = 0
stk.push(stkTop)
'Get child members and place on stack until stack is empty
Do While stk.Count > 0
stkItem = stk.Pop
Dim MembDimName As String = $"RootDim"
Dim Currency As String = ""
Dim mbrDesc As String = ""
Dim MembId As Integer = -1
Dim text1 = ""
Dim text2 = ""
Dim text3 = ""
Dim text4 = ""
Dim text5 = ""
Dim text6 = ""
Dim text7 = ""
Dim text8 = ""
'Pull member Properties to add into Table:
If Not stkItem.Name.XFEqualsIgnoreCase("Root") AndAlso Not stkItem.Name.XFEqualsIgnoreCase("EntityDefault") AndAlso Not stkItem.Name.XFEqualsIgnoreCase("UD1Default") AndAlso Not stkItem.Name.XFEqualsIgnoreCase("None") Then
mbrDesc = BRAPI.Finance.Members.GetMember(si, dimTypeId, stkItem.Name).Description
MembId = Brapi.Finance.Members.GetMember(si,dimTypeId,stkItem.Name).MemberId
MembDimName = Brapi.Finance.Members.GetMemberInfo(si,dimTypeId,stkItem.Name,True,Nothing,MemberDisplay).MemberDim.Name
If dimTypeId.equals(DimType.Entity.Id) Then
Currency = MembProperties.GetLocalCurrency(si,MembId).Name
End If
If Not MembProperties Is Nothing
If dimType.IsUDDimType(dimTypeId) Then
text1 = MembProperties.Text(si,dimTypeId,MembId,1,ScenarioTypeId,TimeId)
text2 = MembProperties.Text(si,dimTypeId,MembId,2,ScenarioTypeId,TimeId)
text3 = MembProperties.Text(si,dimTypeId,MembId,3,ScenarioTypeId,TimeId)
text4 = MembProperties.Text(si,dimTypeId,MembId,4,ScenarioTypeId,TimeId)
text5 = MembProperties.Text(si,dimTypeId,MembId,5,ScenarioTypeId,TimeId)
text6 = MembProperties.Text(si,dimTypeId,MembId,6,ScenarioTypeId,TimeId)
text7 = MembProperties.Text(si,dimTypeId,MembId,7,ScenarioTypeId,TimeId)
text8 = MembProperties.Text(si,dimTypeId,MembId,8,ScenarioTypeId,TimeId)
Else If dimType.Scenario.Id.Equals(dimTypeId)
text1 = MembProperties.Text(si,MembId,1)
text2 = MembProperties.Text(si,MembId,2)
text3 = MembProperties.Text(si,MembId,3)
text4 = MembProperties.Text(si,MembId,4)
text5 = MembProperties.Text(si,MembId,5)
text6 = MembProperties.Text(si,MembId,6)
text7 = MembProperties.Text(si,MembId,7)
text8 = MembProperties.Text(si,MembId,8)
Else
text1 = MembProperties.Text(si,MembId,1,ScenarioTypeId,TimeId)
text2 = MembProperties.Text(si,MembId,2,ScenarioTypeId,TimeId)
text3 = MembProperties.Text(si,MembId,3,ScenarioTypeId,TimeId)
text4 = MembProperties.Text(si,MembId,4,ScenarioTypeId,TimeId)
text5 = MembProperties.Text(si,MembId,5,ScenarioTypeId,TimeId)
text6 = MembProperties.Text(si,MembId,6,ScenarioTypeId,TimeId)
text7 = MembProperties.Text(si,MembId,7,ScenarioTypeId,TimeId)
text8 = MembProperties.Text(si,MembId,8,ScenarioTypeId,TimeId)
End If
End If
End If
Dim IsBase As Boolean = Not Me.isParentAllDims(si,dimTypeId,stkItem.Name)
'Adds into the Table:
dtDim.Rows.Add(rowID,MembDimName,stkItem.Name,mbrDesc,IsBase,stkItem.Parent,Currency,Text1,Text2,Text3,Text4,Text5,Text6,Text7,Text8)
rowID= rowId + 1
'Looks Up for Children:
Dim result() As DataRow = UniqueParentChildTable.Select("[Parent] = '" & stkItem.Name & "'")
If result.Count > 0 Then
'Loop through the result backwards to get the stack in the right order:
For i As Integer = result.Count - 1 To 0 Step -1
Dim stkChild As New StkMbr
stkChild.Name = result(i).Item("Child")
stkChild.Parent = result(i).Item("Parent")
stkChild.Gen = stkItem.Gen + 1
stk.Push(stkChild)
Next
End If
Loop
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Sub
'Data structure for stack
Private Structure StkMbr
Property Name As String
Property Parent As String
Property Gen As Integer
End Structure
Function isParentAllDims (ByVal si As SessionInfo, ByVal DimTypeId As Integer, ByVal MembName As String) As Boolean
Try
Dim MembId As Integer = BRApi.Finance.Members.GetMemberId(si,DimTypeId,MembName)
Dim IsParent As Boolean = False
For Each sDim In BRApi.Finance.Dim.GetDims(si,DimTypeId).Select(Function(x) x.DimPk).toList()
If Not IsParent
IsParent = BRApi.Finance.Members.HasChildren(si,sDim,MembId)
End If
Next sDim
Return IsParent
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Private Sub ExportToCSV (ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal FileName As String, ByVal DataTable As DataTable)
Try
'1. Declare target dims and DataTable
Dim csvContent As New Text.StringBuilder
Dim qualifier As String = StageConstants.ParserDefaults.DefaultQuoteCharacter
Dim delimiter As String = StageConstants.ParserDefaults.DefaultDelimiter
'Create DataTable
Dim dt As DataTable = DataTable
'2. If rows returned, create CSV file content
If Not dt Is Nothing Then
Dim csv As New Text.StringBuilder
'Declare string builder to store csv text
csv.AppendLine("")
csv.AppendLine("")
'Create column list from dt
Dim colList As New List(Of String)
For Each dc As DataColumn In dt.Columns
colList.Add(dc.ColumnName)
Next
'Write the column Definitions
Dim colDescs As New Text.StringBuilder
For Each colName As String In colList
Dim dc As DataColumn = dt.Columns(colName)
'Check to see if the column data type requires quotes
Dim quotesRequired As Boolean = False
If dc.ColumnName.Contains(delimiter) Then
quotesRequired = True
End If
If colDescs.Length > 0 Then
colDescs.Append(delimiter)
If quotesRequired Then colDescs.Append(qualifier)
colDescs.Append(dc.ColumnName)
If quotesRequired Then colDescs.Append(qualifier)
Else
If quotesRequired Then colDescs.Append(qualifier)
colDescs.Append(dc.ColumnName)
If quotesRequired Then colDescs.Append(qualifier)
End If
Next
csvContent.AppendLine(colDescs.ToString)
'Write the Data Rows
For Each dr As DataRow In dt.Rows
Dim rowVals As New Text.StringBuilder
For Each colName As String In colList
Dim dc As DataColumn = dt.Columns(colName)
Dim rowVal As String = dr(dc.Ordinal).ToString
'Check to see if the row data type requires quotes
Dim quotesRequired As Boolean = False
If rowVal.Contains(delimiter) Then
quotesRequired = True
End If
If rowVals.Length > 0 Then
rowVals.Append(delimiter)
If quotesRequired Then rowVals.Append(qualifier)
rowVals.Append(rowVal)
If quotesRequired Then rowVals.Append(qualifier)
Else
If quotesRequired Then rowVals.Append(qualifier)
rowVals.Append(rowVal)
If quotesRequired Then rowVals.Append(qualifier)
End If
Next
csvContent.AppendLine(rowVals.ToString)
Next
'3. Write File to User Temp Folder (to open from dashboard button)
' This folder is cleared when user session expires
Dim fileBytes As Byte() = Encoding.UTF8.GetBytes(csvContent.ToString)
BRApi.Utilities.SaveFileBytesToUserTempFolder(si, si.UserName, fileName, fileBytes)
'Add this text To button that calls Function. Navigation Action. Open File
'FileSourceType=Application, UrlOrFullFileName=[Internal/Users/|UserName|/Temp/Test.csv], OpenInXFPageIfPossible=False
End If
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Sub
End Class
End Namespace
