SCD's and Fact Tables

View previous topic View next topic Go down

SCD's and Fact Tables

Post  jimbo1580 on Wed Nov 04, 2009 11:07 am

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

jimbo1580

Posts : 23
Join date : 2009-04-30

View user profile

Back to top Go down

Re: SCD's and Fact Tables

Post  ngalemmo on Wed Nov 04, 2009 1:19 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD's and Fact Tables

Post  jimbo1580 on Tue Dec 01, 2009 5:37 pm

Can you please explain why do you implement a type 1 version of the dimension as well? Wouldn't the most recent record in a type 2 version be the same thing? What benefits does your solution provide? It seems that it adds some complexity because the business analyst now needs to know what dimension to use to get the info they are looking for.

I understand you to be saying to disreagrd the fact tables when determining how to handle changes. My understading has been that the fact table requirements are what should determine the change type because, although you are tracking the actual changes in the dimension, your fact table requirements tell you whether to change your foreign key to the changed dimension. I am running into this particular issues in my design now. I have an certain attribute in a dimension and it makes logical sense to track changes to it, but the fact table linked to it doesn't care about the change and the most recent value is fine. So I am not sure what to do?

It seems that implementing type 2 changes really complicate the dimensional model and add layers of complexity that the business anlaysts need to be aware of when performing various analysis, such as having duplicate rows for a single product.

Do you have any good internet resources on SCD's?

jimbo1580

Posts : 23
Join date : 2009-04-30

View user profile

Back to top Go down

Re: SCD's and Fact Tables

Post  BoxesAndLines on Tue Dec 01, 2009 8:50 pm

Type 2's are not that more difficult. From a business perspective, they are no different than a type 1. Just join on the FK and you get your dimension row. Instead of implementing 2 dimensions, I implement just 1. I carry a column in my type 2 dimension that never changes across all history for the given natural key. I then store that column as a FK on the fact table. This gives you the same functionality as 2 dimensions but with just one table. To get the current row you join on the Fact FK that never changes and Current Row Indicator = 'Y'.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: SCD's and Fact Tables

Post  ngalemmo on Wed Dec 02, 2009 3:19 pm

When you implement a type 2 dimension, the fact table foreign key references the point in time row for that dimension. To get the most current row in a type 2, you need to query the dimension table twice. First from the fact foreign key to get the point in time row, then a second time to locate the most current row using the natural key (from the first query) and the current flag. While it is easy enough to do this with a view, the query complexity can cause performance problems, particularly if the query contains predicates on current attribute values.

Creating a type 1 version of the same dimension and carrying an additional foreign key on the fact tables makes current value queries much simpler and more efficient. And my experience has been, more often than not, that users are more interested in current rather than historical attribute values in reports.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD's and Fact Tables

Post  jimbo1580 on Thu Dec 03, 2009 5:28 pm

I understand now. Thanks!

I am thinking about this problem in the context of an accumulating snapshot fact table. In this case, the fact is representing something that is in progress and it should always be pointing to the most recent rows in the dimensions until the process is complete and I think that is why I got confused. How do SCD's change when considered in the context of an accumulating snapshot fact table vs a transaction fact table?

Our business requirements tell us to link the facts to the most recent values of the dimensions until the process is complete for the particular fact row. At the time of completion, they want to make sure a few of the dimension attributes are frozen and do not change. I was either going to use slowly changing dimensions for these few attributes or store them in a junk dimension and build the logic into my ETL to not change them after the case is complete. Any experience with this?

Thanks!

jimbo1580

Posts : 23
Join date : 2009-04-30

View user profile

Back to top Go down

Re: SCD's and Fact Tables

Post  BoxesAndLines on Fri Dec 04, 2009 9:12 am

I would treat an update on an accumulating snapshot the same way I would treat an insert on a transaction fact.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: SCD's and Fact Tables

Post  ngalemmo on Fri Dec 04, 2009 4:52 pm

I have often used a junk dimension as a substitute for a type 2 dimension particularly when there are only a handful of attributes that require point in time treatment. This works well, and, the junk dimension is often very much smaller than the type 2 equivalent, which improves performance of queries that filter on those attributes.

If you need to keep track of change history on the attributes you junked, a simple factless fact table carrying the junk and main dimension keys and date would do the trick.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD's and Fact Tables

Post  kapoor_dh on Tue Dec 08, 2009 1:44 am

Why can't we just do this...

Two fact tables F1,F2
Dimension D1 with attributes A,B

1.While handling the SCD2 in D1,end the record and add a new record with indicator as "Y",when any of the attributes A,B changes and generate a new key k1
2.Use the key k1 in the Fact table as the Foreign key

As we never filter based on the surrogate key,and whenever we query we filter on attributes in dimension table like A='ABC' or B='XYZ' our results will be perfect.

kapoor_dh

Posts : 24
Join date : 2009-12-08

View user profile

Back to top Go down

Re: SCD's and Fact Tables

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