Contract Research Organization Saves $288,000 in Clinical Trials Data Migration Testing Project

Background

RTTS embarked on a data migration testing project to ensure that over 300,000 records were transformed and loaded from an older Siebel source system to a newer Siebel system. QuerySurge, a big data test automation solution, was utilized to verify 100% of the data migration effort.

Challenges

A leading global contract research organization providing lifecycle management services recently embarked on the upgrade implementation of Oracle’s Siebel Clinical Trial Management System (CTMS), which would automate core components of its business. The upgrade was important, because the existing CTMS system was nearing its expected end-of-life. This project was implemented with an expectation that it would provide the organization with a competitive edge in the market space. A substantial piece of the CTMS project involves migrating data from the current system to the new system.

• The organization needed to ensure that the business-critical data was migrated from the source system to the target system according to set guidelines and specifications.
• Any incorrect or bad migrated data might cause system users to have to troubleshoot the data and potentially to re-enter or to edit the incorrect data, causing days or even weeks of lost productivity.
• The potential for duplicated data could cause the overall size of the backend database to increase, resulting in a decrease in performance and an increase in hardware resource utilization.
• The cost per clinical trial, on average, is roughly $46 million. If the data is incorrectly migrated from one system to the other, this could have serious cost implications for the company.

Solution

The focus of testing the data migration was on data quality as verified by query-to-query data comparisons between legacy and new systems. QuerySurge was utilized to create a suite of tests that facilitated comparing and verifying data from the legacy source database to the target Siebel database. 31 query pair tests (totaling 62 queries, with 31 against the legacy system and 31 against the new implementation) were executed over 8 ETL builds to ensure the transformation from source to target completed successfully with all data transformations and mappings consistent with the project specification. This procedure resulted in verification of 100% of the migrated data. The following project-level requirements were fulfilled:

• The ETL process completed without failure
• No incorrect data transformations were processed
• The ETL process did not improperly correct, reject, or substitute data

Benefits

The 31 QuerySurge tests discovered 24 critical defects that would have cost the organization countless hours of manual entry and modifications to the invalid data, not to mention costs associated with resulting compliance issues. The defects discovered over the course of the ETL build process ranged from:

• Truncation issues: specific column widths in the target database were too short to accommodate the source data
• Null values in the Target database: The ETL process had failed, and populated columns with null values, rather than valid data from the source
• Improperly implemented mappings: List Of Values were improperly mapped according to specifications
• Data Duplication: Instances of record duplication were discovered

Below is a Defects Discovered Per Build graph:

Project Result Highlights:

A major return on investment was seen by the organization within their first project using QuerySurge. They were able to greatly reduce the risk of improperly transitioned clinical trials data, and they were also able to calculate the amount of money and time saved on the CTMS Project: