Implementing Basel II could be an overwhelming job. There are so many parts that need to be in place before the Basel II can run successfully. This article provides a data warehouse framework for implementing Basel II. This article is for project managers, architects and business sponsors who are involved in implementation of Basel II. As the implementation deadline is nearing, all the financial institutions across the world are gearing up to implement the accord. This article provides a framework for this implementation and challenges faced during the course of implementation. This article provides techniques, strategy and architecture for a fast and successful implementation of the accord. The reader should have basic understanding of Basel, banking and data warehouse concepts.

 

Objectives of Basel II

 

The success of any data warehousing project comes downto the level of understanding of the business processes or requirements. It is very critical to understand the requirements of Basel II before implementation. Some of the key objectives are: safety and soundness of the financial system, maintaining the overall level of capital, alignment of regulatory capital with underlying risks, a flexible structure - incentives for better risk management, creation of a level playing field, equal treatment, leading to equal opportunities and more.

 

This article focuses mainly on implementing one of the major Basel II pillars, credit risk. Other pillars of Basel II, such as operational risk, market risk, etc. could be added as an addendum to the framework.

 

The Team

 

Figure 1 provides an outline of different stakeholders in the implementation process. The different players have set of different roles and responsibilities in the implementation processes.

 

  1. Business Sponsors - sponsors the project and is responsible for the budget of the project. He/she should be effective in communicating the importance of the project for the enterprise to senior management to the cash for the project.
  2. Planning Team - provides direction to the project and resolves conflicts.
  3. Governance - the governance board defines guidelines and policies for the warehouse.
  4. Senior management - IT/business senior management keeps track of the project on a bi-weekly or monthly basis.
  5. Credit policy - interprets Basel and provides the requirements.
  6. Business operation - validates the data and the processes.
  7. Requirement/business analyst - interprets the requirements.
  8. Operation team designs, develops and supports the application.

The Model

 

Figure 2 provides a conceptual model for implementing the warehouse. This is a conceptual model of the data warehouse from which single or multiple data marts can be developed as per the requirements. The implementation approach could be based on Inmons or Kimball’s philosophy. The key dimensions and facts in the model are:

 

  1. Instruments - a drawing or actual loan taken by the borrower (customer). This dimension contains attributes like channel, start date, end date etc.
  2. Facility - a loan/credit to the customer to draw money. This dimension contains all the relevant attributes of a loan like interest rates, start date, maturity date etc.
  3. Collateral - contains all the information about the collateral pledged against the loan by the borrower. Attributes in this dimension could be collateral type, lien type, collateral appraisal date etc.
  4. Customer - borrower attributes such as name, address, Social Security Number/tax payer identification number, etc. are captured in this dimension table.
  5. Ratings - the individual and commercial borrower internal/external rating information are stored in this dimension.
  6. Guarantee - stores the attributes relevant to the guarantee provided for the loan.
  7. Products - all the product attributes are captured in the product dimension.
  8. Probability of default/Loss given Default.
  9. Facility fact - all the measures of the loan are stored here. There could be additional facts depending on the specific requirement. 

The Implementation Tracks

 

The different tracks in the implementation process are shown in Figure 3. This provides a high-level overview of the different tracks that need to run for a successful implementation.

 

  1. Requirements gathering - This track plays an important role in the overall success of the implementation. Developing a data warehouse is an iterative process so we can’t expect a 100 percent correct implementation the first time. The requirements could change as we build the data warehouse. The focus should be to get the requirements as correct as possible.
  2. Architecture - The architecture is somewhat like the foundation of a house. Building a house on the wrong foundation could lead to a bad house structurally, decreasing the life of the house, etc. Similarly, building a data warehouse with the wrong architecture could lead to disaster such as total failure of the project or inefficiencies in end-user usage.
  3. Data modeling (build versus buy) - The data model can be built from scratch or can be bought from the market and customized it. Both have pros and cons.
  4. Source application enhancement - There could be a requirement to capture new business attributes per Basel II. This could have an impact on the operational systems and could be a time-consuming process.
  5. Source data extraction - The source data is extracted and provided to the data warehouse by this group. Some of the important things to be looked after in this group are standardization of data extraction, capturing change data and extracting the right attributes as per the requirement.
  6. Risk weighted calculation.
  7. Credit scoring
  8. Web/GUI - The GUI group works on developing front-end applications for data visualization and maintenance.
  9. Extract, transform and load.
  10. Customer data integration (CDI) - All the major data warehouse implementations face the challenge of generating a unique customer. The ideal solution is to implement it as part of the CDI solution and bring unique information to the warehouse.
  11. Data quality - The data profiling option is part of the data quality process that is used for doing a quick analysis of the data sent by source systems. It helps in doing the analysis on row count, distinct value count, aggregate, domain inference and redundancy evaluation. One of the major challenges faced in the implementation process is the quality of data and implementing the process to correct it.
  12. Metadata - The metadata application develops a solution to capture the metadata across all steps of the process.
  13. History data - One of the requirements of Basel is to capturehistory data. Some of the challenges faced are capturing the history data from the operational systems, and integrating the history data with the ever-changing data warehouse processes.
  14. Information delivery - This track is to develop a solid foundation for the presentation layer and one of the important task for this group is educating and familiarizing the user with the data warehouse processes, model and data.
  15. System support - The system support group focus should be to maintain and support the production system. 

