Slowly changing dimension is a common data warehousing scenario. SCD utilizes three different method types for handling changes to columns in a data warehouse dimension table.
In Type 1, new data overwrites existing data. The existing data is lost and there is no tracking of historical changes. Type 1, is the easiest method to support, but is useful only if you need not track historical changes.
Consider a table that keeps product information:
Key |
Name |
Price |
|---|---|---|
1 |
Notebook |
1200 |
The price of the notebook increases to 1500. The updated table simply overwrites the current record:
Key |
Name |
Price |
|---|---|---|
1 |
Notebook |
1500 |
Type 2 retains the full history of values. If new data differs from the old data, an additional dimension record is created with new data values and becomes the current record. Each record contains the effective date and an expiration date to identify the time period for which the record was active. Use type 2 to keep a full history of dimension data in the table.
Type 3 tracks changes using separate columns. There is one version of the dimension record that stores the previous value and current value of selected attributes. Use type 3 when you need to track historical changes that occur only for a finite amount of time.
Consider a table that keeps product information:
Key |
Name |
Price |
|---|---|---|
1 |
Notebook |
1200 |
The price of the notebook increases to 1500 on 15th July 2008. To accommodate type 3, new columns are added, Current Price and Effective Date:
Key |
Name |
Original price |
Current price |
Effective date |
|---|---|---|---|---|
1 |
Notebook |
1200 |
1500 |
2008-07-15 |
Type 3 is rarely used because altering the structure
of the dimension table should be undertaken for only a very important
change.
