03-15-2024 10:21 AM
Dear Team,
I'm trying to understand what caused the low performance of my code. I see that it's because of many loops, but I'm wondering if there's any possible way to fix it:
Dim ResultDataBuffer As DataBuffer = New DataBuffer()
Dim sAccounts As List(Of String) = New List(Of String) From {
"1711050",
"2742020",
"1712020",
"2745060",
"1346010",
"2413010",
"1742050",
"2716050",
"1745010",
"2713010",
"3221010",
"3221020",
"3221030",
"4451010",
"4451020",
"4451030",
"1722060",
"2741030",
"4541070",
"4321020",
"4541010",
"4541020",
"3222010",
"4452010",
"4541030",
"4541040",
"4541050",
"4541060",
"5111010",
"5111020"}
Dim mICFilter As List (Of Member) = api.Members.GetBaseMembers(api.Pov.EntityDim.DimPk,api.Members.GetMemberId(dimtype.entity.Id,"Top_member"))
For Each Acc As String In sAccounts
For Each Ic As Member In mICFilter
Dim DestinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("A#" & Acc & ":O#Forms:I#" & Ic.Name & ":UD1#None:UD2#None")
'Starting data buffer
Dim StartingDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(A#" & Acc & ":O#Forms:I#" & Ic.Name & ":UD1#None:UD2#None)")
For Each sourceCell As DataBufferCell In StartingDataBuffer.DataBufferCells.Values
Dim resultCell As New DataBufferCell(sourceCell)
resultcell.SetOrigin(api,"Forms")
resultcell.SetIC(api,Ic.Name)
resultcell.SetAccount(api,Acc)
'Calculate amount and set to is NoData = true
resultcell.CellAmount = 0
resultCell.CellStatus = DataCellStatus.CreateDataCellStatus(True, False)
'Set target member
ResultDataBuffer.SetCell(si,resultcell)
Next
'Save the Data Buffer using Result Data Buffer and Destination Info
api.Data.SetDataBuffer(ResultDataBuffer,DestinationInfo)
Next
Next
03-15-2024 10:53 AM - edited 03-15-2024 11:36 AM
Databuffers are made to be efficient in processing all the cells in one collection(the buffer). Create one databuffer will all your accounts and ICs in it instead of looping over the accounts and ICs one at a time.
This should get you close:
Dim ResultDataBuffer As DataBuffer = New DataBuffer()
Dim sAccounts As List(Of String) = New List(Of String) From {
"1711050",
"2742020",
"1712020",
"2745060",
"1346010",
"2413010",
"1742050",
"2716050",
"1745010",
"2713010",
"3221010",
"3221020",
"3221030",
"4451010",
"4451020",
"4451030",
"1722060",
"2741030",
"4541070",
"4321020",
"4541010",
"4541020",
"3222010",
"4452010",
"4541030",
"4541040",
"4541050",
"4541060",
"5111010",
"5111020"}
Dim accountsFilter As String = String.Join(",", sAccounts.Select(Function(account) $"A#{account}"))
Dim iCsFilter As String = String.Join(",", icMembers.Select(Function(ic) $"I#{ic}"))
Dim DestinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo($"Filtermembers(O#Forms:UD1#None:UD2#None,[{accountsFilter}],[I#Top_member].Base)")
Dim StartingDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula($"RemoveZeros(O#Forms:UD1#None:UD2#None,[{accountsFilter}],[I#Top_member].Base))")
For Each sourceCell As DataBufferCell In StartingDataBuffer.DataBufferCells.Values
Dim resultCell As New DataBufferCell(sourceCell)
resultcell.SetOrigin(api,"Forms")
resultcell.SetIC(api,Ic.Name)
resultcell.SetAccount(api,Acc)
resultcell.CellAmount = 0
resultCell.CellStatus = DataCellStatus.CreateDataCellStatus(True, False)
'Set target member
ResultDataBuffer.SetCell(si,resultcell)
Next
'Save the Data Buffer using Result Data Buffer and Destination Info
api.Data.SetDataBuffer(ResultDataBuffer,DestinationInfo)
03-15-2024 10:55 AM - edited 03-15-2024 11:13 AM
The immediate suggestion would be to use FilterMembers instead of looping through accounts and ICs:
Dim sourceBuffer as DataBuffer = api.Data.GetDataBufferUsingFormula( _
"FilterMembers(RemoveZero(A#All:O#Forms:U1#Whatever:etcetc), A#account1, A#account2, I#ic1, I#ic2, [etc etc]")
If you really want, you can then loop through the records in that buffer to set all cells to zero.
03-19-2024 03:18 AM
Hi, in addition to the above responses, I would also suggest to contemplate why there is data that needs to be set to NoData in the database in the first place. There may be good reasons for the need to do what you are trying to do here, but in most cases, this kind of data should e.g. be bypassed in the transformation rules, excluded from a copy routine (e.g. ACT to FC), or reclassified in order to provide a transparent and auditable data flow. Just deleting data may not always be in the best interest of the end user(s).
Also, as you know, calculated data data can be cleared with api.Data.ClearCalculatedData(), but here, O#Forms seems to be specified in order to remove data that has been manually entered (possibly?)? This may hint at changing other things such as adjusting the data access security so that users cannot enter data in unwanted combinations in the first place.
I am sure you have considered all of this already, so please also view this response in the light of the wider audience of this forum as I like to add words of caution for others reading this post to address their own solutions. 🙂