MS BI Workshop #11 – 3 types of SCD dimension
Here is my notes #11. About dimension again.
It’s easier to demonstrate the Type 0, Type 1 and Type 2 and Type 3 SCD (Slowly Changing Dimension) using the SCD Wizard in SSIS.
- Type 0: any change in the dimension column is treated as errors.
- Type 1: Update changing data only, no historical tracking.
- Type 2: Tracks historical records, expires the row, and puts a new one into the table. StartDate and EndDate are commonly used to expire the records.
- Type 3: Same as Type 2, but only tracks certain number of revisions.
- It’s common to have columns from each type in the same table.
- The SCD wizard in SSIS can handle about 80% of situations.