Dimensional Modelling - Loan Dimension in Banking Data warehouse - Part 1

Loan Dimension in a Banking Enterprise Data warehouse is very crucial. It is undoubtedly one of the if not biggest data warehouse dimension in a banking DW architecture. It is also of one of the most confirmed dimensions in an banking data warehouse. Loan dimension has a lot of textual attributes and also an almost equal number of measurable attributes. It is also joined to many other entities of a banking EDW. A few examples of the key entities are the following,

• Branch 
• Primary Customer
• Teller
• Loan Product
• Time/Dates ( Maturity, Commencement, Settlement, Disbursement, Dormancy )

As we discussed before there are many textual attributes are unique to a loan dimension and few examples are listed below,

Account status,
Product Name
Settlement, Account open date, Loan Approved date, First disbursement date, Last Transaction date.

Considering the size of a loan dimension it can be split into multiple tables to improve performance and maintainability. A good way to start modelling a dimensional model is to create bus matrix as suggested by Ralph Kimball.

The bus matrix for a Loan dimension in an ideal scenario would look as below,

Bus Matrix for Loan Dimension.

The loan dimension being one of the largest entities will take up a number of columns. It is a good practice to split the dimension as follows in case it has a large number of table,

The first partition of the dimension table should have all the most commonly used attributes, like Status, Balance etc. The second partition should have all the measurable attributes like Approved amount, applied amount, and the corresponding date keys. These measures can also act as attributes in some cases. The second table can also be used as a fact in some cases. The third or the next partitions should have all the junk values related to the dimension. Advised to put all the rarely used dimension columns here. These tables can be made to one single dimension in BI tool for example with the help of Logical table sources in OBIEE.

Why we are advising this strategy is because otherwise the maintenance of this table would be down the line a headache. Imagine a table with 500 columns. A single insert statement would be of large size. Hence taking more time to execute and load into target schema.

Naming conventions, You can go ahead with standard naming conventions followed by Oracle or have one of your own. An Oracle naming standard would be,

W_LOAN_D

If you have multiple sources, Then you can have a source abbreviation also in the same name.

Banking Domain - Types of Banking - Retail, Commercial and Investment explained

Currently working in a banking domain project and being new to the domain I felt very odd among my peers. This is an attempt at learning banking domain from an Analytics/Data warehouse perspective. 

The first and foremost question to start is with what is bank and banking ? Below is how google puts it for you.

Banking is a broad term that includes any function that is performed by a bank.  

There are mainly three types of Banking

• Retail/Consumer Bank
• Investment Bank
• Commercial Bank

Types of Banking - Explained

Retail or Consumer banking 

are financial institutions that deal with individual customers and SME's. 

1. Cater to Individuals and Small/Medium enterprises. 
2. Target audience is mostly individuals and SME's. 
3. The amount of money involved here is comparatively less.
4. Low risk banking model.

Investment Banking 

1. Caters to Corporations, Government organizations and financial situtations.
2. Target audience is corporations, financial institution
3. The Amount of money involved is very high compared to retail banking.
4. High risk banking model

Commercial Banking

A commercial bank primarily works with businesses

1. Caters to individuals, small investors as well as large enterprises.
2. Amount of money involved is high