Slowly Changing Dimensions

Slowly Changing Dimensions (SCDs) is a concept in data warehousing and business intelligence that deals with managing changes in dimension data over time.

Data Warehousing has

- Time Variant (with history data)
- Non Volatile (no changes)

But in reality

- The customer may change his address.
- Store move to a new location.
- An employee joins a new company.

Dimension data refers to the descriptive attributes of an entity, such as customer, product, or location. These attributes provide context for analysis and reporting in a data warehouse.

However, dimension data can change over time, and it's essential to maintain a historical record of these changes for accurate reporting and analysis.

There are several types of SCDs, each with different strategies for managing changes in dimension data:

Type 1 - Overwrite: In this approach, when changes occur, the existing values are overwritten with the new values. No historical data is preserved.

Type 2 - Add a New Row: This method maintains a complete history of changes by adding a new row with the updated attribute values in the dimension table. Each row has a start and end date (or a start date and a flag indicating the current record) to show the period during which the attribute values were valid.

Type 3 - Add a New Column: This approach maintains a limited history of changes by adding new columns to the dimension table to store the previous values of changed attributes. It is useful when tracking a small number of changes but can become unwieldy with many changes.

Type 4 - Add a History Table: In this method, a separate history table stores the changes in the dimension attributes. The primary dimension table contains the current attribute values, while the history table stores historical data.

Type 6 - Hybrid: This combines Type 1, Type 2, and Type 3 approaches. It allows for the selective preservation of history for specific attributes and can be used to overwrite certain attribute values.

Choosing the appropriate SCD type depends on the specific requirements of the data warehouse, the importance of historical data for analysis, and the performance implications of each approach.