When implementing a new LIMS, the question is not usually whether to migrate legacy system data, but how much data, in what manner, and why? These questions are usually best addressed early in your LIMS planning, as multiple stakeholders will be affected by your decisions. Additionally, tasks may be added to the overall LIMS project to satisfy your data migration needs. Regulated organizations requiring validation of their data migration activities will require even more tasks and effort to satisfy their data migration needs.
Defining the Data
Defining the data to migrate is the first step in any successful data migration plan. LIMS have two types of data to consider migrating: static data and dynamic data. While both types of data are necessary for a LIMS to operate on a day-to-day basis, not all data is created the same and not all of it is required for a system go-live. Separately addressing static data and dynamic data sets the bar for a successful data migration project.
Static data is the data that enables a system to run and is usually configured before use. Examples include customer information, product specifications, analyses or test methods, and storage locations.
Static data is usually simple to identify. However, in some cases, it is deceptively difficult to migrate. If an organization wants to identify and remove inactive client data, or discovers that product specifications are handled differently within the new LIMS, it will impact the overall data migration process. This will be further discussed below.
Dynamic data is the other type of data that exists within a LIMS. It is generally created through the actual use of the system. Examples of dynamic data would include batch records, stability studies, sample/test results, QA approvals, and audit trail records.
Migration of dynamic data is possibly the most challenging aspect of your data migration to address. Doing so requires consideration of an organization’s data needs, as well as the overall LIMS application architecture required to support that data. Many organizations decide to address their data migration needs by drawing a clear ‘line-in-the-sand’ where the old LIMS will be maintained in read-only mode (often in a virtualized sense) to support historical data trending, client reporting, audits, and investigations.
In some cases, however, organizations feel that preserving all historical data by migrating it to the new LIMS is preferable. If so, substantial amounts of historical data will need to be migrated; often generating complicated and expensive project activities that must be completed prior to system go-live.
Finally, many organizations elect to implement a more complete data-warehousing and reporting system wherein historical data from several systems (such as LIMS, ERP, MES, etc.) is centrally stored to facilitate decision making with information that affords a complete view of the organization, inside the laboratory and out.
Successfully Managing Dynamic Data
Selecting team members with the right skillsets usually makes the difference in the success of a data migration project focused on Dynamic Data. Upper management and sponsors must also be involved in the decision making process to guide the LIMS project team in establishing where the lines will be drawn around the data boundaries of both the new and old systems. Of course, data migration decisions all must be approached while still considering the overall needs of the organization, project schedule, cost, and validation requirements. Skilled assistance during the planning and initial stages of a Data Migration project can help to determine the answers to your data migration choices quickly and cost effectively.
Mapping the Data
In order to successfully map data between systems, expertise is required in understanding how both LIMS define and store data within their respective databases. The structure of tables within a database will be different between LIMS. In simple cases, identifying the correct data mapping may be easy (as in the case of clients, or addresses). In more advanced cases, multiple tables, and their relationships to each other, will need to be mapped between systems – leading to a more complex data migration.
Moving the Data
After the data has been identified for migration, a formal Data Migration Plan may be developed. The Data Migration Plan will describe, in detail, the data to be migrated and the migration methods to be used. Most organizations use a combination of programmatic interfaces with associated data mappings and manual data entry of old data. For the programmatic interfaces, variations of the Extract-Transform-Load (ETL) model are often used to migrate data.
Simple Data Migration Cases
In simple cases, SQL Queries are used to extract the data and load it into the new system with no changes to data (data transformation) needing to take place. More advanced WYSIWYG type database management tools exist to easily facilitate simple data migration activities and can provide a robust reporting option to ensure data integrity throughout the process. Reports generated after the data migration make the data review and confirmation process simpler, and help QA departments avoid costly alternatives such as 100% manual data verification.
Complex Data Migration Cases
More advanced types of data migration involve more activity within the Transform action in the ETL process. This often happens when fields are not configured the same in the new system as the old. For example, Storage Location names may need to be changed in the new LIMS compared to the old since the new LIMS will support multiple departments. It is important to note that test limits on product specifications are almost always defined differently between LIMS solutions (i.e. one LIMS may use the symbols =< to describe ‘less than or equal to’, when the other uses ≤ within product specifications).
Dynamic data may often have complex rules around the transformation of data, whereby multiple transformations have to take place on several pieces of source data, and IF/Then rules may need to be applied. Each of these new rules is generally considered a ‘stratification’ to your data – treating a slice of data different from the rest of the data within a table. These stratifications will impact the overall Data Migration Plan and may contribute substantially to the validation work required to prove the integrity and completeness of the data has been preserved through the data migration process.
Data Migration Validation
To elaborate, validation around Data Migration can become a complicated task, especially for organizations not used to dealing with the migration of millions of records and several types of stratifications, each requiring separate approaches to validating the data.
The Data Management Plan should be written to include what transformations will be performed on which data, and provide a way to verify the transformations are correct prior to loading them into the final system. The plan will serve as the basis for the data validation effort including methods to verify that the final loading has been completed correctly.
The tools that organizations use to validate data migration vary widely within the industry. Some organizations may choose to purchase a data management suite that facilitates extraction, transformation, loading, verifying, and reporting all within the same toolset. Other organizations may choose to rely on a more manual approach where data verification is done manually to ensure 100% data matches. Remember, the validation approach required during data migration is always an important conversation to have with QA in early project phases.