SCD types

Slowly Changing Dimension (SCD) refers to the concept where some or most of the data changes at irregular intervals. There are multiple SCD types available, and each has different implementations.

SCD Type 1

Use SCD Type 1 to update data without tracking historical data. For example, you can correct a typographic mistake by marking a dimension as SCD type 1. These changes in semantic model design require a full build of the semantic model. After that, future incremental builds will update the data. The last level of an SCD must be uniquely identifiable otherwise, data integrity will be compromised.

SCD Type 2

SCD Type 2 includes historical data along with current data.  It includes another dimension record to track the full history of values and uses keys to identify each one. Each change includes an effective and expiration time. 

To specify that a dimension is an SCD Type 2, create an associated dimension with fields named startdate and enddate and create a relationship between that and the field used in your primary dimension.

For example, in a semantic model that contains data about employees and the hours they work, there is a field named date. However, employees may change departments or even leave a company and later return to it. To track the duration over time, create an associated dimension record that includes fields named startdate and enddate. Then, create an equi join (=) between both fields named EmployeeID and create a non-equi join (<>) between the startdate, enddate, and date fields and set the relationship to Between.