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
|
Williams
|
New
York
|
To accommodate Type 3 Slowly Changing
Dimension, we will now have the following columns:
- Customer Key
- Name
- Original State
- Current State
- Effective Date
After Williams moved from New York to
Los Angeles, the original information gets updated, and we have the following
table (assuming the effective date of change is February 20, 2010):
Customer Key
|
Name
|
Original State
|
Current State
|
Effective Date
|
1001
|
Williams
|
New
York
|
Los
Angeles
|
20-FEB-2010
|
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 Williams later moves to Texas on December 15, 2003, the Los Angeles information will be lost.
Usage
Type 3 is rarely used in actual
practice.
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.
give an ssis packege example
ReplyDeletenice explaination
ReplyDeleteNice explaination..
ReplyDeleteNice Explaination of SCD Type 1,Type 2, Type3 .
ReplyDeletenice and clear explanation, thanks
ReplyDeletesuperb
ReplyDelete