Skip to main content
N
NAS
August 28, 2023
Solved

Change derived data to stored

  • August 28, 2023
  • 2 replies
  • 0 views

Hi, I am loading periodic data and in some periods there are derived amounts (due to no periodic data for some periods).  The YTD values appear as derived data.  However, the parent entity isn't rolling up correctly because the parent is not including the derived amounts of the children. 

What is the most straightforward / best method to include the child's derived value in the parent value for the entity to correct this?  (I am ok with storing the children's derived values, but not sure of best method for that, either.)YTD Balance.PNG

Thank you! 

Best answer by MarcusH

From my experience, you have 2 options: either rectify the problem in Staging or the Cube.

Staging: this will depend on the source data. If that imports the Cube dimension members (ie all the transformation rules are * to *), then you could potentially write a derivative rule that creates a record with amount = 0 for all the intersections that are in the previous period but not the current. If the dimension members need transforming, then it is more tricky as you need to reference transformed data and create a new target data record.

Cube: have a calculation that checks the derived status (api.Data.GetDataCell("POV").CellStatus.IsDerivedData) and sets the value to be 0 if necessary. You can restrict the check to Origin Import.

Staging I think is probably the way to go if the source data does not need transforming. If that is not the case I think Cube is probably safer and certainly easier.

2 replies

M
MarcusHAnswer
Expert
August 29, 2023

From my experience, you have 2 options: either rectify the problem in Staging or the Cube.

Staging: this will depend on the source data. If that imports the Cube dimension members (ie all the transformation rules are * to *), then you could potentially write a derivative rule that creates a record with amount = 0 for all the intersections that are in the previous period but not the current. If the dimension members need transforming, then it is more tricky as you need to reference transformed data and create a new target data record.

Cube: have a calculation that checks the derived status (api.Data.GetDataCell("POV").CellStatus.IsDerivedData) and sets the value to be 0 if necessary. You can restrict the check to Origin Import.

Staging I think is probably the way to go if the source data does not need transforming. If that is not the case I think Cube is probably safer and certainly easier.

    N
    NASAuthor
    August 30, 2023

    Thanks so much, Marcus!  In light of the need to create a lot of zeros, I am exploring either stage or possibly a BI Blend solution which (hopefully) will eliminate the need to store zeros.  I appreciate your input.

    Terms & ConditionsAccessibility statement