The Architecture

 

The different high-level architecture stages of implementing the process are:

 

  1. Source systems - In this stage, the focus is to extract the data from the operational systems.
  2. CDI - One of the major challenges in the implementation is defining a unique customer and its attributes that are solved by implementing a CDI solution.
  3. Staging - This is a landing area for the operational data. The data quality checks could be done in this area.
  4. Warehouse - The warehouse integrates data from different operational systems.
  5. Calculation engines - The calculation engines calculate the factors required in Basel II by taking data from the warehouse.
  6. Data marts - The data marts are specific to the requirement from the business. Depending upon the requirements, there could be a single or multiple data marts.
  7. Reporting tools - This is the information delivery layer of the implementation process.

 

Figure 4 and Figure 5 provides the different architecture that can be followed for the implementation.

  Tool Selection

 

The selection of tools plays an important role in the success of the project. You need to have the right correct and latest weapons to win a war. There are many moretools available in the market than what is listed below. A careful examination of the tools could result high ROI and success of the project.

 

  • ETL - Ascential DataStage, Ab Initio, Informatica PowerCenter, SAS, custom build.
  • Reporting - Essbase, Business Objects (Crystal Reports), Cognos, custom build etc.
  • Data quality - Trillium, Informatica, custom build, etc.
  • Meta Data - Informatica Superglue, custom build, etc.
  • CDI - Custom build etc.
  • Modelling tools - Erwin, etc.
  • Data models - IBM BDW, PeopleSoft EPM, etc.
  • Databases - Oracle, Sybase, etc.
  • Hardware (parameters, CPUs, speed, RAM) - HP-UX, SUN etc.
  • Scoring model/tools.
  • RWA (risk weighted assets) engines.

Right Strategies

 

Putting the right planning and strategies before actual implementation of the project could pave the way to success. Some of the standards and strategies which should be put in before beginning the project are shown in Figure 6.

  Enterprise Metadata

 

Banks have to create an enterprise metadata infrastructure that enables business and IT users to navigate through the various application and product layers for productive analysis. This also helps external auditors during the review process. Enterprise metadata will help to:

 

  • Design lineage reports.
  • Show the data flow from the data source, through various systems and staging areas to the data destination. This information helps determine where data comes from and where it is going
  • Create where-used analysis reports.
  • Analyze the business intelligence system, such as report information, user activity and how long it takes to run reports.
  • Analyze dependencies between objects in a repository, e.g., impact analysis reports show the dependency between a mapping and the sessions that use the mapping.
  • Create data integration reports.
  • Provide a set of data integration reports that analyze data integration operations.
  • Identify data integration problems and analyze data integration processes

Issues and Challenges

 

  1. Internal politics - There are multiple departments/vendors and people who need to be working in congruence to make the project a success.
  2. Data ownership - Lack of ownership for the identified data issues.
  3. Foresight - The architects, managers and business sponsors should have foresight of value add based on utilizing the resources in an efficient way. The implementation should be such that it not only caters to Basel II but is utilized in other day to day operations.
  4. Tactical solutions versus strategic solutions - Implementing tactical solutions helps in reducing implementation time, cost and effort, but the risk is high that it will stay forever. In most cases, the tactical solution becomes a part of the production process, and the strategic solution never comes into picture.
  5. Foundation - A house built on a good foundation lasts longer.
  6. Teams - Implementation warrants involvement of different teams and vendors, and each has his own interests and agenda.
  7. User education - Educating the users about using the reporting tools or about the model is very important to the success of the Basel II implementation.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access