Dear All,
We have crises-cross transformation in the JDM.
Eg:- Fields have been transformed from Sales Header to Sales Line and vice versa.
The reporting requirement was of Summary and Transaction reports. The summary reports were drawn from Sales header and some fields required at the Summary level like Line Amount were taken from the Sales line table.
We need to ensure that all transformation field have been populated with the relevant data. Now we have written validation queries to check the table count from source to staging and from staging to DW,but we are not sure how to validate that the data has been populated in the transformation fields in both the tables?
2 comments
-
Jet Reports Historic Posts Official comment We decided to revamp the structure by taking the approach 2 as the cleansing rules failed for the incremental scenario in the criss-cross transformation approach.
-
Jet Reports Historic Posts We are considering the following approach:
o Incremental
Now we wish to implement the source based incremental approach based on timestamp but when we try to execute the same execution package with incremental approach on all the relevant tables the cleansing rule fails.
First we deploy all the tables in Full load mode and then we execute the package.
I believe this happens due to the execution package approach Sales header (Incr)->Sales Line(Incr)->Sales Header cleansing rules (Incr). Cleansing rule doesn’t execute as it should.
o Approach-1
Also since we have criss-cross transformations we were planning to execute in the following sequence and need your advice on the same
• At stage
o Masters
o Headers (Transformation from Masters)
o Line (Transformation from Headers and Masters)
• At DW
o Masters
o Line
o Headers (Transformation from Line)
o Approach-2
What we can do is get the data in the staging as is and perform transformation at DW from Line to Header only.
Write all the views in the DW for all the reports
Which will be the best possible way to achieve the population of all the tables with no transformation field blank?
Please advice.