Reporting on SCD

[Idea]

we have a customer SCD type 2 DIM, with start date, end_date and current_value_yn. This SCD connects to 2 FACTs which populates in different interval. say FACT1 populates on 15th of each month, where as FACT2 populates 25th of each month.

We have a scenario where CUSTOMER SCD got updated on 15th and we've a new dim record shows as current_value_yn=Y and FACT1 populated with this latest customery key on 15th itseself. whereas FACT2 will still have an old record as current_value_yn=Y as we'll load FACT2 only on 25th.

if we need to report values from both FACTS/ latest CUSTOMER SCD, how can we join tables?

sample table structure given


customer scd

customer_key cust id start date end date current_yn
1000 100 1/1/2011 3/15/2013 n
1001 100 3/15/2013 y




FACT1
customer_key sale_amt earning_amt time_key
1001 250000 1000 3/15/2013


FACT2
customer_key sale_amt market_rate time_key
1000 1250 300 2/1/2013

Report out:
customer_key, FACT1.sale_amt, FACT2.market_rate on 03/15/2013

[ngalemmo]

You combine facts by aggregating each across common dimension attributes then joining (or union) the two sets.

[Idea]

Thanks for your repsonse, Here the challenge is, in FACT1 for March data customer 1001 is the current record, where as FACT2 still have customer 1000 is the current. so if we use the queries based on current_yn flag, it will point to different dim versions. can you suggest?

[ngalemmo]

If you need current, you do a self-join on the dimension table using the natural key to locate the current row.

There are other ways to do this, such as maintaing both type 1 and type 2 dimensions, or keeping a type 1 key in the type 2 dimension to eliminate the self-join. Both require storing both a type 1 and type 2 key in the fact table.

[Sponsored content]