Reporting on SCD

View previous topic View next topic Go down

Reporting on SCD

Post  Idea on Thu Mar 14, 2013 11:40 pm

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

Idea

Posts : 2
Join date : 2013-03-14

View user profile

Back to top Go down

Re: Reporting on SCD

Post  ngalemmo on Fri Mar 15, 2013 1:59 am

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

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Reporting on SCD

Post  Idea on Fri Mar 15, 2013 10:19 am

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?

Idea

Posts : 2
Join date : 2013-03-14

View user profile

Back to top Go down

Re: Reporting on SCD

Post  ngalemmo on Fri Mar 15, 2013 4:43 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Reporting on SCD

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