Overview
This case study describes how a complex data migration was 100% tested using a reconciliation solution. 100% testing means 100% coverage: all non-technical data fields of all records of all tables must be compared before and after the data migration. Custom practice with data migration is to test a representative sample survey, covering 5% or less and only the most critical data fields. Sample surveys, counters and hash codes are insufficient to exclude all possible mismatches and (un)intentional exchanges in the data. 100% coverage is necessary for critical business data, e.g. customer and contract data.
The reconciliation consisted of:
- Data completeness tests for 90 million data records
- Data correctness tests consisting of 280 million data field comparisons
The reconciliation was used for testing the data migration as part of the total test plan for accepting the new system in combination with the migrated data. The reconciliation only took 1,5 hours to fully execute and obtain the full 100% coverage.
After successful migration to the new system, the reconciliation solution was also used for daily reporting on synchronisation issues between the new system and the old system. This was needed because of a stepwise migration approach, in which the large and complex application landscape was renewed incrementally. So, the reconciliation solution was used for safeguarding both the data migration and the data synchronisation afterwards.
Background
The reconciliation project was part of a transformation program initiated by an international automotive company to enable fulfilment of strategic targets. Major goal is the reduction of the operating ratio by standardisation and harmonisation of the currently heterogeneous process and application landscape. These measures increase efficiency and reduce technical complexity.
First step in the program is the migration to a standard and harmonised business partner platform. The data migration was conducted by the internal IT department. Data eXcellence was responsible for the reconciliation solution.
Challenges
The customer had limited experience with the reconciliation of complex data migrations. Data eXcellence was asked to propose a solution for a 100% reconciliation for the data migration. The reconciliation had to compare all records and all data fields before and after migration, considering all mapping rules for the new standard system. The main challenges included:
- The volume and complexity of the data
- The transformation rules were not yet formalised and under construction
On top of that, it was required that the solution was reusable after Go Live, to compare the new and the old system daily. This was needed for synchronisation reasons, since the old system remained in use, as part of a step-by-step migration approach.
Solution
The first step to come to a solution was to formalise and complete the transformation rules for the data migration on a functional level. This made the mapping understandable for the business, essential for approval. These specifications became the common truth for both the data migration and for the reconciliation. And, after Go Live, for the synchronisation too.
Next step was to design and build the reconciliation tests. This is more than simply comparing the values of data fields before and after data migration. In mapping the data to the new target platform data all kind of transformations take place. Simply reversing the transformations is not possible, because the mappings from source to target are not always reversible, e.g. many to one transformations. Since a 100% reconciliation was needed we decided to rebuild the complete transformation logic using the DX data migration factory framework (DXF™). This way, we avoided the pitfall of reversing the mapping logic. A 100% reconciliation was possible by simply comparing the results of this transformation with the results in the target system after the actual data migration, which was performed by the internal IT department. This approach made it possible to use the reconciliation tests as a full automatic regression test for each data migration trial run. This reduced the test effort and helped to improve the quality of the data migration software.
Final step was to produce reports on top of the reconciliation and comparison software showing the results on different levels of aggregation. Management reports showed overviews on percentage complete and correct. Detailed reports for developers of the migration software showed the actual mismatches and were used for analysis and debugging. For the reconciliation of the synchronisation after Go Live, reports were created that could be used by data cleansing teams.
Results
The Data eXcellence approach of formalising the mapping and rebuilding the data migration based on the same specifications turned out to be practical and efficient in establishing a 100% reconciliation. The extra effort for building the data migration a second time paid off in reduced test effort and high quality of the data migration. And after Go Live the same solution was used to assure that both systems kept in sync and deviations were automatically reported in daily data cleansing reports.
The reconciliation solution provided by Data eXcellence helped to achieve a successful data migration and 100% reconciliation within planning and budget. The collaboration between the data migration team and the reconciliation team was always constructive and focussed on a common goal: a high-quality data migration.
Manager Data Migration program