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.

Part 1 - Slowly Changing Dimensions in Data Warehouse

Dimensional modeling is different from data modeling because it is fundamentally a logical modeling of business requirements. In dimensions, all the entities are often physical in nature such as Customers, Patients, Products, Stores, and Salespersons etc. Identifying Dimensions and their attributes is easy when you know the trick.

SlowlyChanging Dimensions

Dimensions that change over the time are known as Slowly Changing Dimensions. For example, Customer demographics, product characteristics, classification rules, status of customers and Salespersons regional offices etc. lead to changes in the attributes of dimensions.

If we try to see into a transaction system where many a times the changes are overwritten and track of changes are lost easily.

Salesperson example - a source system may have only the latest Salesperson regional office, in which they work. However, the salespeople are sometimes transferred from one regional office to another.

For historical sales reporting purposes it may be necessary to keep a record of the fact that a particular sales person had been assigned to a particular regional office at an earlier date, whereas that sales person is now assigned to a different regional office.

Customer example - a source system may have only the latest customer PIN Code which is needed to send the marketing and billing statements. However, the customers are sometimes transferred from one location to another.

For historical sales reporting purposes, a data warehouse needs to maintain all the previous PIN Codes as well, because we need to track on how many customers move to new locations over what frequency.

To handle these kinds of issues, a Slowly Changing Dimensions methodology comes into the picture to provide the accurate reporting data to the management. There are various techniques to maintain the historical data information. To understand this, we are taking the example of the Salesperson with the following columns-

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

You can see that SalesPersoncode and RegionalCode are the natural key and SalesPersonKey is using as the surrogate key. With the help of the above Salesperson dimensional data table, we are going to demonstrate the various Slowly Changing Dimensions (SCD) methodologies as given below-

Type 1:Overwrite technique - This is the technique, simply overwrite the existing salesperson records. For example, Ryan Arjun is aligned with CA regional code and after sometime he is transferred to another OH regional code. So, we can overwrite this record in Salesperson dimensional data table as 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

The biggest disadvantage of the Type 1 method is that there is no history in the data warehouse and but it has the advantage however that it's easy to maintain.

If one has calculated an aggregate table summarizing facts by RegionalCode, it will need to be recalculated when the RegionalCode is changed.

Type 2: Addnew row technique – In this case, we can easily maintain the movement history of the Salesperson by adding the new column called IsActive in Salesperson dimensional data table.

In case of the salesperson is transferred from one regional office to another then his IsActive status will be changed for the old record as 0 and for the latest record it would be 1.

Now, we are taking the same example where Ryan Arjun was aligned with CA regional code and after sometime he is transferred to another OH regional code. So, we are going to add new row into the data table as given below-

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

There is another method also where we can add start date and end date in the Salesperson dimensional data table and maintain the movement history of the Salesperson as given below-

SalesPersonKey	SalesPersonCode	SalesPersonName	RegionalCode	StartDate	EndDate
1	SP001	Ryan Arjun	CA	1/5/2013	14/10/2014
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
6	SP001	Ryan Arjun	OH	15/10/2014	NULL

The null value in the EndDate column indicates the latest or current status of the SalesPerson in the Salesperson dimensional data table. 
Please visit - Part 2 - Slowly Changing Dimensions in Data Warehouse