Data migration: manage the risks or lose the project

Introduction

Imagine your organization just having finished a major project that replaced the financial system used to support the financial processes. Next to your regular responsibilities, you and your colleagues have been intensively involved in the project to complete it on time and on budget. The entire organization is currently gearing down and is absorbing this huge change.

Then, disaster strikes. Payments seem to be going to the wrong creditors, the sales department is missing crucial client information, stock is piling up in the warehouse and the management is losing confidence in the integrity of the financial reports. In-depth analysis reveals that incorrect and/or missing data in the new environment is the cause of all the problems. Suddenly the central question of the organization is how to correct this situation, how to revive the operational processes, and how to explain this to the external auditor.

Although this scenario seems rather theoretical, it can become reality when a data migration does not receive the appropriate amount of attention and is regarded as a minor and isolated project activity. The challenges that projects and their migrations bring should not be underestimated.

Executing IT projects will present a wide variety of activities and challenges that are not regularly faced by the organization. Examples are business process redesign, functional test management and major changes in the IT infrastructure. In many cases a project is performed by team members who, simultaneously, are responsible for the execution of the day-to-day operations.

This landscape can lead to cutting corners and paying insufficient attention to critical activities, such as data migration(s) to meet deadlines. An ironic observation since the data migration track should ensure that the organizations’ data will properly land in the new IT landscape in order to properly drive the business processes, supply management reporting and provide for external accountability.

Earlier Compact articles, such as [Biew17], have elaborated about data migrations as a project activity and possible approaches of adoption. This article will provide insight into typical challenges we see when working on migration related projects at clients. We will also provide a high-level overview on how to overcome them.

Challenges encountered

Typical challenges that occur during migrations, which should be properly managed to avoid a failing migration, are summarized below:

1. Underestimation of the migration; leading to unavailability of resources and insufficient detailing of the migration process. Data migrations are always complex in their nature, underestimation could result in a migration that is not timely performed or not properly tested.
2. Insufficient analysis of the required conversion scope, data cleansing and data mapping. This could result in a migration that does not suit the expectations and/or business processes of the organization.
3. Scattered landscape with different (external) parties that are involved during the migration. Where different parties are responsible for separate migration activities, the risk emerges that activities are not aligned and not performed appropriately.
4. Custom built tooling that is developed in order to migrate the data. Software that is tailor-made for a migration could contain programming errors causing completeness and/or accuracy issues during migration.
5. Poor data quality that is not identified. For instance due to an incomplete oversight of the project team or dummy data to facilitate ‘workarounds’ in the current application.
6. Insufficient knowledge of the source system and its underlying database tables, leading to challenges in exporting the relevant data and/or ways to perform reconciliation.
7. Insufficient testing activities leading to the situation that completeness and/or accurateness issues are not identified and cause issues in the execution of the business processes. For instance: a visual inspection of only 25 samples on a total dataset of 3500. Another example is testing with data that does not represent the production data; in this case the results will not reflect the actual situation in the production environment.
8. An insufficient cut-over plan to cater for a solid migration and transfer to the destination system. A solid cut-over plan is crucial to guide the project members in this phase, especially when for test purposes the new environment is synchronized with the legacy system for a limited period of time.
9. Lack of acceptance criteria that provide the thresholds for a successful migration. This situation would hinder the final acceptance decision, making since migrations typically result in a certain amount of deficiencies. The acceptance criteria should guide management in determining whether the set of deficiencies, and their characteristics, is blocking the acceptance.
10. The documentation of the conversion is insufficient to provide insight into the migration and the activities that were performed to validate the completeness and accuracy. Certain documentation is crucial for several purposes, such as internal analysis afterwards and external audits.

Further, we have selected three cases to provide detailed insight into the main challenges our clients faced, and the approach to tackle them.

Three cases

Organization A – Custom built tooling and unclear migration requirements

Organization A has implemented a new solution for managing their clients and the services that are provided to these clients. Properly migrating the client and service data was crucial to continue the business processes and to properly invoice clients. However, the technical means to execute the migration are not available in the market. In order to technically realize the migration, a technical specialist was introduced to build a tailor-made script to download the data from the legacy system, convert it and populate the new environment.

Further, the project was under great time pressure to perform the migration and to deliver the new functionality. This landscape resulted in the following main challenges:

• Due to the lack of a (standard) solution to migrate the data, a custom built migration tool was necessary; bringing major risks of errors caused by flaws in the software code and scripts.
• Parameters of the migration, such as scope and data mapping, initially were not completely and not sufficiently defined due to the lack of knowledge in the organization and due to a time-restricted project.

An iterative approach

Based on the time aspect it might be tempting to start playing with the migration tooling and to simply populate the new system. Instead, we adopted an iterative approach where all aspects of the migration were refined during different cycles, in order to reach the situation where a complete and correct migration could be performed. This iterative approach also added to the view of the organization regarding their requirements and expectations of the migration.

The cycle started by defining and refining the migration parameters (e.g. scope, renumbering, data mapping, etc). These parameters are input for both the custom built migration tool, as well as input for the review of the migration results. A plan to review the outcomes of the migration was developed, and refined per cycle, to guide the review activities.

Since the migration was performed by means of custom built tooling, it was crucial to track the risk of possible errors in the software and scripts. In response, we were able to obtain independent data reports from both the legacy and new environments as input for a data comparison. Any errors, caused by flaws in the migration script, could be identified by means of this comparison.

