Showing results for 
Search instead for 
Did you mean: 

Seeding Actuals to Forecast for Extended dimension

New Contributor III

Hi ,

I am trying to write a statement similar to below in a Finance business rule. This copies all data for source scenario to target scenario for Base accounts under a specific node  and some additional filtering. This works fine when both Scenarios are of type Forecast. However for Actuals to Forecast it is not working and this is because our Actuals are extended and uses a deeper granularity than forecast type scenario ( which uses a summarized granularity). I have seen O/S snippets and article that we use a convert data buffer command to make this copy work , but unable to find the exact syntax which can help me to also filter accounts and other dimension as shown below.


api.Data.Calculate("S#" & Target & " = S#" & Source & ", V#Periodic" ,"A#GMACM.Base" ,,"O#BeforeAdj.Base","I#None",,,,,,,,,AddressOf onEvalDataBuffer,,True)



Going back to a simple example of seeding from an extended dimension:

Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo(String.Empty)
Dim sourceDataBuffer As DataBuffer = api.Data.GetDataBuffer(DataApiScriptMethodType.Calculate,"S#DetailedSourceScenario",destinationInfo)
Dim convertedDataBuffer As DataBuffer = api.Data.ConvertDataBufferExtendedMembers("AnotherCube", "AnotherScenario", sourceDataBuffer)
api.Data.SetDataBuffer(convertedDataBuffer, destinationInfo)

Onto your specific questions
Q1) How do I filter the source members (like the member filters in
A1)  You do it in the GetDataBuffer expression.  Remember that if you need to filter like U1#[XXX].Base then use the GetDataBufferUsingFormula along with a FilterMembers() . There are some examples in the Design & Reference Guide.

Q2) What's the equivalent of the onEvalDataBuffer that you get in ?
A2) You can simply do a for each loop in the DataBuffer that you opened up in the first line, and loop over the data buffer cells in exactly the same way as you would be doing in the onEvalDataBuffer argument.




New Contributor III

I followed the instructions above. I am trying to copy data from Actuals to 4_32_forecast scenario. Actual is extended to lower level detail. Forecast is higher level What am I doing wrong below


Dim startingBuffer As String = "S#Actual:I#None"
Dim filter1 As String = "[O#[Top]]"
Dim filter2 As String = "[A#[GMACM].base]"
Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo(String.Empty)
Dim sourceDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(" & startingBuffer & ", " & filter2 & ", " & filter2 & ")")

If Not sourceDataBuffer Is Nothing Then
'Use ConvertDataBufferExtendedMembers when copying from one scenario to another scenario that has extended members
Dim destDataBuffer As DataBuffer = api.Data.ConvertDataBufferExtendedMembers(api.Pov.Cube.Name, "4_32_Forecast", sourceDataBuffer)

For Each sourceCell As DataBufferCell In sourceDataBuffer.DataBufferCells.Values
If (Not sourceCell.CellStatus.IsNoData) Then
Dim resultCell As New DataBufferCell(sourceCell)
destDataBuffer.SetCell(api.DbConnApp.SI, resultCell)
End If
api.Data.SetDataBuffer(destDataBuffer, destinationInfo)
End If

End If
'End If 'Function is SeedForecast
End Select

Return Nothing

Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function


I can't tell you why the snippet won't work in your particular application since we don't have the time and resources to provide end-to-end consulting support (not without a Statement of Work anyway) and learn the context within the application, which may hold all the secrets to why this works or not.

However, I would propose...

a) Take out the filtering on O#Top.   It's not doing anything because you're going to include all Origin members from the source anyway. If you want to aggregate to a single target Origin member then set this on the ExpressionDestinationInfo and then use the aggregate option in the SetCell in the loop. ( SetCell(si, cell, Aggregate=True)

b) Have you done a LogDataBuffer, immediately after opening the source buffer?  Does it output any intersections?  

c) Does the destDatabuffer contain any cells , just before the SetDataBuffer? 

d) I would pay particular attention to any fixed common members in the source databuffer which may get substituted for the special member XFCommon. Also ensure the ExpressionDestinationInfo is referrred to when opening the source buffer (as an optional argument).


New Contributor II

I have done something similar and got it to work:

In this case, I extended horizontally so Actual is at a more granular level to my Forecast scenario.  It's 4 lines of code.

#1 I set my destination to write to O#Import

#2 I set my source buffer which is Actuals

#3 I create another buffer to do the conversion

#4 I write the converted buffer to my destination

I put this code into a Finance BR that I fire from a DM Sequence, so I don't need to specify the Forecast scenario in the target because OneStream will pick it up from my DM Step.

Dim destination As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("O#Import")

Dim sourceActualsBuffer As DataBuffer = api.Data.GetDataBuffer(DataApiScriptMethodType.Calculate, "Cb#PLANNING:S#Actual:O#BeforeAdj:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None",destination)

Dim summaryPlanBuffer As DataBuffer = api.Data.ConvertDataBufferExtendedMembers("PLANNING", "Actual", sourceActualsBuffer)

api.Data.SetDataBuffer(summaryPlanBuffer, destination)