Switch scenario, period and value members when looping data buffer cells

[gkeushkerian]

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. 

[ChrisLoran]

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

[JackLacava · Accepted solution]

Basically in every loop you are:

1. retrieving a very thin databuffer
2. storing it into a new place
3. clearing the buffer

This is memory-light (good) but network-intensive (bad).

An alternative could be:

1. retrieving the buffer
2. 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.