Sunday January 21st 2018

Categories

Insider

Archives

Slowly Changing Dimension

slowly changin dimensionThe “Slowly Changing Dimension” a common problem in data warehousing.
Basically, this applies to cases where the attribute for a record varies over time. For Example;
George is an employee with XYZ Inc. He first worked at production plant. So, the
original entry in the employee lookup table has the following record:
Employee ID or Employee Key = 12345, Employee name = George , Department = Production
Later on he was transfered to Purchasing Department on August 2009. How should XYZ Inc. now modify its Employee table to reflect this change? This is the “Slowly Changing Dimension” problem.

There are in general three ways to solve this type of problem, and are categorized accordingly;
Type 1: The new record replaces the original record. No trace of the old record exists.
Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people.
Type 3: The original record is modified to reflect the change.
————————————————————
In Type1 Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept. In our example, recall we originally have the following table:
Employee ID or Employee Key = 12345, Employee name = George, Department = Production
After George moved from Production to Purchasing the new information replaces the new record, and we have the following table:
Employee ID or Employee Key = 12345, Employee name = George , Department = Purchasing
————————————

Type 2: A new record is added into the Employee dimension table. Therefore, the employee is treated essentially as two people. In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The new record gets its own primary key.

In our example, recall we originally have the following table:

Employee ID or Employee Key = 12345, Employee name = George, Department = Production

After George moved from Production to Purchasing we add the new information as a new row into the table:
Employee ID or Employee Key = 67891, Employee name = George , Department = Purchasing

Advantages:

– This allows us to accurately keep all historical information.

Disadvantages:

– This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.

When to use Type 2:
Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes

—————————————-

Type 3- The original record is modified to reflect the change.

In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. There will also be a column that indicates when the current value becomes active.
In our example, recall we originally have the following table:
Employee ID or Employee Key = 12345, Employee name = George , Department = Production
To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns:
Employee ID or Key , Employee Name , Department worked, Current Department, Date
After George moved from Production to Purchasing, the original information gets updated,
and we have the following table (assuming the date of change was August 15, 2009):
Employee ID or Employee Key = 12345, Employee name = George , Department Worked = Production, Current Department = Purchasing, Date = August 15, 2009.

Advantages:
– This does not increase the size of the table, since new information is updated.
– This allows us to keep some part of history.
Disadvantages:
– 3 will not be able to keep all history where an attribute is changed more than once.
For example, if George later moves to Quality department on Feb 15, 2010, the purchasing information will be lost. It shou when such changes will only occur for a finite number of time.

Please also visit an article “Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3 by Margy Ross and Ralph Kimball”