Adding mini dimension to bridge table
2 posters
Page 1 of 1
Adding mini dimension to bridge table
Hi,
My question relates to design tip #136 http://www.kimballgroup.com/2011/06/design-tip-136-adding-a-mini-dimension-to-a-bridge-table/
I have a similar situation to the example in the link, I have a Customer Dimension (100+ attributes), Account Dimension(60+ attributes) and a CustomerAccountBridge table.
To reduce the width of my customer dimension, I have several mini dimensions that are created out of the base customer dimension:
Customer Demographics
Customer Segmentation
Customer Preferences
Kimball recommends to put the mini/junk dimension surrogate key directly into the CustomerAccountBridge table, however I was wondering if my alternative would be ok:
Instead of putting the surrogate key into the CustomerAccountBridge, I create a Factless Fact table to relate all the mini/junk dimensions and base customer dimension together to track changes ie:
Factless Fact Table
Every time the base customer record changes(SCD 2) I insert a row into the Factless Fact table to track the change and updating the EffectiveTo date of the old record.
Similarly, every time a base customer record needs to point to a different junk dimension row, I also insert a row into the Factless Fact table and update the EffectiveTo date of the old record.
The reason I wanted to do it this way is because the VIEW I create for the business on the Customer would then not need to navigate through the CustomerAccountBridge table which would require a DISTINCT in the query as 1 customer can have many accounts potentially.
Is this a valid reason?
What are the pros and cons of doing it my way vs the one provided in the link?
Also, is it valid to put attributes like "EmailOptOutDate" and "AdvertisingOptOutDate" in the factless table as I have above? Or would those date values be better off stored in the base customer dimension?
Thanks.
My question relates to design tip #136 http://www.kimballgroup.com/2011/06/design-tip-136-adding-a-mini-dimension-to-a-bridge-table/
I have a similar situation to the example in the link, I have a Customer Dimension (100+ attributes), Account Dimension(60+ attributes) and a CustomerAccountBridge table.
To reduce the width of my customer dimension, I have several mini dimensions that are created out of the base customer dimension:
Customer Demographics
Customer Segmentation
Customer Preferences
Kimball recommends to put the mini/junk dimension surrogate key directly into the CustomerAccountBridge table, however I was wondering if my alternative would be ok:
Instead of putting the surrogate key into the CustomerAccountBridge, I create a Factless Fact table to relate all the mini/junk dimensions and base customer dimension together to track changes ie:
Factless Fact Table
CustDemographicID (SK of customer demographics) CustSegmentID (SK of customer segmentation) CustPrefID (SK of customer preference) CustID (SK of customer base) EmailOptOutDate (Date) AdvertisingOptOutDate (Date) EffectiveFrom (Date) EffectiveTo (Date) |
Similarly, every time a base customer record needs to point to a different junk dimension row, I also insert a row into the Factless Fact table and update the EffectiveTo date of the old record.
The reason I wanted to do it this way is because the VIEW I create for the business on the Customer would then not need to navigate through the CustomerAccountBridge table which would require a DISTINCT in the query as 1 customer can have many accounts potentially.
Is this a valid reason?
What are the pros and cons of doing it my way vs the one provided in the link?
Also, is it valid to put attributes like "EmailOptOutDate" and "AdvertisingOptOutDate" in the factless table as I have above? Or would those date values be better off stored in the base customer dimension?
Thanks.
memphis- Posts : 19
Join date : 2010-10-21
Re: Adding mini dimension to bridge table
It depends what your reporting requirements are.
The example Kimball gives allows you to report on the Account fact by the customer demographics.
If you just want to report on the structure of your customers then a factless fact table will probably do it
If you want to report on other fact tables by Demographics/Segmentation/Preferences then you will need to add these Dims' keys to those fact tables and/or the relevant bridge tables
The example Kimball gives allows you to report on the Account fact by the customer demographics.
If you just want to report on the structure of your customers then a factless fact table will probably do it
If you want to report on other fact tables by Demographics/Segmentation/Preferences then you will need to add these Dims' keys to those fact tables and/or the relevant bridge tables
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Similar topics
» Tip #136 Adding a Mini-Dimension to a Bridge Table: What if you can't split the dimension?
» bridge table and junk dimension on customer dimension (bank/credit union)
» Multvalued dimension bridge table and SCD 2 dimension
» Degenerate Dimension - Bridge Table
» Multivalued Dimension & Bridge Table
» bridge table and junk dimension on customer dimension (bank/credit union)
» Multvalued dimension bridge table and SCD 2 dimension
» Degenerate Dimension - Bridge Table
» Multivalued Dimension & Bridge Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum