- 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,
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.
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.
Interesting post about a Loan dimension. One question: when you affirm that I can put the "Approved amount" in this dimension I wonder that this is a measure and would be stored in our Loan Fact table, Isn't? Or must we use "Approved amount" both as a Dimension attribute and a measure for a loan? Cheers!
ReplyDeleteGlad you liked it. Approved Amount is a definitely a dimension attribute. If you have it in the fact, it will repeat for each transaction row under the loan.
DeleteWe had another fact designed in our datamart which was at the grain of one row per loan record. You can have the approved amount here.