SCD 2 and transaction facts

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

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?

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.