Thursday, September 28, 2017

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. 
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.

No comments:

Post a Comment

Popular Posts