Questions tagged [scd]

SCD may refer to Slowly Change Dimension transformation for SQL Server or Schema Component Designators which is a declarative query language for XML Schemas.

Slowly Changing Dimensions in data management and data warehousing are relatively static dimensions containing entities. Data captured by SCD changes slowly rather on unpredictable time interval. In the data warehouse they are used for reporting historical data.

Slowly Changing Dimension Techniques :

1. Type 0 - retain original. Dimensions attributes never change, so facts are always grouped by original value.
2. Type 1 - overwrite. The old attribute value in the dimension is overwritten with the new value.
3. Type 2 - add new row. It adds a new row in the dimension with an updated attribute values.
4. Type 3 - add new attribute. It adds a new attribute in the dimension to preserve the old attribute value.
5. Type 4 - add mini-dimension. When a group of attributes changes rapidly and it split off to a mini-dimension.
6. Type 5 - add mini-dimension and Type 1 outtriger. Used to accurately preserve historical attributes values, plus report historical fact according to current attribute value.
7. Type 6 - add Type 1 Attributes to Type 2 Dimension. Like Type 5, Type 6 delivers both historical and current dimension attribute values. Type 6 builds on the Type 2 technique by also embedding current type 1 versions.
8. Type 7 - dual Type 1 and Type 2 dimensions. Final hybrid technique used to support as-was and as-is reporting.

More information on Slowly Changing Dimensions:

1. Kimball Dimensional Modeling Techniques
2. Wikipedia
3. Slowly Changing Dimensions by Ralph Kimball
4. Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3