Test Automation
Automation of Data Warehouse Testing
Background
The group risk and regulatory data warehouse of this Tier 1 Banking client, was critical for corporate decision-making and for predictive analysis. The huge volume of data being loaded into the data warehouse meant that the manual testing of this data was exhaustive and extremely impractical. Data was being fed into the system from various sources, at different times and in different formats, yet consistent processing was required and the current scope of testing was to be preserved. Performance testing through a manual approach was also extremely difficult and testers were suffering from testing fatigue. Critically, the existing release deadlines could not be impacted and testing costs had to be contained.
Approach
To combat these challenges, the ACS team recommended that an automated testing approach was quickly developed and implemented.
Solution
The team created a reconciliation procedure to perform a ‘Data Comparison’ (either relating to the Total Records Count or Sum of Measure’s values by each and every record of a table moving to another table or one database to another database). This also helped identify anomalies in the transformed or migrated data.
The team created an automation script around data count, data structure, sum of data, union of data and intersect of data to meet the above reconciliation challenge:
- Scripts were created to validate data from source to external layer.
- Scripts were created to validate data from external to staging layer.
- Scripts were created to validate data from staging to relational layer.
The standardization of data at the staging area was a key activity as the staging area contained a summation of all data sources. ACS implemented the business rules at the relational layer and performed the very complex mapping transformation logic over the data therein. Report testing included slice and dice, analytics, trends and huge volumes of processed data.
A data testing framework (DTF) comprising SQL / PLSQL scripts, test cases, test scenarios and sample test data was created. This helped in validating Data Completeness, Data Transformation, Data Quality, End-to-End Integration testing, Regression testing, Performance testing and Business Intelligence Reports validations.
Realised Benefits
Treasury issuance consistent between financial forecasting, ALM and capital planning applications.
- Automation scripts reduced human errors and increased data quality.
- Automation improved the validation process.
- Automation delivered reduced cycle time (due to automation, issues identified early, fewer defects etc)
- Automation increased re-usability.
- Automation improved data testing coverage because all layers were thoroughly verified.
- Automation provided the capability to test heterogeneous sources of data.
For more information about how we can help you please contact us
Vijay Jadhav
Managing Consultant
BI Architecture
Recent Case Studies
Extensibility & Scalability of Data Warehousing
In order to minimize bad debt volumes, this…Read more
End to End Data Warehouse Testing (FDSF)
The PRA initiated an industry-wide programme called FDSF…Read more
Delivery that counts
Our track record for end to end delivery speaks for itself. In our short history, we have delivered some of the most complex business and IT change across some of the worlds largest organisations. Our people are happy and so are our clients. If you would like to know more about a career in ACS please click here.