03-05-2024 04:50 AM
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
Solved! Go to Solution.
03-05-2024 08:28 AM
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
03-05-2024 08:28 AM
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
03-05-2024 08:38 AM
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
03-05-2024 08:47 AM
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 🙂
03-05-2024 02:54 PM
@NicolasArgente consider adding ParallelOptions and setting the degreesOfParallelism to 1 less than Environment.ProcessorCount to prevent monopolizing the CPU.
03-05-2024 02:50 PM - edited 03-05-2024 02:52 PM
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