December 28, 2007 at 7:52 am
We are at the early stages of designing a DW and are considering the options for storing history within our dimensions.
We have identified the attributes that will trigger our SCD but are concerned that future requirements may warrant expanding this list.
Our dilemma is whether to track just the fields we now know or build the dimension population process to track changes on all the attributes.
Apart from possible needless bloating of the DW, can anyone point out the pros/cons of tracking all the attributes within a dimension?
Many thanks,
January 4, 2008 at 4:21 am
Hi,
First question is, why would you want to track all dimension attributes from a business\reporting perspective? Second question is, have you decided on which SCD strategy you will use, if you need to track all attributes (e.g. type1 - overwrite, type 2 add new row or type 3 capture both the current and previous changes). Depending on your business requirements, type 2 and 3 will involve adding new dimension rows along with a new surrogate key, thus more space. In addition, its advisiable to add a change reason attribute, which will requirement updating upon a change.
Cheers,
Phillip Cox
January 4, 2008 at 11:05 am
Thanks for the response Phillip.
We will be implementing a hybrid of Type 1 and 2.
For each of our dimensions we have identified attributes that will trigger a Type 1 update as well as detailing attributes that will trigger a Type 2 change. Each dimension will carry Start and End date fields as well as a Current flag.
This solution will suit our current requirements but I have a concern that future needs may identify Type 1 attributes to be Type 2 attributes.
My question is, why not make all attributes trigger a type 2 change?
Ta,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply