Dimensional modelling in Retail Banking Industry

The retail banking also known as consumer is banking provides services such as savings and checking account, mortgages, personal loans, debit/credit card to individual customers through local branches. Banks facilitate transfer of funds between accounts, currency conversion, auto transfer between accounts, bill payments etc. The major source of income for banks is the difference between interest paid by customers for loans and the interest paid to customers for having funds in savings account. Additionally banks earn revenue from monthly maintenance fees, conversion charges and fee for various banking activity such as Swift transfer etc. Banks are regulated by a federal agency and have to abide to rules and regulations laid down by them. Certain regulations include maintaining a certain percentage of liquid funds at all times, reporting fraudulent transaction and customers etc.

As discussed in the above paragraph banks earn their bread and butter by lending money to borrowers and pays some part of it to customers who maintain funds in their savings accounts. One of the important business metrics that the CEO would be interested in while evaluating performance of a bank is the total funds product wise in a financial quarter. He would also be interested in evaluating the number of accounts under each product and the number of active customers for a quarter. Such data can be used to analyze how the distribution of funds is spread across various products and help implement strategy to maintain liquidity. Additional metrics such as number of accounts under each product and numbers of customers when compared across quarters provides QoQ statistics and can help business teams to come up with products and promotions to increase customer base for a particular product or service.

The banking industry is becoming increasingly dependent on information technology to retain its competitive edge and adapt to changing market scenarios. Every day as a result of the sheer volume of transactions that take place in a bank, enormous amounts of data is produced. Yet most of this data that can be used to gather strategic information remains locked within archival systems. Dimensional modelling of such information can be used to generate reports that can be used by corporate heads while making decisions regarding strategy. Reports can also be generated for compliance issues. The lack of consistent data restricted the use of model based decision making.

Dimensional modelling can help the business processes in the following ways

1.        Collate data from multiple sources and create a single consistent view.

2.       Quick ad hoc queries to support real business questions

3.       Help maintain flexibility and scalability.

4.      Optimize user end to end experience by encapsulating the underlying model.

Considering the metrics we earlier described about quarterly information of funds across products and customer segments, a periodic snap shot fact table would be the appropriate selection. A snapshot of the account balance for account belonging to various products and customers will be uploaded at intervals of quarters. This information can then be used to generate report of the total funds under each product or customer segment type for a quarter.

A sample dimensional model is shown below.

The dimensional model shown above provides one way in which a model can be created to provide quick statistics for decision making.