Situation

A top food and agriculture company had enjoyed a steady growth in revenue and demand due to the success of their various products/brands in the farm supply and dairy food markets.  In order to sustain this growth it was determined that additional efficiencies needed to be found in their quality control laboratory operations in order to support the increasing output of their production areas.  Although a LIMS system was currently in place (LabVantage Sapphire) through which additional laboratory automation could be implemented, this software solution had reached its end of life time point and would shortly no longer be supported by the vendor.  

The company, therefore, determined that there was a need to upgrade their current LIMS system from LabVantage Sapphire to LabVantage 7.  By doing so the company would not only mitigate any risk associated with using an unsupported LIMS, but they would also be able to take advantage of some of the new and more robust features and functions that LabVantage 7 offers to increase their laboratory efficiency.  

However, as the planning process for the upgrade and migration was taking place, it became clear that the static data in the legacy LabVantage Sapphire LIMS was not in shape to allow for a clean migration.  There were many inconsistencies, duplications, and holes in the static data that needed to be addressed.  Additionally, it was quickly determined that mapping the data entities from LabVantage Sapphire to LabVantage 7 was not a trivial task.  

The food and agriculture company decided to engage CSols, Inc., a LabVantage partner, to support their Sapphire LIMS to LabVantage 7 LIMS upgrade and data migration for several reasons including our informatics, domain, and IT expertise. They especially chose CSols for their critical knowledge of the underlying data structures of both the Sapphire and LabVantage 7 LIMS solutions.

Objectives

The primary goals of this project were to maintain vendor support and increase laboratory efficiency through the upgrade of the company’s Sapphire LIMS to LabVantage 7.  It was also critical that the static data from the client’s Sapphire LIMS be scrubbed and transformed into a format that would be acceptable for import into LabVantage 7, thereby preserving their investment and maintaining information continuity through the upgrade process.  The static data migration would require CSols to cleanse the original data to remove duplicate entries or items that had been discontinued, streamline and normalize naming conventions for Workitem IDs and parameter list (Paramlist) IDs, and modify corresponding relational tables in LabVantage 7 to accommodate the data entities to be migrated.  Updates were made to all analysis names, reference method numbers, reference method sources, and test methods for each key identifier.  Each set of data elements, ranging from microbiology to dairy items, were color coded for ease of use and organizational purposes and were then reviewed and configured.  

Once the data was scrubbed, a data export file was created for each colored data block and delivered to the client.  The CSols resources worked closely with the company’s LIMS team to resolve any data conflicts found during processing.

Challenges

There were several challenges that needed to be overcome in order to attain success in this project.  First, the original data entities were not standardized and there were many duplicate entries or similarly worded data elements in the legacy system.  The CSols resources were able to overcome this challenge by leveraging their domain and product knowledge and by working with the client to develop a standard naming convention and then applying it to every key field and Paramlist.  

Another project challenge was that inconsistent and dirty data was widespread in the legacy system.  There were many instances where data was missing or incorrect, which required review and reconciliation by a specific client’s functional team like microbiology.  For example, there were record identifiers that did not have a corresponding parameter list, which meant the data elements were not properly created in the original system.  The microbiology functional team had to review these kinds of errors to determine if the data point would be needed or could be eliminated. If the former, the parameter list had to be developed and then agreed upon.  

To attain success in this project and meet the project deadline, the CSols consultants were able to document each data discrepancy and continue working on another colored block of data while waiting for feedback from a specific functional team. 

CSols’ Role in the Solution

This effort included the following key tasks and deliverables:

• Data Transformation/Scrubbing Support:
  • Reviewed each colored data block, removing duplicates and draft records
  • Updated existing data points, Workitem and Paramlist IDs, to adhere to the agreed upon naming conventions
  • Configured related data points to link to the updated key identifiers
  •  Modified analysis names, reference method sources, reference numbers, and test methods
  • Transformed 11 relational tables and 2 Helix data points for the dairy business
  •  Delivered 7 cleansed data export files, including 5 colored transformations and 2 Helix transformations for the dairy business
  • Delivered a summary of changes, detailing the changes for each key identifier and parameter

• Team Lead Resource:
  • Guided transformation team in formulating a plan
  • Provided support as needed

Benefits

Many benefits were realized by the company based on CSols’ efforts and services in making this project a success.  They include:

• Decreased the client’s overall timeline to complete the transformation by formatting the legacy data to facilitate importation into the new system
• Allowed client to focus on completing the upgrade to LabVantage 7 by managing the data migration
• Reduced the time needed for data validation by including a Summary of Changes document as a deliverable to the client
• Standardized the client’s key fields and naming conventions, improving efficiency and making it easier to find and log samples into the new LabVantage 7 LIMS
• Increased throughput by decreasing cycle times as a result of standardization of test methods and material names

‍