Type 2 SCD and Fact table

[hmukadam]

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?

Thanks,

[hang]

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.