Type 2 SCD and Fact table
2 posters
Page 1 of 1
Type 2 SCD and Fact table
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,
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,
hmukadam- Posts : 1
Join date : 2009-12-14
Re: Type 2 SCD and Fact table
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.
Now when you group by the CSK, you will get the proper aggregate. The CSK will also provide the current profile of the dimension.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Similar topics
» Kimbal Fact Table Type - Transactional Fact Type Issue
» SCD Type 2 dimension and fact table being the same table
» Fact table type
» Type 2 Fact table - Durable surrogate key?
» Factless Fact table to model 1:M relationships between Type 2 SCD
» SCD Type 2 dimension and fact table being the same table
» Fact table type
» Type 2 Fact table - Durable surrogate key?
» Factless Fact table to model 1:M relationships between Type 2 SCD
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum