How To Execute Successful Data Migrations

Data migration is the unglamorous task of a system implementation. And so, life-sciences companies often give short shrift to data migrations in comparison to new or upgraded business applications. Because a business application is only as good as the data upon which it sits, however, it is critical that data migrations receive the same level attention as application functionality. Like business applications, successful migrations must be measured by the extent to which they meet the business needs in a timely, cost-effective manner that attain the highest level of data integrity. By taking proactive steps to fully plan, scope, design, and develop a formal quality assurance (QA) approach, a team will be poised to make their next migration a great success.

The importance of planning

Due to poor planning, many migration efforts come up short (or fail), resulting in project delays, cost overruns, lack of delivery, and bad data that compromise system integrity and compliance. Insufficient planning often stems from the failure to view data migration efforts as distinct projects (as part of a larger project or program). But that is exactly what they are, and as such, are no less deserving of meticulous planning, rigorous project management, and adherence to a software development lifecycle.

Migration projects should include all the major constructs of a significant application implementation, including dedicated resources like business analysts, technical developers, QA analysts, and a project manager. They should also include business stakeholder engagement, representing both the micro and macro view of the migration within the organization. For example, audit trails that capture data maintenance of raw materials, components, and ingredients must not only migrate in a good manufacturing practice (GMP)-compliant manner but should also be FDA-audit ready. Toward that end, organizations need to consider how the newly housed data and documents align with existing company audit plans.

Keys to planning success are:

• Establish project governance with representation from business and technical resources.
• Develop a project charter with stated business objectives and targeted timelines.
• Identify risks known at the outset. Keep these and future risks visible to all parties throughout the project.
• Perform the scoping of the migration with a budget and project plan as major outputs.

Scoping a migration

Like any other software project, success begins with stakeholder engagement to accurately size the effort according to both business and technical requirements. To scope well, here are some key questions a team should be asking.

Which data should be migrated?

Consider a scenario in which a team will be implementing a new document management system that supports core manufacturing processes. The legacy system being replaced is eight years old and holds over one million documents. How do you determine which and how many of these documents should migrate to the new system?

To start, determine the “state” of the process with which these documents are associated. Documents, such as standard operating procedures supporting active processes will, of course, need to be migrated. But what about long-retired procedures that are no longer subject to regulatory inspection? If there are no clear needs to access a document in near real-time, wouldn’t it make more sense to archive that document off-line? Similarly, how far back will a company need to maintain manufacturing logbooks and specifications for retired formulations? 

On many levels and for many reasons, the amount of data and documents that migrates has a direct relationship to the cost and effort. For example, because data migrations typically require significant transformation logic and data enrichment, the value of the data should be considered in relationship to the effort needed to migrate.

Determining the specific business need(s) for legacy documents and data will establish a strong foundation for all migration activities to follow.

Keys to migration scoping success are:

• Identify and engage stakeholders to define business requirements for the migration, who will need the data and for what purposes.
• Use these business requirements to understand the locations/dispositions to which data will be migrated (active systems, auditable archives, records management, or even data destruction).
• Leverage the business requirements to detail the data mapping and transformation requirements.

Which entities of data will need to migrate?

In the example above, it is tempting to consider only the documents and meta-data in its own distinct compartment. But what if some of the meta-data attributes are elements of enterprise-wide data entities, such as product, indication, vendors/organizations, or people? In such cases, a migration scope should consider how these elements fit into the overall enterprise data architecture and, specifically, an organization’s master data management

If a migration contains transactional data or documents that also introduce new reference data records (new organizations, people, customers, etc.), it is critically important to assess the impact of these with the relevant parties early in the planning phase of the project.

Keys to success are:

• Profile the source data and identify data elements that may be shared across the enterprise.
• Engage with the organization’s data governance team and data stewards.
• Define high-level requirements on how the legacy data will integrate not only into the target system but also into the organization’s master data repositories and related processes.

What characteristics of the target environment need to be considered?

Few business applications sit in isolation; they are typically part of an overall business process supported by both upstream and downstream systems that either share or pass data to each other. 

Likewise, scoping a migration should always consider the overall environment (system context) in which the target system resides. In some cases, the source-to-target mapping for a migration may need to accommodate the requirements for other systems. In other cases, interfaces between these systems may require modification to accommodate new requirements arising from a migration. In either case, dependencies need to be identified and integrated into the overall migration plan.

Keys to success are:

• When scoping a migration, make sure to engage with stakeholders who have “the big picture” of the relevant application and data architectures.
• Identify system dependencies and develop plans to identify and mitigate risks associated with these dependencies.
• Leverage the established project governance so representatives of affected systems keep one another informed.

Data transformation

Most migrations involve some combination of automated transformation and user data enrichment. In the business requirements phase of a project, determining the right mix should be a major output.

Some questions to consider:

• How clean is the source data, and what will be required to cleanse prior to migration?
• What data elements can transformation rules be reasonably programmatically applied to? What effort should be extended to accomplish this transformation?
• How much data will users be required to manually enrich? How many rows? How many columns?
• What toolsets can be enlisted for users to easily enrich data?
• Are there any data elements that can be transformed post-migration without adversely affecting system functionality?

All of these questions should be answered during the business and technical requirements activities of the project. Again, by treating data and document migration as a distinct project conforming to a software development lifecycle, a team—working with key business stakeholders—can develop the most effective transformation approach that balances budget and human resource constraints.

Keys to success are:

• Define, document, and share business requirements.
• Develop multiple enrichment scenarios, weighing the pros and cons of each, considering data integrity and the effort and costs needed to attain the desired quality threshold.
• Define the technical requirements needed to automate data transformation versus manual data enrichment.
• Update established project plan(s) to achieve the desired results.

Quality assurance

A new business application would not be deployed without rigorous system and user acceptance testing. The same principles should apply for migration projects. 

Effective QA begins well in advance of technical testing. Migrations should jumpstart the QA effort by closely examining the business, technical, and transformation requirements that have been agreed upon. In effect, the requirements are tested as a precursor to testing the implementation of the requirements. In addition, because migration testing typically involves repeated cycles of moving large volumes of data, pay sufficient attention to processing time, overall throughput, and the ability to easily refresh target environments.

Proper planning for migration projects should include overarching QA/test plans that detail an overall approach, technical requirements, system testing approach and system test scripts, user acceptance testing requirements and methodology, and post-migration smoke testing.

Keys to success are:

• Define an overall testing strategy as early as possible in the project, at minimum when the business and technical requirements are near finalized.
• Assign experienced QA resources to review and test business and technical requirements.
• Have QA resources work in tandem with business analysts to review detailed transformation/mapping specifications.
• Have QA resources meet regularly with the technical team to assure alignment of test scripts with the transformation requirements.
• Engage business stakeholders early in the effort to define the user acceptance testing approach and testing requirements.
• Develop the technical mechanisms for rapid refresh of target environments.

Conclusion

The scope of a data migration must include which data will be migrated, how to transform and enrich that data, and how to test the business and technical requirements of the project. Because a new system implementation can’t happen without migrating data, it is critical to treat all migrations as software implementation projects in order to achieve desired results.

About the author

Art Meisler is principal consultant, Daelight Solutions.

Article details

Pharmaceutical Technology Europe
Vol. 32, No. 11
November 2020
Pages: 34–37

Citation

When referring to this article, please cite it as A. Meisler, “How To Execute Successful Data Migrations,” Pharmaceutical Technology Europe 32 (11) 2020.