Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Tracking Type2 SCD when using a dimension outrigger

3 posters

Go down

Tracking Type2 SCD when using a dimension outrigger  Empty Tracking Type2 SCD when using a dimension outrigger

Post  parbie 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
parbie
parbie

Posts : 11
Join date : 2010-04-06

Back to top Go down

Tracking Type2 SCD when using a dimension outrigger  Empty Re: Tracking Type2 SCD when using a dimension outrigger

Post  hang 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

Back to top Go down

Tracking Type2 SCD when using a dimension outrigger  Empty Re: Tracking Type2 SCD when using a dimension outrigger

Post  warrent 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
warrent
warrent

Posts : 41
Join date : 2008-08-18

Back to top Go down

Tracking Type2 SCD when using a dimension outrigger  Empty Re: Tracking Type2 SCD when using a dimension outrigger

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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