Case Study

A leading global contract research organization providing comprehensive, integrated drug development, laboratory and lifecycle management services was seeking an automated testing approach for interface data validation between its numerous systems feeding into its Project and Portfolio Management (PPM) systems by CA Technology and its Siebel Clinical Trial Management System (Oracle). The company required an automated data testing solution that would validate all of the data interfaces. RTTS helped to achieve significant ROI by validating the numerous internal and external data interface feeds.

Challenges

• The organization needed to guarantee that the business-critical data was transferred from the source human resource system to the PPM system met business requirements and mapping specifications.
• Incorrect data would cause downstream resourcing issues and could cause millions of dollars in lost revenue. The organization required that 100% of the data be validated data, not the 5% percent that was being validated manually.
• Manual testing, using the Sampling Method (also known as​‘stare-and-compare’, was taking too long and was susceptible to human error.

The data feeds that needed to be tested included:

• Feeds from multiple external pharmaceutical organizations with their own proprietary systems
• An internal human resources system (Lawson)
• An internal management software (Synergy)
• An internal integrated voice response system

Solution

Using an automated ETL testing solution, the organization was able to increase the amount of data tested by 2,000%, speed up the testing process dramatically and decrease the cost of testing resources. The testing also eased the minds of stakeholders regarding the risk of an internal or external audit — they now would have historical testing data and reports to quickly and easily verify that 100% of the data was tested and validated. A total of 26 automated tests were authored, with 26 queries validating the legacy source system and 20 queries validating the target data warehouse. These tests were logically grouped into test suites for scheduling test executions.

Execution time took 1 hour to execute and validated 360,000 individual data cells. At the end of execution, an email notification was automatically sent to the test team and management stakeholders identifying the status (pass/fail) and completion time. Several test cycles were needed to clean the data, since defects (transformation & special character defects) were discovered. Additionally, the automated tests will be used for future enhancement testing and regression test cycles.

This automation process resulted in verification of all transformed and non-transformed data. The following project-level requirements were fulfilled:

• 100% of data was tested and validated
• Verified that the ETL code was developed correctly according to business requirements
• Verified that the ETL process did not improperly correct, reject or substitute data

Benefits

The 26 automated tests discovered numerous critical defects that would have cost the organization significant dollars. The defects discovered over the course of the testing cycles missing row defects. RTTS also provided metrics that were not previously available such as data reliability, using detailed reports generated by the tool.