Type 2 SCD and Fact table

View previous topic View next topic Go down

Type 2 SCD and Fact table

Post  hmukadam on Wed Feb 02, 2011 6:41 pm

How to handle grouing in fact table when data gets changed in Dim table. Fore example, I have dim table A with data like:
surogate key = 1001, acc# = 100 and name = ABC, after couple of months, name gets changed to XYZ, by using SCD2, new records get created as
surodate key = 2001 acc# - 100 and name = xyz. In fact table, I have data like:
qty = 1, amt = 50 and surogate key = 1001
qty = 4 amt = 100 and surogate key = 2001.
Customer want to see data using group by on account# : for acc# 100, name = xyz and amount = 150 and qty = 5, how can we handle this situation?



Posts : 1
Join date : 2009-12-14

View user profile

Back to top Go down

Re: Type 2 SCD and Fact table

Post  hang on Wed Feb 02, 2011 8:18 pm

You need to join fact table with dimension on surrogate key (SK) first, and then have a another self join to the same dimension on acc# (NK) with current status ='Y', so that you can always associate current dimension key (CSK) in your fact to any SK that shares the same NK.

Now when you group by the CSK, you will get the proper aggregate. The CSK will also provide the current profile of the dimension.


Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

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