On our business intelligence team, we use a document to display what we call the “cross walk” for Source Tables, Mart Tables and Reporting Layer objects. And from this document, we can know the ETL mapping between source columns and mart columns, and we also can know the ETL mapping between these mart columns and the report layer columns.
But in this document, we often have thousands of records, so how can we make sure the mappings are right? How can we make sure there are no duplicate records? Can we test it with automation?
Here is an Excel template we used to keep the cross walk, the first two columns are source table name and source column name, the next two columns are mart table name and mart column name, the last three columns are report layer object, report layer object type and report layer column name.
Testing method glance:
Here are detail test steps we used.
1. Import the cross walk document into database, for example, table name is QA_Crosswalk_Doc
2. Import the mart velocity mappings which define the relationship between source tables and mart tables into database, for example, table name is QA_Mart_Metadata
3. Import the report layer velocity mappings which define the relationship between mart tables and report layer objects into database, for example, table name is QA_Report_Layer
4. Duplicate testing: Using following scripts to find out the duplicate records, and delete the duplicate one to make sure all of the records are unique.
5. Set up the expected result: Using the two tables QA_Mart_Metadata and QA_Report_Layer to create the expected result for the cross walk. for example, view name is QA_VW_Expected_Result. Here is the script:
6. Using Toad to compare the view QA_VW_Expected_Result to the table QA_Crosswalk_Doc, and all of the records should be identical.
