Adding mini dimension to bridge table

View previous topic View next topic Go down

Adding mini dimension to bridge table

Post  memphis on Mon Feb 23, 2015 10:54 pm

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
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)
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.

memphis

Posts : 19
Join date : 2010-10-21

View user profile

Back to top Go down

Re: Adding mini dimension to bridge table

Post  memphis on Thu Feb 26, 2015 6:11 am

Anyone??

memphis

Posts : 19
Join date : 2010-10-21

View user profile

Back to top Go down

Re: Adding mini dimension to bridge table

Post  nick_white on Thu Feb 26, 2015 8:28 am

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

nick_white

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

View user profile

Back to top Go down

Re: Adding mini dimension to bridge table

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