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:
|
Customer Key
|
Name
|
State
|
|
1001
|
Guru
|
Jharkhand
|
To accommodate Type 3
Slowly Changing Dimension, we will now have the following columns:
·
Customer Key
·
Name
·
Original State
·
Current State
·
Effective Date
After Guru moved from Jharkhand
to Karnataka, the original information gets updated, and we have the following
table (assuming the effective date of change is January 15, 2003):
|
Customer Key
|
Name
|
Original State
|
Current State
|
Effective Date
|
|
1001
|
Guru
|
Jharkhand
|
Karnataka
|
15-JAN-2003
|
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:
- Type 3 will not be able
to keep all history where an attribute is changed more than once. For example,
if Guru later moves to Texas on December 15, 2003, the California information
will be lost.
Usage:
It’s rarely used
When to use Type 3:
Type III slowly changing
dimension should only be used when it is necessary for the data warehouse to
track historical changes, and when such changes will only occur for a finite
number of time.
