‘Data, data everywhere | But not an insight in sight’
Data warehouse and business intelligence system for leading bank undergoing merger
This banking industry client was planning a major merger with another bank. The problem was data management. They needed to build a business intelligence and data warehouse system from scratch including algorithms supporting data reconciliation. The goals were to meet regulatory requirements, facilitate a smooth merger, and improve company productivity by shifting control of report generation from a third party vendor directly into the hands of the internal business users. This would require a custom algorithm, internal training, and a comprehensive understanding and application of business sources and priorities.
Merger in the making
This financial services client is among the largest banks in the U.S., with over $3.5 billion in total assets and almost 50,000 employees. In 2008 it was planning a merger with an equally large bank. In order to comply with internal and external reporting requirements associated with the merger, they needed to consolidate and run reports on data from both banks and from about 25 different sources. They also wanted to improve operational efficiencies overall and efficiencies specific to the merger in particular.
Big data but limited access
At the time of our engagement the process the business user had to go through to retrieve data and prepare reports was long and complicated. A third party vendor could source and deliver documents, but the reconciliation was largely an internal, manual process. Customized reports and open-ended queries were not available.
As an example, if a portfolio manager needed a financial profile specific to a geographical area, risk rating, or market share, they would need to request documents via a third-party vendor from 25+ different sources (e.g., marketing, finance, regulatory, sales, operations…), each having its own method for tracking a loan or a borrower. So, if one source used the social security number for primary tracking and another source used the loan number or the primary residential address, those documents would need to be compared, confirmed as pertaining to the same borrower, lender, and/or loan, and reconciled. All of this was largely a manual process.
The goal of this project was to change all that–to meet compliance requirements and to gain operational efficiencies. The project would involve building a data warehouse, loading all data into one repository, and developing a business intelligence (BI) system that would allow users to build reports and analytics on top of the data warehouse. The data would be cleansed, consolidated, automatically updated, accurate, reliable, and easy to interact with. In this way users would have full access to the data and full control over analyzing, parsing, and report building.
Approach and timeline
We jointly assembled a team consisting of key stakeholders and skill sets on the client side and complementary skills within Integrant. On the client side we worked with the head of the portfolio oversight group plus subject matter experts in marketing, sales, finance, operations, regulatory, and other groups. The Integrant side included a solution architect, a technical project lead, business intelligence developers, and QA engineers.
The client operated within an Agile environment. Leveraging this approach, we jointly identified top priorities and decided to roll out the project in two phases, including weekly sprints and daily interaction. The goal was that within six months we would have completed what was necessary for regulatory compliance surrounding the merger. Phase I would enable portfolio oversight but not from all 25+ data sources. It would allow high level reporting about the overall portfolio that management needed. The following months would be dedicated to phase II, which would incorporate all sources and would fine-tune functionality, user experience, etc.
Tools and technology
At the data warehouse level we used the Microsoft BI stack: SQL server integration services (SSIS), SQL server analytic services (SSAS), SQL server reporting services (SSRS), and transactional SQL (TSQL).
Our main BI tool was Microstrategy, which allowed us to build reporting and analytics on top of the data warehouse.
System performance was an important aspect of the project. We used the SQL server, enterprise services edition, to handle the system’s volume and performance expectations. This allowed us to achieve additional features and requirements like disaster recovery (with colos in California and Arizona) and partitioning for performance purposes.
In addition, the security expectation requirements were high. We utilized a secure colo with controlled access. We complied with strict encryption and security practices. The entire database was encrypted and all data was encrypted during transmission.
Addressing data reconciliation
With respect to the necessary reconciliation of various data formats and sources, we developed an algorithm to address this. The solution was a function of both architecture and BI. The result was that a business user requesting a report that required pulling data from multiple sources and formats (e.g., an Excel sheet containing 400 columns, a PDF bank statement, an Access database file) could deliver reconciled results in real-time.
Setting users up to hit the ground running
The new BI system would offer them a new way of doing business and one they had been struggling without for years. There would be no need to rely on/wait for outside vendors to collect data, and no need to parse and compile internally upon receipt. As we were building the data warehouse and the BI system, we wanted to train the business users so they could leverage the new system immediately.
Key aspects of user training included:
- We trained on the BI system and tools to encrypt data and transfer files.
- We worked closely with the users so they could speak the language of data warehousing and BI for purposes of communicating changes, enhancements, and fixes.
- We started training early on–a few months into the project–so at production time business users were ready to hit the ground running.
With training completed, the phase I launch occurred as scheduled at the end of six months. The merger proceeded successfully with no regulatory or compliance issues.
Beyond the merger, the business users were happy to get their hands on the final product including all data sources in the coming months. They now had easier, faster data access, and they had control over how that data was analyzed and presented.
We perform a yearly audit and SOC audit with this client. Performance tuning and enhancing is an ongoing process. We work consistently to achieve data accuracy and the fastest possible delivery times. Although development of the BI system was a necessary tool for merger compliance, the most significant aspect of the project has been the enhanced control and creativity the system puts in the hands of the business users. The ongoing success of this partner within the financial industry would indicate that the investment paid off.