What is a Slowly Changing Dimension in SQL Server Analysis Services? What are the different types of Slowly Changing Dimensions?

The Slowly Changing Dimension (SCD) concept is basically about how the data modifications are absorbed and maintained in a Dimension Table. In an SCD the contents/members change over a period of time. Apart from the existing members which are modified, new records are added to the dimension similar to any other dimension.

Here are the major 3 types of Slowly Changing Dimensions:

Type 1:- 

In this type of SCD, modifications to the dimension members are absorbed by overwriting the existing member data present in the Dimension Table. Example: Say you have an Address Dimension which contains addresses of all the employees in an organization. When the address of an employee changes, then the old data (address) in the Dimension is overwritten with the new data (address) and there is no way to get the old address from the table.

Type 2:- 

In this type of SCD, modifications to the dimension members are absorbed by adding it as a new record into the Dimension Table. Meaning, the history of all the changes happening to the dimension members is maintained by creating a new record each time a dimension member is modified. The new (modified) record and the old record(s) are identified using some kind of a flag like say IsActive, IsDeleted etc. or using Start and End Date fields to indicate the validity of the record. New dimension members are inserted into the Dimension Table with appropriate flag value which indicates that this is the active/current record. Unlimited amount of history can be maintained in this type of SCD. Example: Considering the above example of an Address Dimension. When the address of an employee changes, then the old data (address) in the Dimension Table is retained as it is and the flag is updated to indicate that this is a historical record. The modified data (record) is inserted as a new record into the table and the flag is updated to indicate that this is the current record.

Type 3:- 

In this type of SCD, modifications to the dimension members are absorbed with the help of additional columns in the Dimension Table. In this SCD type, the amount of history data (number of modifications) that can be maintained is limited by the number of columns (fixed) in the Dimension Table, which are used for tracking the history details. This is decided at the time of designing the Data Mart. Example: Considering the above example of an Address Dimension. When the address of an employee changes, then the old data (address) in the Dimension is retained as it is and the new address is updated in a separate column to indicate that this is the latest address.

Apart from the above listed 3 types of SCDs, there are other types like Type 0, Type 4, Type 5, & Type 6 etc. which are used rarely.