BI/Data Warehouse Test Automation Case Study

RBS Services (Switzerland) Ltd

How RBS services (Switzerland) Ltd ensures Data Integrity across regulatory reporting and management information systems

“Our MI (Management Information) systems are the basis for critical executive decisions. From calculating payments, to evaluating discounts, to assessing the impact of regional developments, the company’s ability to reduce risk and drive new business hinges upon the accuracy of the data underlying our highly data-driven processes. The ability to automatically expose data impacts—as soon as they enter our systems—is vital to maintaining our competitive advantage, as well our regulatory compliance.”

— Stephen Fishwick, Director, Head of DWH, MI and Archiving, Enterprise and Data Solutions at RBS

Background

In the banking industry, “move fast and break things” is not an option. Across every customer transaction, regulatory report, and strategic investment decision, absolute accuracy is imperative.

During their most recent risk review, RBS Services (Switzerland) Ltd IT leaders observed that the growing scope and complexity of their application landscape warranted a more automated approach to identifying data impacts. It was becoming increasingly difficult to manually pinpoint all the potential impacts of each change—and their commitment to doing so was consuming resources allocated for regulatory reporting and system innovation.

Challenges

Monthly data warehouse iterations require millions of data rows to be checked vs production.

Nearly 50 projects go live simultaneously bi-annually.

Avaloq rolls out updates twice a year, and these changes impact hundreds to thousands of components each time.

Accurate reports must be delivered on regulator-mandated deadlines—regardless of what updates and obstacles RBS may be facing internally..

Testing scope and speed had to dramatically increase to keep pace with growing system complexity…but without continuously expanding the headcount.

The Tricentis Solution: Automated regression testing for Data Warehouses and ETL processes

To ensure that potentially far-reaching change impacts were identified immediately and efficiently, RBS Services (Switzerland) Ltd launched an initiative to automate regression testing across their data warehouse and its various ETL processes.

For several years, the company has been using Tricentis to ensure the continued integrity of UI-driven scenarios across their core banking solution (Avaloq). When they decided to extend their test automation initiative to the data layer, they contacted Tricentis. Following an extensive pilot that covered 15 use cases ranging from Avaloq data validation to SAP BusinessObjects security tests, they selected the Tricentis solution to automate their BI/DWH testing.

RBS Services (Switzerland) Ltd is already up and running with a core set of tests that automatically cover their most pressing risks. With this automated approach, data regressions are exposed more efficiently—and much earlier in the project lifecycle.

Solution details

Regression testing across their complex systems (Avaloq feeds processed into Oracle DB using SAP data services as an ETL tool, transformations in data marts/cubes send data to related DWH systems, tools like SAP Business Objects are used to share insights and enable smart decision making).

Automated testing includes data validation across different sources and targets, metadata checks, security tests, and report testing.

A reusable, extendable set of regression tests is centrally available, fostering collaboration among teams and increasing productivity.

Testing efforts and results are automatically documented, assisting the teams in identifying and remediating issues during regression testing.