Forum Discussion

Keyser_Soze's avatar
Keyser_Soze
Contributor
2 years ago
Solved

Edit member properties in Bulk

I need to update the properties (Constraints/Texts) of 20.000 UD members based on transformation rules. 

The script runs in 10 seconds, but once I add the 'SaveMemberInfo' method it surpasses 2h ... I tried both 'MemberInfo' & 'WritableMemberNoCache' with no performance enhancement

Dim ListofU1Stores As List(Of MemberInfo) = BRApi.Finance.Metadata.GetMembersUsingFilter(si, dimensionName, myStoreFilter, True)
								
For Each oStore In ListofU1Stores
    'MemberInfo
    ''Dim myStore As MemberInfo = BRApi.Finance.Metadata.GetMember(si, DimType.UD1.Id, oStore.Member.MemberId, True)
    ''Dim StoreUDVMInfo As UDVMProperties = myStore.GetUDProperties()

    'WritableMember								
    Dim objWritableMember As WritableMember = BRApi.Finance.Members.ReadWritableMemberNoCache(si, DimType.UD1.Id, oStore.Member.Name)
    Dim objVaryingMemberProperties As VaryingMemberProperties = BRApi.Finance.Members.ReadMemberPropertiesNoCache(si, DimType.UD1.Id, oStore.Member.Name)
									
    Dim StoreUDVMInfo As UDVMProperties = objVaryingMemberProperties.GetUDProperties()   

    StoreUDVMInfo.Text1.SetStoredValue(ScenarioType.Unknown.Id, DimConstants.Unknown, text1ToAssign)
    StoreUDVMInfo.Text2.SetStoredValue(ScenarioType.Unknown.Id, DimConstants.Unknown, text2ToAssign)

    BRApi.Finance.MemberAdmin.SaveMemberInfo(si, True, objWritableMember, True, objVaryingMemberProperties, False, Nothing, TriStateBool.FalseValue)
										 
    'BRApi.Finance.MemberAdmin.SaveMemberInfo(si, myStore, True, True, False, TriStateBool.FalseValue)
Next oStore

Is there any faster way to modify properties in Bulk mode ?

Within method 'SaveMemberInfo', when I switch the booleans SaveMember to False and SaveProperties to True, the code is fast but values aren't saved

 

Metadata
  • NicolasArgente's avatar
    NicolasArgente
    2 years ago

    Hi Keyser_Soze !
    Updating members can be risky - but you already know that.
    Regarding the time it takes, it is normal as it is a Brapi and it will do 20 000 connections to the database.
    Now, for testing purposes you could test with Parralle for Each  and something like this : 

    Imports System.Threading.Tasks

' Assuming ListofU1Stores is a list or collection that supports parallel iteration
Parallel.ForEach(ListofU1Stores, Sub(oStore)
                                     ' Assume these API calls are thread-safe and can be called concurrently
                                     Dim objWritableMember As WritableMember = BRApi.Finance.Members.ReadWritableMemberNoCache(si, DimType.UD1.Id, oStore.Member.Name)
                                     Dim objVaryingMemberProperties As VaryingMemberProperties = BRApi.Finance.Members.ReadMemberPropertiesNoCache(si, DimType.UD1.Id, oStore.Member.Name)

                                     Dim StoreUDVMInfo As UDVMProperties = objVaryingMemberProperties.GetUDProperties()

                                     StoreUDVMInfo.Text1.SetStoredValue(ScenarioType.Unknown.Id, DimConstants.Unknown, text1ToAssign)
                                     StoreUDVMInfo.Text2.SetStoredValue(ScenarioType.Unknown.Id, DimConstants.Unknown, text2ToAssign)

                                     BRApi.Finance.MemberAdmin.SaveMemberInfo(si, True, objWritableMember, True, objVaryingMemberProperties, False, Nothing, TriStateBool.FalseValue)
                                 End Sub)

    I am not an expert of the drawbacks of parallel for each.
    Cheers

5 Replies

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    2 years ago

    Consider doing this in a Finance rule instead of an  extender so you're not depending on the BRApi:

    Imports System.Threading.Tasks

