SCD 2 and transaction facts

View previous topic View next topic Go down

SCD 2 and transaction facts

Post  tim_goodsell on Thu Apr 17, 2014 2:39 am

Hi

We have SCD 2 dimensions that will change rarely and a transaction fact that references dimensions via surrogate keys.

If we want to report on the fact transaction table (say from start date to end date) via do we have to join the fact table to the dimensions via the dimension natural key to cater for the situation if a new dimension dimension row has been added during the date range specified

if so this will mean storing both the dimenison natural key and the surrogate key in the fact table

Regards

Tim




tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: SCD 2 and transaction facts

Post  nick_white on Thu Apr 17, 2014 6:56 am

You join the facts and dimensions using the SKs and then filter using the dimension attributes - the fact that a Dimension might be SCD doesn't affect this. For example, suppose you want all facts related to a Customer and your Customer Dim is SCD. Assuming your identifier for a customer is cust_no in your Customer Dim then your query just has "WHERE cust_no = '12345' ". The fact that this might select multiple Customer Dim records is irrelevant to the resultset that the query returns.

Hope this helps? If not can you provide more details of the sort of query that you want to run that you think would require you to hold natural keys in a fact table?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: SCD 2 and transaction facts

Post  BoxesAndLines on Thu Apr 17, 2014 8:04 am

That is one way to do it. Other options are joining the dimension back to itself to get the latest row, creating a durable foreign key (useful when the natural key is a compound key) and storing that column on the fact table. You can also create another type 1 dimension which will always have the latest row.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: SCD 2 and transaction facts

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