Slowly Changing Dimensions (SCDs) in SSIS

Slowly Changing Dimensions (SCDs) is a term coined by Ralph Kimball refer to dimension tables that have data that slowly changes with time. These changes in data need to be recorded in order to make dimension table data refreshed and also to enable end user to analyse historical changes in data. For example, it could be necessary to let the Data Warehouse "know" when a customer's address changes (to make it possible to analyze sales by customer locations and have more focused marketing activities), or it could be necessary to update a product's name.

You could use several techniques to deal with these types of dimensions. One technique is explained in this recipe through an SSIS component created exclusively for that purpose.

The SCD has four methods to manage changes in a dimension:

• SCD Type 0

• SCD Type 1

• SCD Type 2

• SCD Type 3

SCD Type 0 is a passive approach to managing changes in dimension values; no action is taken because the dimension record remains...