Extender Auto Create Metadata from data warehouse.

[Shreyas22]

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.

[kakouchtim]

Hi,

I am lso interested about this. Thanks.

[ChristianW]

Yes, you can do it, with an event handler business rule. I will post a sample later today. 

[Shreyas22]

That will be great!!👍

[ChristianW · Accepted solution]

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

[Shreyas22]

Thanks for the quick response i will look into it.👍

[Shreyas22]

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
 

[ChristianW]

Oh ok, that is much simpler, I will post a solution later today.

[Shreyas22]

That will be best for me 🙂

[ChristianW]

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