Dimensional Modelling of a Bank ; Can Loan be considered as a Dimension ???

View previous topic View next topic Go down

Dimensional Modelling of a Bank ; Can Loan be considered as a Dimension ???

Post  Math_1729 on Sun Sep 21, 2014 11:21 pm

My team is designing data-warehouse for a bank, due to huge number of facts linking to Loan number (unique for each loan)  we have put Loan as a dimension which contains all the non changing fields of Loan (Account no, amount, Customer...) but now as Loan is a dimension all the other dimensions like Customer, Loan Scheme etc are connecting to both loan dimension and fact table  
My question is can Loan be a Dimension ??? I am attaching a sample model of my design

Math_1729

Posts : 2
Join date : 2014-09-21

View user profile

Back to top Go down

Re: Dimensional Modelling of a Bank ; Can Loan be considered as a Dimension ???

Post  nick_white on Mon Sep 22, 2014 7:46 am

Hi - yes, Loan can be a dimension if Loan has textual attributes that you wish to use to filter/aggregate your facts by.

Please bear in mind that Dimensions are logically joined to each other via facts, not directly. So all the red lines in your diagram should be conceptual/logical joins not actual foreign keys in your DB i.e. you would not have an FK column on your Loan Dim that references the PK on your Time Dim

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Dimensional Modelling of a Bank ; Can Loan be considered as a Dimension ???

Post  BoxesAndLines on Mon Sep 22, 2014 9:44 am

Loan can be a dimension. It is best modeled as a degenerate dimension. The problem with instantiating the loan dimension is that it is nearly always 1-1 with the fact table. It also ends up being a bucket for just about any attribute. A better approach is to model loan attributes into other dimensions that do not have a 1-1 relationship with your fact tables. You will end up with a couple of junk dimensions to handle the odd attributes that defy any meaningful classification.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimensional Modelling of a Bank ; Can Loan be considered as a Dimension ???

Post  ngalemmo on Mon Sep 22, 2014 11:21 am

Just ignore the red lines and you are fine.

It is ok to store a FK to another dimension as along as you don't snowflake your facts. The key is used when loading a fact, not when you are querying a fact. ETL would use the key in the dimension table to populate the key in the fact. End users use the fact key when querying and would not 'see' the key column in the dimension.

As far as Loan goes, it would be worth looking into breaking down the dimension further. It may be possible to store the loan number as a degenerate along with a group of more specific keys, by breaking down and grouping the attributes of the loan. You can maintain a structure of loan number and the related keys, and use it during ETL to store the loan number and dimension keys into the target fact table. You would have much smaller dimensions and better performance.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimensional Modelling of a Bank ; Can Loan be considered as a Dimension ???

Post  Math_1729 on Thu Sep 25, 2014 6:57 am

Thank you nick_white

Math_1729

Posts : 2
Join date : 2014-09-21

View user profile

Back to top Go down

Re: Dimensional Modelling of a Bank ; Can Loan be considered as a Dimension ???

Post  Jeff Smith on Thu Sep 25, 2014 11:13 am

I'm curious. I would see Loan as an account, like a deposit account. The reason being that Loans and Deposit accounts can use the same transactions mechanisms. And some Loans act a lot like deposit accounts. I can use an ATM, go online, use the telephone, or even a teller to withdraw money from a home equity line or checking account or to deposit/make a payment to the deposit/credit account.

Would you put loans and checking accounts into different dimensions and if so, how would they connect to a fact table storing transactions? If they were seperate dimensions, wouldn't one of the dimension key values always be -1 when the other was not -1?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Dimensional Modelling of a Bank ; Can Loan be considered as a Dimension ???

Post  BoxesAndLines on Fri Sep 26, 2014 2:46 pm

It's all in the semantics and how the model and your business interprets those semantics. Is an account something that holds products such as mortgages, checking, savings, ATM, LOC's, car loans, credit cards? Or are all those things managed independently?

I would have a different fact for loans, checking, savings, etc. Sure they look the same but the characteristics, and more importantly, the reporting metrics are different. I would also have an account dimension that consolidates all of the products to my customers. I wouldn't have a Loan dimension since it is 1-1 with my loan fact.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimensional Modelling of a Bank ; Can Loan be considered as a Dimension ???

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum