Company utilizes QuerySurge to validate their Data Interfaces and provide 'Peace of Mind'

Validates Data Interfaces for Portfolio Project Management & Clinical Trial Management systems

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 selected QuerySurge data testing solution to automate the testing of the data interfaces. QuerySurge helped to achieve significant ROI by validating the numerous internal and external data interface feeds.

Background

A pilot project was selected for the organization’s internal resource system (Lawson) interfaced with their Project and portfolio management systems (PPM) by CA Technology. The organization needed to test and validate the flow of data between the two systems. A testing effort was implemented and configured utilizing QuerySurge as the data test automation tool. The interface between the two systems was to be executed on a nightly basis and would update the PPM system with the human resource system data (Lawson). In the past, manual efforts by the internal testing team could only validate 5% of the data during the testing cycle with the time allotted. The organization needed a more efficient automated testing process.

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 built by QuerySurge ™
• An internal human resources system (Lawson)
• An internal management software (Synergy)
• An internal integrated voice response system

Solution

Using QuerySurge, the premier automated testing solution for data interface testing and data validation, 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. QuerySurge was used to create automated tests (query pairs) over several weeks. A total of 26 query pairs were authored, with 26 queries validating the legacy source system and 20 queries validating the target data warehouse. These query pairs were logically grouped into test suites for scheduling test executions.

Execution time took 1 hour to execute the 26 query pairs and validated 360,000 individual data cells. At the end of execution, an email notification from QuerySurge 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 QuerySurge tests will be used for future enhancement testing and regression test cycles.

This automation process resulted in verification of all of 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 QuerySurge 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. QuerySurge also provided metrics that were not previously available such as data reliability, using detailed reports generated by the tool.