Pilot Verification and future road mapping for a user centric Data warehouse application

1. Pilot Verification and future road mapping for a user centric Data warehouse application Sundaresa Subramanian Hardik Patel Nikunj Damani Infosys Limited (NASDAQ: INFY)

2. Abstract Financial institutions rely on reporting as a key indicator – both for operational efficiency and to monitor their health status. BFSI in the near past was talking about ‘just in time’ reporting – reports that could be generated quickly based on the needs and produced. BFSI in the current mode is all about ‘slice and dice’ reporting – intelligent reporting mechanism that not only provides you information but gives various perspectives to it. Reports that can be sliced and diced. Merely being ‘on time’ is not enough in reporting today

3. Need for conference room piloting strategy? • Banks have to redesign or transform their existing data warehouse system to something more ‘efficient’. • By ‘efficient’ we mean to say that the new solution should take ‘optimal time’ to perform a business • function, consume ‘optimal cost’ and realize ‘optimal business benefits’. • Time, Cost and Benefit are always in a Mexican-standoff with one compromising the other if proper • planning is not done for the entire program. • The best way to approach these type of futuristic programs is to adopt a “piloting” strategy where a set of • critical business functions are implemented on a pilot basis for the transformation project. • This type of Conference room piloting requires agility, future vision, overall domain knowledge and testing • acumen from the QA team since they act indirectly as architects for the holistic solution. • Through this paper we intend to discuss best practices from QA effort for a pilot data warehouse reporting and transformation program for regulatory compliance domain of a Bank.

4. Background • The primary aim of data warehousing is to make business-worthy data available as quickly and as accurately as possible. Let us consider the example of a complex data warehouse system for risk and compliance LOB of a financial institution. The typical reporting can be viewed in 4 different types as shown in Figure 2: • Management reporting for strategic decision making • Business user specific reports • Regulatory compliance – External and internal audits • Capital adequacy specific reports – such as Basel II & III

5. Characteristics of a Risk & Compliance data warehouse

6. Challenges faced in transformation projects • There are multiple challenges involved in a typical transformation project of such magnitude where a legacy data warehouse application is transformed to a modern technology/architecture. Let us analyze those challenges/problems from various points of view:

7. Why this Paper? Conference room piloting helps eradicate the ‘fear of unknown’ while transforming to a new technology. It allows the project team to sample (or) pilot the new technology on a specific module or business flow first. The output is analyzed and lessons learned out of this conference room piloting exercise are explained to the business user. In CRP, the business user plays a quick and early role in determining the success of the new technology by providing feedback based on the pilot.

8. Traditional data warehouse transformation projects – Big Bang Figure 3: Traditional data warehouse transformation projects Demo of new technology High level transformation roadmap Architecture Level changes Coding & Build Design Changes System Testing User Acceptance testing Error identification points

9. Conference Room piloting – Sampling the solution first… Figure 4: Conference Room Piloting Demo of new technology Sample business flow or critical scenario identified for transformation Development & mini-build Signoff User review of the pilot transformation Flash testing Traditional development cycle Transformation roadmap based on lessons learned & best practice from pilot Error identification points

10. Conference Room piloting with early QA involvement • Though conference room piloting would provide everyone a sneak peak of new technology, QA was not involved from very early stages in the lifecycle. If the QA team has good knowledge on the existing application then their domain and technical acumen could be used as valuable inputs during conference room piloting of a module. This gives rise to the concept of early QA in conference room piloting, where the QA team would be involved in every stage of the conference room pilot activity adding value to the entire process.

11. Application of early QA in conference room piloting 1 • Objective selection criterion Existing data warehouse gets its data from 80 different source systems – about 40% of the systems are sourced directly and another 50% of the source systems pass through intermediate stages and roughly 10% of source systems have a combination – few data elements are sourced directly whereas few other data elements are passed through intermediate stages. Flow is illustrated in Figure 4. Various lines of businesses such as Mortgage, Reverse Mortgage, Auto Loans, Personal Loans, Aircraft/Boat loans and student loans are served by these source systems. The data warehouse is revamped (new database, new reporting routine). There is a need to identify one or two source systems for Conference room piloting purpose and the systems have to be selected from a group of 80 systems. 11

12. Figure 5: Various ways of sourcing data to data warehouse • Early QA involvement - Objective selection criterion Direct sourcing Indirect sourcing Direct + Indirect • Criticality of the source systems – based on defects • Coverage of all business flows – example Mortgage and Loans • Prioritization based on type of sourcing - both direct and indirect sourcing Source systems Source systems Intermediate DB Data warehouse 12 Data warehouse Data warehouse

13. Application of early QA in conference room piloting 2 • Minimize upstream dependency For an existing data warehouse program, a request has to be placed to the upstream to send test file for every iteration of development, system testing or UAT. Upstream followed its own release cycle and calendar and so test data requirements had to be communicated well in advance. The problem faced due to this scenario was that any adhoc test request was not honored by the upstream. Moreover, the upstream data sometimes did not cover all the test scenarios as defined by the QA team. When the data warehouse program was transformed and redesigned to a new technology, one of the critical requirements during conference room piloting was to decide a test data strategy that minimizes upstream dependency. 13

14. Early QA Involvement - Minimize upstream dependency • Existing data warehouse’s production data is analyzed • QA team considers this production data as a ‘baseline’ for implementation of this new transformation program. • QA team prepares a test strategy to extract this production data to a temporary database, process it and use it as “Golden copy reference’ for any new test data requirements.. • Since upstream dependency for test data is minimized because of this strategy, the project team readily approves this suggestion and starts implementing it. 14

15. Advantages of early QA in conference room piloting • Early involvement of QA team during CRP helped in their better understanding of the target architecture and hence led to effective test approach, planning and execution. • By following CRP approach, fewer defects were identified during the actual QA verification as the critical defects were identified and fixed during the POC. • There were no design gaps identified as each module POC was certified by QA team during CRP. • A significant cost saving was achieved in the project by minimizing upstream dependency. • Business stakeholders were very confident on the quality of the application due to exhaustive testing performed by QA in CRP. 15

16. Conclusion – Revisiting the objectives 16

17. Revisiting the objectives…continued 17

18. Q&A 18

19. References • Infosys project experience • Infosys resources (www.infosys.com) • www.wikipedia.org 19