04-03-2023 10:00 PM - last edited on 05-02-2023 04:19 PM by JackLacava
Hi,
Has anyone updated and added new member in metadata by using extender rule by picking the member information from SQL table or any other data warehouse?
Table contains most of the information related member in OneStream and their member properties.
If anyone has done similar kind of implementation, please let me know.
Thanks.
Solved! Go to Solution.
04-05-2023 07:02 AM - edited 04-05-2023 07:17 AM
Here it is:
You need to create a TransformationEventHandler business rule.
In the main section you need to do the following:
'Evaluate the operation type in order to determine which sub-event is being processed
Select Case args.OperationName
Case Is = BREventOperationType.Transformation.ParseAndTrans.ProcessTransformationRules
CreateMemberWhileLoading(si, args)
End Select
And CreateMemberWhileLoading, it is hard coded for UD4:
It uses Attribute1 to locate the parent and Attribute2 for the description.
Sub CreateMemberWhileLoading(ByVal si As SessionInfo, ByVal args As TransformationEventHandlerArgs)
Try
'Check the before / after flag, we want to handle the AFTER event
If Not args.IsBeforeEvent Then
'Initialize the transformer
Dim objTransformer As Transformer = DirectCast(args.Inputs(0), Transformer)
'Create a lookup of existing UD4 Base Members
Dim iColIndex As Integer = (objTransformer.TransformerDimensions(StageConstants.MasterDimensionNames.UD4).DataTableColumnIndex) + _
StageConstants.TransformationColumnIncrements.Target
'Parent name is in attribute1
Dim iParentColIndex As Integer = (objTransformer.TransformerDimensions(StageConstants.MasterDimensionNames.Attribute1).DataTableColumnIndex)
' 'Parent descritption is in attribute2
' Dim iDescColIndex As Integer = (objTransformer.TransformerDimensions(StageConstants.MasterDimensionNames.Attribute2).DataTableColumnIndex)
Dim UD4DimId As Integer = BRApi.Finance.Dim.GetDimPk(si, "[You UD4 Dim's Name]").DimId
'Loop over all pages in the cache
If objTransformer.DataCache.Pages.Count > 0 Then
'Move to the first page
objTransformer.DataCache.MoveFirstPage(si)
For intPageNo As Integer = 0 To objTransformer.DataCache.Pages.Count - 1
'Set the current data page
objTransformer.DataCache.ActivatePage(si, intPageNo)
'Process each row in the data table on this page
For Each row As DataRow In objTransformer.DataCache.CurrentPage.PageDataTable.Rows
Dim rowMemberName As String = row(iColIndex)
' Dim rowDescriptionOfMember As String = row(iDescColIndex)
'Member
Dim newMemberToCreatePk As New MemberPk(dimtypeid.UD4, DimConstants.Unknown)
Dim newMemberToCreate = New Member(newMemberToCreatePk, rowMemberName, rowDescriptionOfMember, Ud4DimId)
'If it doesnt exist
If newMemberToCreate Is Nothing Then
'Get Parent Id and description
Dim rowParentName As String = row(iParentColIndex)
Dim rowParentID As String = BRApi.Finance.Members.GetMemberId(si, dimtypeid.UD4, rowParentName)
brapi.ErrorLog.LogMessage(si, $"{rowMemberName} {rowDescriptionOfMember} {rowParentName}")
'Create Member
Dim newMemberToCreateInfo = New MemberInfo(newMemberToCreate)
brapi.Finance.memberadmin.SaveMemberInfo(si, newMemberToCreateInfo, True,False,False, TriStateBool.TrueValue)
newMemberToCreate = BRApi.Finance.Members.ReadMemberNoCache(si, dimTypeId.UD4, rowMemberName)
'Create Parent Child Relationship
Dim relToCreatePk As New RelationshipPk(DimTypeId.UD4, rowParentID, newMemberToCreate.MemberId)
Dim relToCreate As New Relationship(relToCreatePk, Ud4DimId, RelationshipMovementType.InsertAsLastSibling, 1)
Dim relToCreateInfo As New RelationshipInfo(relToCreate, Nothing)
Dim relPostionOpt As New RelationshipPositionOptions(RelationshipMovementType.InsertAsLastSibling, 1)
brapi.Finance.MemberAdmin.SaveRelationshipInfo(si, relToCreateInfo, relPostionOpt)
End If
Next
Next
End If
End If
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Sub
As you can see, this requires some advanced understanding of how Onestream works, so please only use it if you know what to do and test it heavily before using it.
I copied it out of a larger project, and I couldn't test it, so I can't guaranty that it works, but it should give you enough information, to find a solution for your own requirements.
The code isn't optimized for large volumes data loads, you might need to tweak, it if you face performance problems.
I hope this helps and cheers
Christian
04-04-2023 07:26 AM
Hi,
I am lso interested about this. Thanks.
04-05-2023 05:37 AM
Yes, you can do it, with an event handler business rule. I will post a sample later today.
04-05-2023 05:39 AM - edited 04-05-2023 06:15 AM
That will be great!!👍
04-05-2023 07:02 AM - edited 04-05-2023 07:17 AM
Here it is:
You need to create a TransformationEventHandler business rule.
In the main section you need to do the following:
'Evaluate the operation type in order to determine which sub-event is being processed
Select Case args.OperationName
Case Is = BREventOperationType.Transformation.ParseAndTrans.ProcessTransformationRules
CreateMemberWhileLoading(si, args)
End Select
And CreateMemberWhileLoading, it is hard coded for UD4:
It uses Attribute1 to locate the parent and Attribute2 for the description.
Sub CreateMemberWhileLoading(ByVal si As SessionInfo, ByVal args As TransformationEventHandlerArgs)
Try
'Check the before / after flag, we want to handle the AFTER event
If Not args.IsBeforeEvent Then
'Initialize the transformer
Dim objTransformer As Transformer = DirectCast(args.Inputs(0), Transformer)
'Create a lookup of existing UD4 Base Members
Dim iColIndex As Integer = (objTransformer.TransformerDimensions(StageConstants.MasterDimensionNames.UD4).DataTableColumnIndex) + _
StageConstants.TransformationColumnIncrements.Target
'Parent name is in attribute1
Dim iParentColIndex As Integer = (objTransformer.TransformerDimensions(StageConstants.MasterDimensionNames.Attribute1).DataTableColumnIndex)
' 'Parent descritption is in attribute2
' Dim iDescColIndex As Integer = (objTransformer.TransformerDimensions(StageConstants.MasterDimensionNames.Attribute2).DataTableColumnIndex)
Dim UD4DimId As Integer = BRApi.Finance.Dim.GetDimPk(si, "[You UD4 Dim's Name]").DimId
'Loop over all pages in the cache
If objTransformer.DataCache.Pages.Count > 0 Then
'Move to the first page
objTransformer.DataCache.MoveFirstPage(si)
For intPageNo As Integer = 0 To objTransformer.DataCache.Pages.Count - 1
'Set the current data page
objTransformer.DataCache.ActivatePage(si, intPageNo)
'Process each row in the data table on this page
For Each row As DataRow In objTransformer.DataCache.CurrentPage.PageDataTable.Rows
Dim rowMemberName As String = row(iColIndex)
' Dim rowDescriptionOfMember As String = row(iDescColIndex)
'Member
Dim newMemberToCreatePk As New MemberPk(dimtypeid.UD4, DimConstants.Unknown)
Dim newMemberToCreate = New Member(newMemberToCreatePk, rowMemberName, rowDescriptionOfMember, Ud4DimId)
'If it doesnt exist
If newMemberToCreate Is Nothing Then
'Get Parent Id and description
Dim rowParentName As String = row(iParentColIndex)
Dim rowParentID As String = BRApi.Finance.Members.GetMemberId(si, dimtypeid.UD4, rowParentName)
brapi.ErrorLog.LogMessage(si, $"{rowMemberName} {rowDescriptionOfMember} {rowParentName}")
'Create Member
Dim newMemberToCreateInfo = New MemberInfo(newMemberToCreate)
brapi.Finance.memberadmin.SaveMemberInfo(si, newMemberToCreateInfo, True,False,False, TriStateBool.TrueValue)
newMemberToCreate = BRApi.Finance.Members.ReadMemberNoCache(si, dimTypeId.UD4, rowMemberName)
'Create Parent Child Relationship
Dim relToCreatePk As New RelationshipPk(DimTypeId.UD4, rowParentID, newMemberToCreate.MemberId)
Dim relToCreate As New Relationship(relToCreatePk, Ud4DimId, RelationshipMovementType.InsertAsLastSibling, 1)
Dim relToCreateInfo As New RelationshipInfo(relToCreate, Nothing)
Dim relPostionOpt As New RelationshipPositionOptions(RelationshipMovementType.InsertAsLastSibling, 1)
brapi.Finance.MemberAdmin.SaveRelationshipInfo(si, relToCreateInfo, relPostionOpt)
End If
Next
Next
End If
End If
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Sub
As you can see, this requires some advanced understanding of how Onestream works, so please only use it if you know what to do and test it heavily before using it.
I copied it out of a larger project, and I couldn't test it, so I can't guaranty that it works, but it should give you enough information, to find a solution for your own requirements.
The code isn't optimized for large volumes data loads, you might need to tweak, it if you face performance problems.
I hope this helps and cheers
Christian
04-05-2023 07:42 AM
Thanks for the quick response i will look into it.👍
04-05-2023 09:30 PM
Thanks for you response but it seems that the above script is for stage data lookup and update the metadata accordingly.
In the current situation the new/updated metadata (name/description/properties and so on) is sitting in application database in one of the SQL tables. I have to lookup and make the updating into dimensions from that table with various If conditions.
But that's for the current solution and by any chance if you have solution for updating metadata from custom table kindly share.
Thanks
04-06-2023 02:12 AM
Oh ok, that is much simpler, I will post a solution later today.
04-06-2023 05:15 AM
That will be best for me 🙂
04-06-2023 06:02 AM
Here it is, it is based on the other code and assumes, that the table is already loaded to the DataTable object:
Sub CreateMemberFromDatatable(ByVal si As SessionInfo, ByVal dimPkToLoad As DimPk, ByVal datatableWithDimInfo As DataTable)
Try
'Process each row in the data table on this page
For Each rowWithMemberInfo As DataRow In datatableWithDimInfo.Rows
Dim rowMemberName As String = rowWithMemberInfo("Child")
Dim rowDescriptionOfMember As String = rowWithMemberInfo("Description")
'Member
Dim newMemberToCreatePk As New MemberPk(dimPkToLoad.DimTypeId, DimConstants.Unknown)
Dim newMemberToCreate = New Member(newMemberToCreatePk, rowMemberName, rowDescriptionOfMember, dimPkToLoad.DimId)
'If it doesnt exist
If newMemberToCreate Is Nothing Then
'Get Parent Id and description
Dim rowParentName As String = rowWithMemberInfo("Parent")
Dim rowParentID As String = BRApi.Finance.Members.GetMemberId(si, dimPkToLoad.DimTypeId, rowParentName)
'Create Member
Dim newMemberToCreateInfo = New MemberInfo(newMemberToCreate)
brapi.Finance.memberadmin.SaveMemberInfo(si, newMemberToCreateInfo, True,False,False, TriStateBool.TrueValue)
newMemberToCreate = BRApi.Finance.Members.ReadMemberNoCache(si, dimPkToLoad.DimTypeId, rowMemberName)
'Create Parent Child Relationship
Dim relToCreatePk As New RelationshipPk(dimPkToLoad.DimTypeId, rowParentID, newMemberToCreate.MemberId)
Dim relToCreate As New Relationship(relToCreatePk, dimPkToLoad.DimId, RelationshipMovementType.InsertAsLastSibling, 1)
Dim relToCreateInfo As New RelationshipInfo(relToCreate, Nothing)
Dim relPostionOpt As New RelationshipPositionOptions(RelationshipMovementType.InsertAsLastSibling, 1)
brapi.Finance.MemberAdmin.SaveRelationshipInfo(si, relToCreateInfo, relPostionOpt)
End If
Next
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Sub