The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
Solved
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.
2 Replies
- JackLacava
OneStream Employee
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.
ChrisLoranYou 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