Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Type 2 SCD and Fact table

2 posters

Go down

Type 2 SCD and Fact table Empty Type 2 SCD and Fact table

Post  hmukadam 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

Back to top Go down

Type 2 SCD and Fact table Empty Re: Type 2 SCD and Fact table

Post  hang 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

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum