OneStream Community

mpavan · Tuesday

Hello Experts,

Is there a way to update 500+ member names in OneStream using XML file. Manually changing each member is time consuming there seems to be no functionality available with XML load?

Thank You,

Manoj

jwagner · Wednesday

I just had this use case come up recently. I wrote a business rule that renames members based on a member filter. I needed this rule to rename a number of members with a suffix/prefix due to extensibility in the account dimension. See code below. I have the code set up to be just base members of a top parent member, any member filter will work. (See line 43 for member Filter)

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Linq
Imports Microsoft.VisualBasic
Imports System.Windows.Forms
Imports OneStream.Shared.Common
Imports OneStream.Shared.Wcf
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Database
Imports OneStream.Stage.Engine
Imports OneStream.Stage.Database
Imports OneStream.Finance.Engine
Imports OneStream.Finance.Database

Namespace OneStream.BusinessRule.Extender.BulkRenameMembers
	Public Class MainClass
		Dim m_LogBuilder As StringBuilder = New StringBuilder()
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
			Try
		
							
				Dim dimName As String = "Dimension Name"
				Dim topMember As String = "Top Member in Dimension"
                Dim preSufFlag As String = "Prefix"
                Dim renameString As String = "PREFIX"
		
                Me.RenameBaseMembers(si, dimName, topMember)


				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
		
		Public Sub RenameBaseMembers(ByVal si As SessionInfo, dimName As String, topMember As String, preSufFlag As String, renameString As String)
			Dim dimPk = BRAPI.Finance.Dim.GetDimPk(si, dimName)
            'Modify below as needed for use case.
			Dim baseMemberLst = BRAPI.Finance.Members.GetMembersUsingFilter(si, dimPk, $"A#[{topMember}].Base.Remove(None)", True)
			Me.AddToLog(si, "Member Rename Process Triggered" & vbCrLf & $"Dimension Name: {dimName}" & vbCrLf & $"Top Member: {topMember}" & vbCrLf & $"Members to be Modified: {baseMemberLst.Count}")

			For Each mbrInfo As MemberInfo In baseMemberLst.AsEnumerable()
				Dim oldMbrName = mbrInfo.Member.Name
				Dim newMbrName As String
				If preSufFlag.XFContainsIgnoreCase("Prefix") Then 
					'Prefix all member names
					newMbrName = renameString & "_" & oldMbrName  
				Else
					'Suffix all member names
					newMbrName = oldMbrName & "_" & renameString
				End If
				Try
					BRAPI.Finance.MemberAdmin.RenameMember(si,dimPk, oldMbrName, newMbrName) ' Comment this out to test...
					AddToLog(si, $"Member Renamed." & vbCrLf & $"Old Member Name: {oldMbrName}" & vbCrLf & $"New Member Name: {newMbrName}")
				Catch ex As Exception
					AddToLog(si, $"Error Renaming Member - {oldMbrName}")
					BRAPI.ErrorLog.LogMessage(si, m_LogBuilder.ToString())
					Continue For
				End Try
				Next
			BRAPI.ErrorLog.LogMessage(si, m_LogBuilder.ToString())
		End Sub
		
		#Region "Logging"
		Public Sub AddToLog(ByVal si As SessionInfo, ByVal message As String)
			m_LogBuilder.AppendLine(message)
			m_LogBuilder.AppendLine("---------------------------------------")
		End Sub
		#End Region 
	End Class
End Namespace

View solution in original post

mpavan · Thursday

Thank you for the sample script. In our case there was no logic behind it and the changes are random. I had got the following script and it works. (I will be populating the Key Value Pairs in Excel and paste in script)

Dim dimName As String = "Toal_ProductDim"

Dim dimPk = BRAPI.Finance.Dim.GetDimPk(si, dimName)

' Initialize a dictionary to store key-value pairs

Dim keyValuePairs As New Dictionary(Of String, String)

' Add key-value pairs

keyValuePairs.Add("mj1", "TestU1")

keyValuePairs.Add("mj2", "TestU2")

keyValuePairs.Add("mj3", "TestU3")

For Each kvp As KeyValuePair(Of String, String) In keyValuePairs

BRAPI.Finance.MemberAdmin.RenameMember(si,dimPk, kvp.key, kvp.value)

BRApi.ErrorLog.LogMessage(si,kvp.Key & kvp.Value)

MarcusH · Wednesday

You can't rename members using the XML file because it will see the renamed member as a new member. You could write a Business Rule and use the function BRApi.Finance.MemberAdmin.RenameMember.

mpavan · Wednesday

Thank you for the suggestion!

a) I am assuming that Extensibility Rule is required here?

b) Is the following code is what you are suggesting or am I way off?

Dim ProductDim As String = "Total_Products" 	
     
Dim objDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, PRoductDim)

BRApi.Finance.MemberAdmin.RenameMember(si, objDimPk, memberName, newMemberName) '(Repeat this line for all memebrs to be renamed in Product Dim)

Dim SubProductDim As String = "Sub_Products" 	
     
Dim obj1DimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, SubProductDim)

BRApi.Finance.MemberAdmin.RenameMember(si, obj1DimPk, memberName, newMemberName) '(Repeat this line for all memebrs to be renamed in Sub Product dim)

Henning · Wednesday

Hi, may I ask for the exact reason to do this? Is this for a live customer, or during development?

Keep in mind that changing member names in the metadata does not change the member references e.g. in reports.

Sandy2024 · Wednesday

Hi Henning, this is for a live customer. The purpose of this change to shorten the member's name where possible

Henning · Wednesday

Thanks for the insight!

Doing this for that many members requires careful execution and rigorous testing in a development application before migration this to Prod. If the application has been set up as dynamic as possible, maybe you are lucky and renaming the members does not have that many knock on-effects.

Transformation Rules, Cube Views, Dashboards, Formulas, Business Rules and other items that reference any of those members will need to be automatically changed as well. If you really go through this exercise and were hopefully able to capture most items, this will need to be tested, probably in some parallel runs that also cover year end and the rollover to the next year.

This is potentially a big change effort - though admittedly I do not know anything about the customer and the solution.

mpavan · Wednesday

The member changes only refer to UD dimension and not been hard coded any where so that is not a concern. We do have to test for Data Loss in dev app to be sure once I can get the rule ready.

rhankey · Wednesday

If the members already contain data as it kind of sounds like is the case, then your options are as follows

Rename the members one at a time through the user interface, or
Write a business rule to do so using the BRApi.Finance/MemberAdmin.RenameMember() api call.

If these members have not been used, then an additional option is as follows:

Export the dim to XML
Perform the renaming in the XML file (be sure you do so in both the Members and the Relationships sections)
Select all members that are to be renamed in the dim, and select 'Remove Relationship' so they all land in Orphans
Import your XML file containing the renamed members
At some future point you can either manually deleted the Orphaned members one at a time, or write a DeleteOrphans business rule sometime.

jwagner · Wednesday