04-22-2023 10:42 PM - last edited on 05-02-2023 09:55 AM by JackLacava
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.
Solved! Go to Solution.
04-24-2023 05:01 AM - edited 04-24-2023 05:03 AM
Basically in every loop you are:
This is memory-light (good) but network-intensive (bad).
An alternative could be:
... 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.
04-24-2023 04:59 AM
You could use a dictionary object like this, at least for the first 4 cases:
Dim dctViewToUse As New Dictionary(Of Integer,String) From { _
{101,ViewMember.Trailing3MonthAvg.Name}, _
{102,ViewMember.Trailing6MonthAvg.Name}, _
{103,ViewMember.Trailing9MonthAvg.Name}, _
{104,ViewMember.Trailing12MonthAvg.Name} }
' -- enter loop here --
' for each ... etc..
Dim strViewMemberName As String = dctViewToUse(101)
' next
04-24-2023 05:01 AM - edited 04-24-2023 05:03 AM
Basically in every loop you are:
This is memory-light (good) but network-intensive (bad).
An alternative could be:
... 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.