What is slowly changing Dimension (SCD)?
When creating a Data Warehouse, we associate Facts with some Dimensions.
But what if when a Dimension record changes? Do we update the record and lose the older one? Or keep history as well?This is the concept of Slowly Changing Dimensions (SCD) and its types, through which we decide how do we treat a change in dimension.
Type 0 : No change in Dimension allowed. Once the data is written, it remains the same.
Type 1 : Overwrite the old value. No history of the Dimension is saved. We directly update the old record.
Type 2 : We keep the whole history of the dimension. We do this by inserting a row for the new record, the column 'current indicator' will have value Y for current record and N for older one. We also include a start date and end date column that conveys the time period when the record was active.
Type 3 : We just retain the current value and last value, but not the whole history. So we don't need to add another row, in the same row, we have columns current and previous to record the values.
Type 4 : In the main dimension table, we would keep only the current record, but the history is maintained in a separate history table. So we don't lose the old data.
Type 6 : Its a combination of Types 1,2,3, so we mark it as Type 6. Here, we keep the current record, as well as whole history in the Dimension Table. The current indicator column captures whether the record is current or old, start date and end date record the time period associated with the record, and a Historical Type column that captures the previous record
0 Comments