Namespace OneStream.BusinessRule.Finance.MemberPropertyUpdate
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
		    Try
		        Dim dimensionName As String = "whateverYourDimIsCalled"
		        Dim myStoreFilter As String = "whateverYourStorFilterIs"
		        Dim text1ToAssign As String = "WHateverYourText1Is"
		        Dim text2ToAssign As String = "WHateverYourText2Is"
		        Dim dimPk As DimPk = api.Dimensions.GetDim(dimensionName).DimPk
		        Dim ListofU1Stores As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(dimPk, myStoreFilter)

		        Dim options As New ParallelOptions()
		        options.MaxDegreeOfParallelism = Environment.ProcessorCount - 1

		        Parallel.ForEach(ListofU1Stores, options, Sub(oStore)
	                 oStore.Properties.GetUDProperties.Text1.SetStoredValue(ScenarioType.Unknown.Id, DimConstants.Unknown, text1ToAssign)
	                 oStore.Properties.GetUDProperties.Text2.SetStoredValue(ScenarioType.Unknown.Id, DimConstants.Unknown, text2ToAssign)
		        End Sub)

		        Return Nothing
		    Catch ex As Exception
		        Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
		    End Try
		End Function
	End Class
End Namespace

     

  • NicolasArgente's avatar
    NicolasArgente
    Valued Contributor
    2 years ago

    Hi Keyser_Soze !
    Updating members can be risky - but you already know that.
    Regarding the time it takes, it is normal as it is a Brapi and it will do 20 000 connections to the database.
    Now, for testing purposes you could test with Parralle for Each  and something like this : 

    Imports System.Threading.Tasks

' Assuming ListofU1Stores is a list or collection that supports parallel iteration
Parallel.ForEach(ListofU1Stores, Sub(oStore)
                                     ' Assume these API calls are thread-safe and can be called concurrently
                                     Dim objWritableMember As WritableMember = BRApi.Finance.Members.ReadWritableMemberNoCache(si, DimType.UD1.Id, oStore.Member.Name)
                                     Dim objVaryingMemberProperties As VaryingMemberProperties = BRApi.Finance.Members.ReadMemberPropertiesNoCache(si, DimType.UD1.Id, oStore.Member.Name)

                                     Dim StoreUDVMInfo As UDVMProperties = objVaryingMemberProperties.GetUDProperties()

                                     StoreUDVMInfo.Text1.SetStoredValue(ScenarioType.Unknown.Id, DimConstants.Unknown, text1ToAssign)
                                     StoreUDVMInfo.Text2.SetStoredValue(ScenarioType.Unknown.Id, DimConstants.Unknown, text2ToAssign)

                                     BRApi.Finance.MemberAdmin.SaveMemberInfo(si, True, objWritableMember, True, objVaryingMemberProperties, False, Nothing, TriStateBool.FalseValue)
                                 End Sub)

    I am not an expert of the drawbacks of parallel for each.
    Cheers

    • RobbSalzmann's avatar
      RobbSalzmann
      Valued Contributor II
      2 years ago

      NicolasArgente consider adding ParallelOptions and setting the degreesOfParallelism to 1 less than Environment.ProcessorCount to prevent monopolizing the CPU.

    • Keyser_Soze's avatar
      Keyser_Soze
      Contributor
      2 years ago

      Thanks for your time, I will definitely try this out !

      I did a very dumb attempt, and it worked magically. I switched the 'TriStateBool' => 'TrueValue' and 'SaveMember' => 'False, now from 2h I went down to 4min30 !

      I couldn't believe my eyes, so I'm still searching if there are no drawbacks to it. At first I thought it would be slower, since we're indicating with 'TriStateBool' that it's a newly added member. To my surprise, it works faster

      • NicolasArgente's avatar
        NicolasArgente
        Valued Contributor
        2 years ago

        Please can you ask a colleague to check on his UI what he sees after the update. I think I had a mis-adventure in the past with that. I think it was updating on my screen, but not saved. My colleague did not see the changes on his window 🙂
        Have fun 🙂