fact table's foreign key related to multiple dimensions
4 posters
Page 1 of 1
fact table's foreign key related to multiple dimensions
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?
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
Re: fact table's foreign key related to multiple dimensions
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
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 : 364
Join date : 2014-01-06
Location : London
Re: fact table's foreign key related to multiple dimensions
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?
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
Re: fact table's foreign key related to multiple dimensions
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.
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.
Re: fact table's foreign key related to multiple dimensions
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
Re: fact table's foreign key related to multiple dimensions
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 : 364
Join date : 2014-01-06
Location : London
Similar topics
» Do I need multiple fact tables or dimensions
» Multiple different grain fact tables with lot of common dimensions.
» Composite Dimension Natural Keys - Attribute Used In Multiple Dimensions as well as Fact Tables
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Modeling Related Fact Tables
» Multiple different grain fact tables with lot of common dimensions.
» Composite Dimension Natural Keys - Attribute Used In Multiple Dimensions as well as Fact Tables
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Modeling Related Fact Tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum