End to End Data Warehouse Testing (FDSF)
Background
The PRA initiated an industry-wide programme called FDSF (Firm Data Submission Framework) – working with the largest UK Banks to standardise and automate the CP Data Collection process and to address common issues identified in previous collection exercises across the industry. For this Tier 1 banking client, the PRA had raised concerns on submissions around data quality, particularly within the investment banking balance sheet, structured finance and wholesale RWA submissions. The PRA had indicated that these issues must be addressed before the next Core Stress Testing Programme was to be run and indicated that the bank must have a framework in place to run the CP
Stress Testing Programme on a quarterly basis.
- ETL: ETL processes (using PL/SQL) to load the data with the required transformations and aggregations.
- Data: a data model and meta data that represents an accurate picture of the PRA – FDSF Semantic data model.
- Reporting Tools: a set of required reports as specified in the PRA data request templates for Retail delivered as Advanced Reporting reports (‘AR’ – using new OBIEE reporting toolsets) and in csv format
Testing Approach
As part of the Agile Methodology, ACS analyzed the requirements and created a Test Strategy for FDSF workstreams within the Strategic Risk, Finance and Treasury Transformation using relevant Connect4Change templates.
Working in line with the ACS best practice Test Framework, ACS delivered a comprehensive Test Strategy ensuring full test coverage of all components involved in the end to end flow – from data processing through to data presentation.
Testing Scope
The following test phases were identified to be in scope for strategic regulatory platform – FDSF testing:
System Testing
- To verify new functionality
System Integration Testing
- To verify integration of touch points including scheduling/alert services e.g. interfaces, data flows, end-to-end ETL routines.
- Operational Reconciliation of the extract, transform and load routines
- Financial Reconciliation of the extract, transform and load routines
Business Acceptance Testing
- To verify that strategic regulatory platform – FDSF report functionality conforms to PRA SDM and business requirements. During this testing ACS ensured that the Testing Team had a clear understanding of the Retail business and its requirements. This ensured that appropriate test cases were executed and results produced in such a way that minimised effort required by the business for UAT.
Testing Principles
Testing integrity was a critical success factor. The following test principles were defined, agreed, and adhered to by all parties:
Independence and Objectivity
- The Test function will remain independent and objective throughout the strategic regulatory platform – FDSF programme delivery, thereby maintaining and protecting the integrity of testing. The scope of testing in terms of architecture, functionality, specific data requirements and business processes will be defined in relevant approved programme documentation – requirements (functional and non-functional), design specifications, business process design, and traceability matrices. The test team will not define the scope of testing, nor will it define what needs to be tested.
Traceability
- Testing must be fully traceable between build activity, design specifications and requirements documentation. Traceability will be achieved through the use of Requirements Traceability Matrices. These matrices will be built and maintained by the strategic regulatory platform – FDSF Test Team through the course of Testing.
Risk-Based Testing
- Where possible, testing should be cost effective and hence will utilize a risk-based test approach; prioritizing tests based on an assessment of the risk impact and probability provided against the underlying requirement. As far as target data provision is concerned, this approach can only be taken where the source end-user business requirements have been risk-rated and are available for test phase test plans.
Control
- Code/configuration versions, data and environment are strictly controlled. Promotion of test items between test phases is governed by detailed exit/entry criteria. Baselining and subsequent promotion of any changes into or between environments is managed via a clearly defined and approved Software Release and Configuration Management Strategy and associated processes. Any changes to the strategic regulatory platform – FDSF test environments and activities performed within these environments, must be approved by the strategic regulatory platform – FDSF Test Manager or by the appointed deputy.
Repeatability and Automation
- Aligned to the “Control” principle above, tests can be repeated under identical environmental conditions, to support defect fix testing and regression testing.
- Testing will automate as much of the testing activities as possible to ensure cost-effective, timely and efficient testing practices.
Testing Early
- Defects detected early in the delivery lifecycle are significantly less expensive to fix than those detected at a later stage. Testing will test as early as is practical – this will include the static testing of analysis and design documentation, review of planned unit and link tests and their results, and execution of functional and performance testing at the earliest opportunity.
- Approach to include the early provision of a number of test cases. e.g. proving the quality of test scenarios and test scripts to be used in BAT by running a percentage of them in SIT. These activities build confidence in the quality of the system under test and can reduce the number of test scripts that will be executed in later test phases.
Transparency
- Test scope, materials, results and progress are made fully available and are communicated to all stakeholders.
Security and DPA
- Testing must be planned, prepared and conducted in line with the banks Information Security and Data Protection policies.
De-duplication of test effort
- Merge of test phases wherever possible. For example, if Performance, Load and Volume testing exercises result in business processes at anticipated volumes, then a case could be made for reducing the scope and scale of BAT.
Team co-ordination / collaboration
- Promote a feeling of one team, amongst the team and all of our stakeholders.
Lessons learnt
- Hold regular lessons learnt sessions and act on them. Lessons learnt are not useful unless implemented.
Test Coverage – ETL Layer
- Identification of Data Sources from a number of upstream systems to strategic regulatory platform – FDSF Staging layer
- Test that source files are not corrupted as part of file transfer / extraction.
- Test source system extract file and data item formats conform to source extract specification.
- The feeds in scope are UK Retail, Ulster Bank, Citizens, Wealth
- Data Sourcing from Staging Layer to strategic regulatory platform – FDSF Relational Layer
- Validate – mapping and reconciliation controls conform to the design specification.
- Validate tables and data within the schema conform to schema specification.
- Test the tolerance level for the data as recommended by business e.g. not more than 10% of the data should have default country or industry, etc
- Data processing from strategic regulatory platform Relational Layer -> Presentation Layer
- Validate that data transformations and results are aligned to the design specification
- Validate that data results are aligned to the PRA defined SDM and are fit for the reports required.
- Feed generation from strategic regulatory platform – FDSF Presentation Layer for Quarterly / Yearly submission:
- Validate solution implemented for outbound feeds from strategic regulatory platform – FDSF Presentation Layer for Quarter / Year-end submission supports all the Retail FDSF data reporting contexts. The proposed are in Retail Mortgage, Retail Excluding Mortgage.
- Audit and Error Logging
- ETL Failure and its Restart ability
Report Development – OBIEE
- Delivered optimized reporting tool to fulfill the FDSF reporting requirements – Retail Mortgage and Retail Non-Mortgage reports.
- Report layouts.
- Dashboard layouts.
- Inter reports reconciliation.
- Intra report reconciliation.
- Reports reconciliation with external data.
- Data / Divisional Security.
System Testing validated that the developed functionality, configuration and data feeds function as designed (as specified in design documentation) by exercising them under controlled test conditions.
Business Acceptance Testing was performed to ensure that the OBIEE reports were aligned to the data rules as defined in the PRA SDM and that they were functionally fit for Retail business purposes.
Test Solution
System Testing
- An automated PL / SQL based methodology is adopted to perform system testing in which the process validates records count and High Level Measures at various phases of ETL i.e. Staging, Relational and Presetation Layer. The process produces results reported at various logical entity levels including division and data set level which enables the team to identify potential DQ issues and hence resolve them quickly.
System Integration Testing
- The PRA have Retail specific requirements for FDSF, and these requirements are peculiar to FDSF only. As a result of this, divisional data submissions for Retail FDSF are uploaded as new data sets within strategic regulatory platform and there is a need to test the integration of these Divisional feeds with existing strategic regulatory platform data. A set of reconciliation dashboards with reconcilable measures were designed for this purpose. These reports were built in OBIEE so that even the end users could download the reports and review the results. The dashboards were supported by detailed Excel based data files which help identify the reasons for differences expedite the resolution process thereon.
Business Acceptance Testing
- Providing a testing team with strong Retail business expertise, ACS proved that Business Acceptance Testing builds customer confidence in the solution provided and smoothens the UAT completion process. To this end, the testing team performed functional testing of the data and reports and ensured that all applicable business validations / checks were passed thereon prior to UAT commencement. With this testing already completed successfully upfront, the business acknowledged that approximately 40% of their test cases had already passed successfully at the initiation of the UAT phase.
Benefits
ACS believe that the testing phase should not just be used to identify issues, but should be used to correct the root cause of the issue itself. The testing team, with strong business expertise, were able to work with the development team to investigate and resolve root causes of testing problems. Other benefits include:
- Quick and accurate testing as a result of Automated testing.
- Time and cost savings due to early identification of issues.
- Improved business confidence and a smoothed UAT process, with minimal defects.
- Robust solutions validated by non functional testing including performance, data volume, transformation and negative testing.
For more information about how we can help you please contact us
Vijay Jadhav
Managing Consultant
BI Architecture
Recent Case Studies
[carousel-horizontal-posts-content-slider]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.