Switch scenario, period and value members when looping data buffer cells
Hi. I am trying to forecast OpEx accounts based on a seeding method. I am storing the method on a specific month by account and some UD dimensions and based on the selected method want to apply different math for those combinations.
The issue i am facing is that when looping through my original data buffer, where the methods are stored, i need to change the Scenario, Period and View member for the looped databuffer cell and then sotore that value. E.g. if the method stored in 2023M1 is 3MthAvg, i need to grab V#Trailing3MonthAvg:2023M1:S#Actual for each data unit that is being processed (All months for 2023 Budget scenario).
Dim driverDataBuffer As DataBuffer = Nothing
Dim calculatedBuffer As DataBuffer = Nothing
Dim destinationExpressionInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("V#Periodic:O#Import:U5#NoChannel:U6#NoCustomer:U7#NoProject:U8#None")
driverDataBuffer = api.Data.GetDataBufferUsingFormula( _
"FilterMembers(C#Local:" & firstOpenedPeriod & ":V#Periodic:O#Forms:I#None:U1#Data_Input:U5#NoChannel:U6#NoCustomer:U7#NoProject:U8#SeedingMethod,[A#TotOPEX.Base.Where(Text4 DoesNotContain plpAccount)],[F#InputCurrency_Total.Base])")
'Clear previously calculated durable data
api.Data.ClearCalculatedData(False, False, False, True, _
"A#TotOPEX.Base.Options(Cube=[Mgmt_Reporting_Cube], ScenarioType=Forecast, MergeMembersFromReferencedCubes=False).Where(Text4 DoesNotContain plpAccount)", _
"F#LC_Total.Base, F#InputCurrency_Total.Base, F#EndBal_Calc","O#Import",,,,,,,,,"U8#None")
For Each cell As DataBufferCell In driverDataBuffer.DataBufferCells.Values
Select Case cell.CellAmount
Case 101 '3 Mth Avg
calculatedBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#" & cell.DataBufferCellPk.GetAccountName(api) & ":S#ForecastInput:" & lastClosedPeriod & ":V#Trailing3MonthAvg:O#Top:I#None:U1#Data_Input:U2#" & cell.DataBufferCellPk.GetUD2Name(api) & ":U3#" & cell.DataBufferCellPk.GetUD3Name(api) & ":U4#" & cell.DataBufferCellPk.GetUD4Name(api) & ":U5#NoChannel:U6#NoCustomer:U7#NoProject:U8#None,[F#LC_Total.Base], [F#InputCurrency_Total.Base], [F#EndBal_Calc])")
Case 102 '6 Mth Avg
calculatedBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#" & cell.DataBufferCellPk.GetAccountName(api) & ":S#ForecastInput:" & lastClosedPeriod & ":V#Trailing6MonthAvg:O#Top:I#None:U1#Data_Input:U2#" & cell.DataBufferCellPk.GetUD2Name(api) & ":U3#" & cell.DataBufferCellPk.GetUD3Name(api) & ":U4#" & cell.DataBufferCellPk.GetUD4Name(api) & ":U5#NoChannel:U6#NoCustomer:U7#NoProject:U8#None,[F#LC_Total.Base], [F#InputCurrency_Total.Base], [F#EndBal_Calc])")
Case 103 '9 Mth Avg
calculatedBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#" & cell.DataBufferCellPk.GetAccountName(api) & ":S#ForecastInput:" & lastClosedPeriod & ":V#Trailing9MonthAvg:O#Top:I#None:U1#Data_Input:U2#" & cell.DataBufferCellPk.GetUD2Name(api) & ":U3#" & cell.DataBufferCellPk.GetUD3Name(api) & ":U4#" & cell.DataBufferCellPk.GetUD4Name(api) & ":U5#NoChannel:U6#NoCustomer:U7#NoProject:U8#None,[F#LC_Total.Base], [F#InputCurrency_Total.Base], [F#EndBal_Calc])")
Case 104 '12 Mth Avg
calculatedBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#" & cell.DataBufferCellPk.GetAccountName(api) & ":S#ForecastInput:" & lastClosedPeriod & ":V#Trailing12MonthAvg:O#Top:I#None:U1#Data_Input:U2#" & cell.DataBufferCellPk.GetUD2Name(api) & ":U3#" & cell.DataBufferCellPk.GetUD3Name(api) & ":U4#" & cell.DataBufferCellPk.GetUD4Name(api) & ":U5#NoChannel:U6#NoCustomer:U7#NoProject:U8#None,[F#LC_Total.Base], [F#InputCurrency_Total.Base], [F#EndBal_Calc])")
Case 201 'Prior Year
calculatedBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#" & cell.DataBufferCellPk.GetAccountName(api) & ":S#ForecastInput:T#" & priorYear & "M" & periodNum & ":V#Periodic:O#Top:I#None:U1#Data_Input:U2#" & cell.DataBufferCellPk.GetUD2Name(api) & ":U3#" & cell.DataBufferCellPk.GetUD3Name(api) & ":U4#" & cell.DataBufferCellPk.GetUD4Name(api) & ":U5#NoChannel:U6#NoCustomer:U7#NoProject:U8#None,[F#LC_Total.Base], [F#InputCurrency_Total.Base], [F#EndBal_Calc])")
End Select
'Commit calculatedBuffer
api.Data.SetDataBuffer(calculatedBuffer, destinationExpressionInfo,,, "O#Import",,,,,,,,,, True)
'Clear DataBuffer cell
calculatedBuffer.DataBufferCells.Clear
Next
The code i wrote is working as expected but i guess it could be written smarter. I am storing data in each of the iterations.
Basically in every loop you are:
- retrieving a very thin databuffer
- storing it into a new place
- clearing the buffer
This is memory-light (good) but network-intensive (bad).
An alternative could be:
- retrieving the buffer
- copying cells from there into a target buffer
... and then saving the buffer in one go at the end of the loop. This would be heavier in memory but much lighter on the network/database. To clarify, something like this:
Dim targetBuffer as new DataBuffer() For Each cell As DataBufferCell In driverDataBuffer.DataBufferCells.Values Select Case cell.CellAmount case 104 Dim tempBuf as DataBuffer = api.data.GetDataBufferUsingFormula("FilterMembers(....)") for each tempCell as DataBufferCell in tempBuf.DataBufferCells.Values targetBuffer.SetCell(si, new DataBufferCell(tempCell)) next ... next api.Data.SetDataBuffer(targetBuffer, destinationExpressionInfo,,, "O#Import",,,,,,,,,, True)Do your benchmarking, but I suspect this would be significantly faster.
