Forum Discussion

alex's avatar
alex
New Contributor II
15 days ago
Solved

Transformation Event Handler reviewing pre-transformed (stage) and post-transformation data?

Hi,

My colleagues and I are interested in reviewing pre-transformed data side-by-side with the records from post-transformation data, as we're looking to move our transformation process further upstream and we wanted to validate the accuracy of our new transformation process against OneStream's transformations. I've reviewed the examples in GolfStream in the transformation event handler and I understand how to review the stage data, but I'm unsure how to find the matching transactions after they get transformed.

Has anyone done anything similar before and would be able to share insights into how I can review the data at the validate and match them to the records in stage?

Thanks!

Alex

Data Integration
Data Sources
Transformation
Workflow
  • victortei's avatar
    victortei
    15 days ago

    Hey Alex, you can use a query to retrieve that information. The OS has a view called StageSourceAndTargetData that stores that info.

    Here's a sample query from our past project.

    Select w.profilename ,Si SourceId ,Et Entity_Source ,EtT Entity_Target ,CnT Consol ,VwT ViewDim ,SnT Scenario ,TmT Time ,Ac Account_Source ,AcT Account_Target ,Fw Flow_Source ,FwT Flow_Target ,OgT Origin ,Ic IC_Source ,IcT IC_Target ,U1 U1_Source ,U1T U1_Target ,U2 U2_Source ,U2T U2_Target ,U3 U3_Source ,U3T U3_Target ,U4 U4_Source ,U4T U4_Target ,U5 U5_Source ,U5T U5_Target ,U6 U6_Source ,U6T U6_Target ,U7 U7_Source ,U7T U7_Target ,ConvertedAmount Amount from vStageSourceAndTargetData v join workflowprofilehierarchy w on v.wfk = w.profilekey where 1=1 and lb= 'Forecast' and u4t = '1343' and RawAmount <> 0

    PS: You can run the query from a data adapter component and associate that in a grid view.

    Regards,

    Victor

  • victortei's avatar
    victortei
    New Contributor III
    15 days ago

    Hey Alex, you can use a query to retrieve that information. The OS has a view called StageSourceAndTargetData that stores that info.

    Here's a sample query from our past project.

    Select w.profilename ,Si SourceId ,Et Entity_Source ,EtT Entity_Target ,CnT Consol ,VwT ViewDim ,SnT Scenario ,TmT Time ,Ac Account_Source ,AcT Account_Target ,Fw Flow_Source ,FwT Flow_Target ,OgT Origin ,Ic IC_Source ,IcT IC_Target ,U1 U1_Source ,U1T U1_Target ,U2 U2_Source ,U2T U2_Target ,U3 U3_Source ,U3T U3_Target ,U4 U4_Source ,U4T U4_Target ,U5 U5_Source ,U5T U5_Target ,U6 U6_Source ,U6T U6_Target ,U7 U7_Source ,U7T U7_Target ,ConvertedAmount Amount from vStageSourceAndTargetData v join workflowprofilehierarchy w on v.wfk = w.profilekey where 1=1 and lb= 'Forecast' and u4t = '1343' and RawAmount <> 0

    PS: You can run the query from a data adapter component and associate that in a grid view.

    Regards,

    Victor

    • alex's avatar
      alex
      New Contributor II
      11 days ago

      Hi Victor, thanks! Your idea led me down the right path. :)

      If anyone's wondering, I loaded my validation data to attributes and I compared them to my post-transformation data using the view [vStageSourceAndTargetDataWithAttributes]. Based on Victor's tip I examined the other views in the system using

      SELECT *
      FROM sys.views

      to find the aforementioned view vStageSourceAndTargetDataWithAttributes, then I flipped through it quickly for a single workflow & time period by joining it with the data at [workflowprofilehierarchy] as Victor suggested. After that it was some relatively straightforward SQL to put everything into an object I could use for my testing & validation purposes.

      Thanks all!

      Alex

  • rhankey's avatar
    rhankey
    Contributor
    12 days ago

    Your options at the SQL level are (which you can incorporate into a Business Rule):

    • Joining SQL Tables StageSourceData (pre-transformed data) to StageTargetData (post-transformed data) and optionally StageAttributes if you were bringing in any Stage Attribute data,
    • vStageSourceAndTargetData or vStageSourceAndTargeDataWithAttributes if you would like to use prebuilt views that have done the joining for you.

    If you want to do it without SQL code, I usually reach for BRApi.Import.Data.ReadCombinedData().  This method is pre-filtered to a workflow you provide.

     

  • MarcusH's avatar
    MarcusH
    Valued Contributor
    12 days ago

    Hi Alex

    We have done something similar where we have a spreadsheet using a tableview. This takes a Cube View and adds the stage data for the account. It is only looking at the account dimension. The BR uses FdxExecuteCubeView to get the target data and structure of the report. In a separate datatable it uses the SQL View vStageSourceAndTargetData to get the stage and target information. It reads through the CubeView datatable and adds a column for controlling the row order of the result datatable (TargetOrder: simply increment a counter) and a second order column to specify it is Cube data (TargetOrSource: always set to 0). Then the stage datatable is read, the target account from the stage datatable looks up the row order from the CubeView datatable for the account (TargetOrder) and the stage record is then added to the result datatable with this row order ID and the second order column (TargetOrSource) is set to 1. The results are added to a new tableview which is sorted by TargetOrder and TargetOrSource. Then you add the tableview to a spreadsheet.

    I hope that makes sense.