The independency aspect of the review, for instance the selection of (independent) sources for the data analyses, is crucial to perform a proper review.

Elements of the migration scope that could not (easily) be reviewed by means of data analyses, for instance due to complex restructuring of specific data fields, were visually inspected.

After a mock migration in the test environment, the reviews were performed to validate the completeness and integrity of the migration. The results of the migration and the (independent) data comparison were presented to the project and relevant stakeholders from the organization. This final step led to valuable insights that were input for the next cycle and a further refinement of the migration.

These steps are visualized in Figure 1.

Figure 1. An iterative approach. [Click on the image for a larger image]

Organization B – Multiple external service providers and unclear data structuring

Organization B has replaced its HR software suite used to support the typical HR processes and payroll. In the meantime a new payroll service provider was selected and introduced.

While helping this organization in coordinating the migration stream we encountered the following main challenges:

• Several different external service providers were responsible for different parts of the migration; among which the software vendor, technical migration party and other intermediate parties. This scattered landscape resulted in a risk of different views on the migration and a lack of transparency on the tasks and responsibilities of each party.
• Lack of knowledge regarding the data structuring in the legacy system. Main reasons were inappropriate maintenance of data by different stakeholders and data that was entered in order to facilitate workarounds in the old software solution.

A test strategy that fits the environment

During the commencement of the migration stream we performed inquiries to grasp the quality of the data and the possible need for activities to cleanse or enrich the data. In practice this proved to be challenging, since in this case the data is being managed by different (internal and external) stakeholders. In general, it proves hard to make data quality issues explicit, mainly because people are used to working with the data as it is available. This therefore blocks an objective view of the data itself. This effect is enhanced in the case that external parties are responsible for the maintenance of the data.

In order to test the results of the migration, and to reveal possible data quality issues, we developed a test strategy consisting of different components:

• Where possible, data objects (such as employee master data) were validated on completeness and accurateness by means of a data analysis. The data analyses used (standard) reports from both legacy and destination environments to provide an overview of the data, such as all employees and their details. These reports were used to automatically compare the complete data sets and easily identify issues.
• Data objects for which a data analysis was not possible were validated by means of visual inspection. For a sample of the data set, in this case approximately 10%, these values were manually compared.
• A sample of pro forma pay slips were generated in the destination system, in order to compare the previous pay slips in the source system.

The comparison of the pay slips resulted in insights into poor data quality that caused incorrect pro forma pay slip generation in the new environment. Only after detailed analysis did we learn that specific values were suppressed by the old system as part of a workaround, however, after the migration, the workarounds were reinstated in the new environment and included on the pay slips.

This situation also proves that a migration can be formally performed completely and accurately, however, can still result in a situation that is inappropriate for the organization.

The large number of stakeholders that is involved in the migration calls for strong project leadership. All parties should be aligned beforehand on the data objects to be migrated, but especially during the resolution of the findings that are identified as part of the migration review. In this case, frequent discussions and alignment on the findings between stakeholders fostered an approach to discuss findings and to develop an appropriate mitigation plan.

Organization C – Independency issues and an inappropriate test file

Organization C has replaced their asset management software solution. This solution is used, among others, to manage physical assets, invoicing and the financial administration. As part of this project, a migration was planned in order to migrate all assets and the elements within the realm of financial administration.

Our analysis of their migration approach revealed, among others, the following:

• The project leaned on the (technical) implementation partner when it came to defining the migration and its review. In particular, the review plan was not independently scrutinized by the project.
• The migration file lacked elementary insights into the approach of the migration and the strategy that was used to validate the migration outcomes. A migration file is an essential element for the acceptance of the migration by the steering committee or to provide the required insights into the external accountant.

An independent migration approach is essential

Both the migration and the related test strategy were prepared by the implementation partner. However, the various parameters that are relevant for the migration were discussed in the project and documented; in essence, this provides a conflict in the segregation of duties, resulting in the lack of an independent and critical view on the migration and the review activities.

In this case, we noted that the implementation partner only defined tests to validate the completeness of the migration, whereas solid tests to confirm the accuracy were not included in the strategy.

In response, the organization added additional accuracy tests to validate the migration, such as:

• data analysis to validate critical fields, such as BSN numbers;
• calculation of hash totals on critical fields, for example the total consisting of the bank account numbers multiplied by the creditor ID.

Further, we noticed gaps in the documentation of the migration. An example is the data mapping, consisting of the data objects to be migrated, which was not fully documented; the results of the migration tests were also not (completely) available.

A solid documentation of the entire migration process and results of the tests is crucial to trace back the execution, review and decision making with regard to the migration; especially when differences are identified afterwards or in order to prove to the external accountant that appropriate steps were taken to perform a solid migration. Also, the file can be the means to easily provide insight to IT auditors into the measures that were adopted to mitigate the risks associated with the migration.

In response, the organization confirmed that, at least, the elementary aspects of the migration are properly documented, consisting of:

• the migration scope and data mapping;
• the tooling used to perform the migration;
• the strategy to test the completeness and accuracy of the migration;
• all findings that were identified during the migration review, including the resolution and the result of the re-test after the findings were resolved.

Conclusion

Data migrations have been present ever since IT solutions were introduced and extensive research has been performed in order to further optimize the migration approach. Nevertheless the data migration phase remains an activity with many challenges which, to be avoided, require appropriate attention. By means of this article we have attempted to provide insight into the main areas of attention that need to be managed by the project in order to avoid a failing migration.