In a datamart where 2 or 3 or more dimensions merge to form a new dimension. How can the scd be managed to keep track of all mergers historically and present trending facts as related to those dimensions?
A specific example would be three stores (Business ID 8897, 8965, 9135) merge to create a new store Business ID 9700. How can historical sales data be fetched from the fact table to show that up until a given date 8897, 8965 and 9135 were separate stores which are all now a new store 9700.
Additionally what if the business number of the new store is not 9700 but the new store takes on one of the previous stores business ID. So instead of 9700 the new merged store business ID is 8897.
SurrogateKey -------- StoreBusinessID---------- StoreName
=============== =================== ==================
1 8897 Alpha Electronic
2 8965 Beta Electronics
3 9135 Gamma Electronic
4 9700 Mega Electornics
=============== =================== ==================
You need to use "Type 6" slowly changing dimension. Essentially, it's a combination of Type 2 and Type 1 change.
The way it works: To capture "Type 2" change, you will need to have "Start Date" and "End Date" for each record in your "Store" table. For current records, end date is usually some distant future date like 2999-12-31. When you connect such table to fact tables, you will need to join dimension and fact on natural key (StoreBusinessID) and on fact date between start date and end date.
To capture "Type 1" change, you will have to add a field to your "Store" table to track "Latest Business ID". This field will contain the latest version of the business key. If there is no change, Store Business ID and Latest Business ID will contain the same key. If the stores merge, records 8897, 8965, 9135 and 9700 will all contain "Latest Business ID" 9700.
As a result, you can both "travel in time" (reproduce exact history in any period), and group by the latest version of the store.