fact table's foreign key related to multiple dimensions

View previous topic View next topic Go down

fact table's foreign key related to multiple dimensions

Post  mehrsa on Sun Nov 15, 2015 7:18 am

I have a fact table for collateral named fact_collateral and a dimension for loan named dim_loan and a dimension for bank-guarantee named dim_bg.
Collateral sometimes is used for loan and sometimes is used for bank-guarantee. I have another field ,named 'limit', that determine it's used for loan or bank-guarantee (when limit= 'L' collateral is related to loan and when limit= 'B' collateral is related to bank-guarantee).
How can I model this fact and dimensions?

mehrsa

Posts : 2
Join date : 2015-11-15

View user profile

Back to top Go down

Re: fact table's foreign key related to multiple dimensions

Post  nick_white on Mon Nov 16, 2015 9:30 am

Any reason why you can't create two fact tables: a loan collateral fact and a bank guarantee collateral fact?
When designing a fact you should declare the grain and then make sure you stick to it; if you have one fact that is associated to loans and one associated to bank guarantees then they are different grains and you can't/shouldn't have them in the same fact table

nick_white

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

View user profile

Back to top Go down

Re: fact table's foreign key related to multiple dimensions

Post  zoom on Mon Nov 16, 2015 12:34 pm

I would like to find about more to understand your question.

1) Please explain, in which dim that "limit" attribute exist ( dim_loan or dim_bg).
2) Is value of "Limit" mutually exclusive?... meaning a loan cannot have a value of limit= 'L' collateral is related to loan and limit= 'B' collateral is related to bank-guarantee at same time?
3) Do you keep a loan customer's demographic information in dim_dg or in a different dim?

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: fact table's foreign key related to multiple dimensions

Post  ngalemmo on Mon Nov 16, 2015 6:12 pm

Assuming it must be 2 different dimensions and one fact table, you need to implement the dimension as a sub-type cluster (in relational terms). In dimensional terms it is a dimension table with extensions.

First, there would be a dimension that contains both loans and guarantees. This table would contain attributes that are common to both. It also results in a mutually exclusive surrogate primary key for the dimension. Call this dimension "Instruments" or whatever is appropriate. You would also implement two other dimensions, Loans and Bank Guarantees. These dimensions would contain the attributes that are exclusive to the dimension. It would carry the same surrogate key as that of the Instruments dimension. Each of these tables would only contain entries for the particular type, i.e. Loans would only contain rows for Loans.

On the fact you store the Instruments key.

Queries that are not restricted by type would join to Instruments and use whatever attributes are there. If they are doing a query relating specifically to loans, you would use the Instruments key on the fact to join to both the Instruments dimension and the Loan dimension. The inner join to Loan would naturally exclude all Bank Guarantee facts since it does not contain entries for that type. Same idea with Bank Guarantee specific queries.

But... If the number of unique attributes between the two types is manageable, it is much simpler to just implement a single combined dimension table. Loan specific attributes will be null for a Bank Guaranty and visa-versa.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: fact table's foreign key related to multiple dimensions

Post  mehrsa on Tue Nov 17, 2015 1:39 am

I can't divide the fact_collateral to two facts. because each collateral could divide to some parts and each part assign to loan or guarantee. also loan and guarantees are separate dimensions.

mehrsa

Posts : 2
Join date : 2015-11-15

View user profile

Back to top Go down

Re: fact table's foreign key related to multiple dimensions

Post  nick_white on Tue Nov 17, 2015 5:20 pm

I think you need to clarify what the grain of your fact table is. If you can sub-divide your collateral into different business facts the grain of your fact can't be the collateral

nick_white

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

View user profile

Back to top Go down

Re: fact table's foreign key related to multiple dimensions

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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