First assumption that could CRASH your Credit Risk Data Warehouse project

Over the past two weeks I’ve covered a summary for the implementation of a Credit Risk Reporting project which is available in two parts, How to Implement a Credit Risk Data Warehouse – Part 1 and How to Implement a Credit Risk Data Warehouse – Part 2 holding the overview of the implications and a structured best practices approach to be considered when enrolling on the delivery of such a project.

Today I will focus on a common preconception that could have a severe impact to the schedule, resources, cost, development and ultimately the delivery of your Credit Risk Data Warehouse & Reporting project.

The preconception that I want to cover is “the data mart is a actually a quick and dirty data warehouse” and that you can bring it up without going to the trouble of developing an overall architectural plan for the enterprise.Also in the same reasoning it is considered that it’s to  complex to develop an overall architecture, and actually impossible to have the perspective to try that now.

First of all the data mart should NEVER be a quick and dirty data warehouse rather, it should be a single subject area implemented within the framework of an overall plan.

A data mart can be loaded with data extracted directly from legacy sources. A data mart does not have to be downloaded formally from a larger centralized enterprise data warehouse.

The key to a successful data mart strategy is quite simple. For any two data marts in an enterprise, the common dimensions must be conformed.

Dimensions are conformed when they are the same or one is a strict rollup of another. For example in an Investment Bank, if the cash in-flows is one data mart and the cash out-flows is another data mart, the two data marts will form a coherent part of an overall enterprise data warehouse if their common dimensions (time, instrument, source system) conform.

The “cash-in”s & “cash-out”‘s time dimensions might both be at the individual day level. Or perhaps the “cash-out” time dimension is at the day level but the “cash-in” time dimension is at the week level.

Because days roll up to weeks, the two time dimensions are conformed. The time dimensions would not be conformed if the “cash-out” time dimension was weeks and the “cash-in” time dimension was months – weeks don’ t roll up into months – and the two data marts could not usefully coexist in the same application.

The beauty of conformed dimensions is that the two data marts don’ t have to be on the same machine and don’ t need to be created at the same time. Once both data marts are running, an over-arching application can request data simultaneously from both (in separate queries) and the answer set is likely to make sense.

Logically, the only valid “row headers” in a joint report must come from common dimensions such as time and instrument in our example but we have guaranteed that at least some of the row headers from these two data marts will be in common because the dimensions are conformed. Any of these common row headers can produce a valid report.

The idea of developing an overall data warehouse architecture is daunting, but the key step in that architecture plan is simple:

  • Identify the common dimensions.

In virtually every bank, the most important common dimensions are customers, instruments, geographies, and time frames.

Once the common dimensions have been identified, the development of separate data marts must be managed under this common dimensional framework.

When two data marts use the same dimension (for example, instrument), they must agree on a definition of “instrument” at a very detailed level. Either the two customer lists must be identical, or one must roll up to the other.

Make sure that an overall plan is developed that takes into consideration Entreprise wise reasons rather than focusing on a single area of your Credit Risk Data Warehouse.

When embarking on a journey like this the main concepts that you must keep in  mind are :

  • Business value
  • Coherency
  • Evolvability
  • Scalability

I usually recomend the above to be printed and used to challenge the architectural decisions during the Desing & Planning of the Data Warehouse.

Until next time,

                                Keep learning, Keep searching and Keep succeeding…

Let’s connect on : LinkedIn

Follow me on Twitter : @lgleonte

Article originally published on: Linkedin – First assumption that could CRASH your Credit Risk Data Warehouse project