Forum Discussion

alex's avatar
alex
New Contributor II
4 months 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 ups...
Data Integration
Data Sources
Transformation
Workflow
  • victortei's avatar
    victortei
    4 months 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
4 months 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
    4 months 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