Tracking Type2 SCD when using a dimension outrigger

View previous topic View next topic Go down

Tracking Type2 SCD when using a dimension outrigger

Post  parbie on Wed Sep 08, 2010 4:10 pm

With an outrigger table, a narrow band of attributes, usually with significantly lower cardinality than the main dimension, is in essence snow flaked off of the main dimension. Great technique for data management and maintenance. To hide complexity from the user, Kimball recommends combining the two tables into one view. Nice solution, but how do you present SCD tracking columns such as Effective From & Through Date, Current Flag, and Changed Reason? My instinct is to give both sets of columns a specific alias in the view. Problem solved but does this not introduce complexity to the user with 8 SCD tracking columns?
Thanks
avatar
parbie

Posts : 11
Join date : 2010-04-06

View user profile

Back to top Go down

Re: Tracking Type2 SCD when using a dimension outrigger

Post  hang on Fri Sep 10, 2010 1:07 am

What Kimball suggested is this. To make the main dimension slimmer, you could have an outrigger with a group of low cardinality attributes using junk dimension idea, instead of SCD2, to include all the practically possible combinations of the attribute values.

The outrigger could be called profile (eg. demographic) dimension. The FK in main dimension should point to the current profile of the outrigger. You then have two FKs in the fact to point to the main dimension and outrigger respectively to reflect the historical relationship between them. In this case, the profile dimension (outrigger) is also called mini dimension.

The primary purpose for this arrangement is to limit the fast growing size of big main dimension by moving more dynamic change tracking to the fact table. Therefore having both main and outrigger dimensions as SCD would defeat the purpose, as the main dimension would be just as big as combined dimension, I mean vertically.

Having corelated SCD dimensions is challenging to both ETL process and dimension users. You would be better off having a single SCD dimension with all the relevant attributes or demoting the correlation into a fact table.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Tracking Type2 SCD when using a dimension outrigger

Post  warrent on Tue Sep 14, 2010 12:33 am

You shouldn't need change tracking in the mini-dimension (the outrigger) because it is simply a set of unique combinations of the individual attributes. As the previous poster said, the purpose of the mini-dimension is to move the type 2 change tracking out of the dimension and into the fact table. Design tip #127 gives more details.

If you are simply trying to move some of the attributes out to an outrigger table because those attributes don't get used very often and are making the main table too big, you still don't need to worry about type 2 change tracking in the outrigger. Do the key assignment from the main dimension to the outrigger first for all new and changed rows, then do type 2 change comparison to see if the key changed. If the key changed, you add a new row to the main dimension.

Hope this helps,
--Warren
avatar
warrent

Posts : 41
Join date : 2008-08-18

View user profile

Back to top Go down

Re: Tracking Type2 SCD when using a dimension outrigger

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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