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

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

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

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.

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.

Thank you nick_white

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?

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.