Migration to Dimensional Modeling - Testing

[rf001]

Hello,

I migrated a relational database to dimensional modeling database. I need to test that all the information in the source system is there in the target system. One technique which can be used is...
- to calculate the number of records in the dimensions and compare with the source tables of the dimensions.

Can you please, guide me what else can I do to verify if all the data is there.

regards,

P.S: It is a Human Resource Database,
It is not like traditional data warehouses, answering different queries than the source system. The same queries will be made as were made in the source system. The main purpose is to make the queries faster and make the modeling simpler -

[rob.hawken]

Hi

I always think of 2 levels of validation:
First level validations would be for row counts comparing the source with the target in the DW, which you've already identified. This is not too useful as it doesn't identify any errors that may occur due to the transformations performed in the ETL.
2nd level would be 'value' validations. e.g. in source I have 100 Senior managers what number do I have in DW or I have sick leave balance of 1000 days in source what do I have in the DW. These allow the identification of transformation errors and can be used to show the business that the DW has integrity. You'll need to sit down with the business and identify what are the critical ones are.

It is good practice that these validations should be included as part of the Audit stream in the ETL.

Cheers

[rf001]

Thanks for reply. It is really a useful tip.

I was thinking of like calculating averages or other statistical measures, on different fields like calculating the average of salary for a specific month paid to all employees and compare it. Do you think it it will be useful?

Secondly, how to verify that the data did not change during the process? like maybe the average salary maybe the same, but the salary of two employees maybe interchanged in the process.. how to detect such changes?

[Jeff Smith]

I have found 2 ways to test dimension tables. I load the dimension tables twice, with changed data to make sure it is handling the Type 1 and type SCD dimensions. The second way I test the dimension tables is by joing to the fact tables and doing frequency distributions and comparing the results to distributions from the source data.

One of my validations processes on the Fact table is you count the number of times a foreign Key is -1 (the value for missing dimension keys). For some columns, there should never or rarely have a -1 value. For other columns in which -1s are OK, I look for spikes.

[ngalemmo]

Another test I use, which is very effective but a bit time consuming is to run detailed reports of random (or selective) entities from the source system and compare them with the same report off the dimensional data. This gives the busniess a high comfort level that the transformed data is, in fact, accurate and correct.

It also avoids problems latter when testing the BI layer. Any discrepancies found at that point can be attributed to issues with the report spec or metatdata in the BI layer, not ETL or data content.

[rob.hawken]

RBAR (row by agonising row) - comparing the individual values from the source and DW is painful and common. Often it's the only way the business will sign off the migration.
Take a representative sample of the source data rather than try and do every employee. Also select some of your sample based on what is being affected by your transformations. e.g. staff where their salary is being converted.
Try and get the business users involved in the checking. Develop some reports out of the DW and get the users to compare with the source system.

[rf001]

Any ideas about verifying dates e.g. hire date, and contract renewal. I think we cannot calculate statistical measures for dates.

[Jeff Smith]

Compare counts by date in the finished tale to the source.

By the way, to make it easier to validate the loads, create validation fact tables that contains all of your validation information. If you are comparing record counts from the source to the final table, let the load process do it and store the info in fact tables and create validation dimension tables that has the table being tested, what is being tested, etc. Add it to your BI reporting tool and and create validation reports. After a load, click on a validation report, and you're done (hopefully). You could even add other information to such an environment such as load times, etc.

[Sponsored content]