Part 2 - Slowly Changing Dimensions in Data Warehouse

Data Warehouse has a core need for maintaining historical information. So, it would become very necessary that how an entity has moved and changed the shape through the passage of time. There are many scenarios where the dimension data (Supplier, Salesperson, Customers, Product Price etc.) changes are expected to happen often. 

Please visit - Part 1 - Slowly Changing Dimensions in Data Warehouse

Type 3: Add new Attributes (columns) technique – In this case, we can preserves limited history by creating separate columns in Salesperson dimensional data table. This technique preserves limited history because it is limited to the number of columns designated for storing historical data. In this case, we have to add new columns to preserve limited data history as given below-

SalesPersonKey	SalesPersonCode	SalesPersonName	OldRegional	EffectiveDate	CurrentRegional
1	SP001	Ryan Arjun	CA	1/5/2013	OH
2	SP002	Kimmy Wang	IL	5/6/2013	NULL
3	SP003	Don Wilcox	NY	15/7/2013	NULL
4	SP004	Rosy Gray	TX	23/8/2013	NULL
5	SP005	John Smith	TN	23/10/2013	NULL

The main disadvantage of the technique is that this record contains a column for the old regional and current regional but we cannot track the changes if second time the salesperson relocates to another regional.

Type 4: Add history table technique – In this case, we can maintain the movement of the salesperson from one regional to another regional by creating another history table to store old movement history. Both the tables have the surrogate keys which are referenced in the Fact table to enhance query performance.

Now, we have two tables such as Salesperson Dim given below-

SalesPersonKey	SalesPersonCode	SalesPersonName	RegionalCode
1	SP001	Ryan Arjun	OH
2	SP002	Kimmy Wang	IL
3	SP003	Don Wilcox	NY
4	SP004	Rosy Gray	TX
5	SP005	John Smith	TN

and Salesperson History Dim as given below-

SalesPersonKey	SalesPersonCode	SalesPersonName	OldRegional	CreateDate
1	SP001	Ryan Arjun	CA	1/5/2013
2	SP002	Kimmy Wang	IL	5/6/2013
3	SP003	Don Wilcox	NY	15/7/2013
4	SP004	Rosy Gray	TX	23/8/2013
5	SP005	John Smith	TN	23/10/2013
6	SP001	Ryan Arjun	OH	15/10/2014

Conclusion

Dimension/Attribute that changes over the period of time are known as the slowly changing dimension. SCD like the ones in the case of salesperson regions example will perhaps need history with correctly tagged validity period.  It is very important to examine all the dimensions often with an independent perspective to be able to uncover and address the business risks and issues the changing dimensions bring.