SCD's and Fact Tables
Page 1 of 1 • Share •
SCD's and Fact Tables
How do you handle the situation when a conformed dimension is linked to more than one fact table and one fact cares when a particular dimension attribute is changes, but the other does not? Or when one fact table cares when attribute A changes, but the other fact table cares when attribute B changes? Does your design or the way you link from the facts to the dimension change at all?
Thanks
Thanks
jimbo1580- Posts: 11
Join date: 2009-04-30
Re: SCD's and Fact Tables
Generally, if I need to implement a type 2 dimension, I also implement a type 1 version of the same and carry both FKs in fact tables. When doing so, if there are fewer fields that need type 2 treatment, the type 2 dimension can be stripped down to those data elements that matter. You can even go as far as reducing the type 2 dimension to one or two junk dimensions of those attributes that require point-in-time treatment.
But, when you model this, you need to take individual fact tables out of the picture and look at the dimension on its own. And, while a business may say you need to keep dimensional history on a set of attributes, there is also an implied requirement that they also need the current state as well. Which is why I do both types 1 and 2. It doesn't matter what the facts are... treat all fact tables the same and provide both. Ultimately, it is the particular query they decide to do at that moment, which may lie outside the scope of the original requirement. That is what discovery is all about.
But, when you model this, you need to take individual fact tables out of the picture and look at the dimension on its own. And, while a business may say you need to keep dimensional history on a set of attributes, there is also an implied requirement that they also need the current state as well. Which is why I do both types 1 and 2. It doesn't matter what the facts are... treat all fact tables the same and provide both. Ultimately, it is the particular query they decide to do at that moment, which may lie outside the scope of the original requirement. That is what discovery is all about.

ngalemmo- Posts: 215
Join date: 2009-05-16
Location: Los Angeles

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





