For a major financial client, GDI built Data Quality function to guarantee integrity of the data from disparate source systems that were incorporated into the Enterprise Data Warehouse
The Challenge
Client Background
GDI Client is among the twenty largest banking companies in the U.S. with $62.3 billion in total assets; $41.2 billion in total deposits as of 31 December 2007 and 11,600 employees. The operating units include corporate banking, small business banking and personal financial services. This financial services company is headquartered in Dallas, and is strategically aligned into three major business segments: The Business Bank, The Retail Bank, and Wealth & Institutional Management. In addition to its primary U.S. locations in Michigan, California, Texas, Arizona and Florida, their offices can be found in select high-growth markets across the U.S and there are select business operations in Canada, Mexico and China.
Project Objective:
The client organization was undergoing a major business initiative – building Enterprise Data Warehouse. Objective of Project INSIGHT was to gradually incorporate over 40 disparate financial systems and applications into the data warehouse. First phase was to integrate and clean the data from 4 major applications feeding information to the AML (Anti Money Laundering), KYC (know Your Customer) and Fraud Detection areas. GDI was trusted to set up and execute Data Quality as a function for Data Management Office (DMO). Without it the client ran a risk of not being able to load data warehouse on time, and not being able to use the quality data for compliance.The Solution
Technology Employed:
Based on the Informatica PowerCenter platform used in the data warehouse environment for ETL, a decision was made to use Informatica tools for Data Quality as well. GDI expert used the Informatica Data Explorer and Informatica Data Quality toolsets. Enterprise Data Warehouse was based on IBM DB2 database. PowerExchange was used to transition mainframe data to data warehouse.
Project Details:
Project’s objective was to integrate data from customer information system, human resources system, retail and commercial loan systems and vendor master system into set of relevant information in the Enterprise Data Warehouse. Initial usage of this information was to feed the EAS application for resolving the customer identity including all Involved Parties and their relationships, alternative names etc. This would empower the client organization to relate transactions the customer makes on an account regardless of the different variations of customer data. GDI was engaged into identifying the data anomalies and cleansing the data prior to EAS, so that had much higher chance to resolve the matches and associate the entities.
Key Responsibilities:
GDI consultant had a leading role in the full life cycle of Data Quality effort for the data warehouse. He built a solid data foundation not only performing the complex technical functions, but also led efforts of a group of business analysts, ETL developers, and users in order to identify and resolve data issues.
The scope of work was broad, and accordingly the level of risk was high. Effort entailed multiple major steps. Before the data profiling and data cleansing could be implemented team had to install the tools and implement the data quality methodology. Hundreds of data attributes were profiled and evaluated for data quality issues. Millions of records were processed for data quality benchmarking. Identified problems were prioritized, and resolved via technical methods and business rules implementation.
- Assessed the data quality of the information source systems. Identified the data quality issues. Developed data quality rules and processes for cleansing data. Facilitated their approval by business users and data owners.
- Evaluated data anomalies and designed and implemented data cleansing and enrichment routines.
- Resolved inconsistencies in the data and software resulting in acceleration of successful data loads in the enterprise warehouse.
- Estimated efforts for new data quality projects and enhancements; allocated resources, planned and scheduled implementation.
- Trained a group of 12 business analysts and ETL developers in data profiling techniques, led their effort and mentored them along the way to expedite the delivery to meet the critical timeline of the data quality deliverables.
The Results
The engagement of GDI expert in the Project INSIGHT execution had a significant positive impact on project results and on enterprise data quality as a whole. Few times during the project the creative approach taken by GDI consultant saved weeks of the development time. When tool vendor could not resolve for four months the software bug, GDI expert developed a work-around that kept project moving in spite of fact that bug was only fixed in the next software release.
First time in the history of the client’s address processing, the commercial strength Address Verification was fully implemented in house. This has given the bank a full control of address processing, deep understanding of address handling, and saved the bank expenditures on sending the addresses to third party for cleansing. Process was uniformly applied to several systems providing a standardized approach with consistent results.
The benefits went far beyond the project INSIGHT. The process templates and patterns were successfully reused by subsequent phases of KYC project which had serious data quality issues prior to engagement of GDI data quality expert.
Many data exceptions dictionaries were built and used in data cleansing. Their content reflected data quality issues both generic and very specific to the client organization data.
Future data integration at the client organization will have a data quality platform solid enough to build upon, and flexible enough to be modified to changing needs.