Forum Discussion

royari's avatar
royari
Contributor
3 years ago

Seeding Actuals to Forecast for Extended dimension

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)

Finance
  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    3 years ago

    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).

     

  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    3 years ago

    Hello,
    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 api.data.calculate)
    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 api.data.calculate ?
    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.

     

    Regards

    Chris 

    • royari's avatar
      royari
      Contributor
      3 years ago

      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
      Next
      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

  • Jason's avatar
    Jason
    New Contributor II
    3 years ago

    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)