See Also: Main_Page - Code Management - Loaders - Integration Services (SSIS)

SSIS introduces functionality that is extremely useful and common in data warehousing environments managing slowly changing dimensions. Slowly changing dimensions reflect the natural tendency to modify dimension member values over time. For example, in the account dimension it is quite possible to re-classify an account and give it a different type or provide a more appropriate description. In some environments such changes would be considered trivial and tracking the history of changes would not be necessary. However, some historical data can be interesting; for example, changes in consumer's last name would be of great interest to financial institutions that provide loans and need to assess individual's credit worthiness.

There are multiple methods for managing data value modifications in slowly changing dimensions. These methods are commonly classified as follows:

Type 1 Dimensions

Overwrite the existing record:

An obvious drawback of this approach is that there is no way to track lineage of changes.

Type 2 Dimensions

Create a new row when the values change. Add a current flag column to the dimension:

Note that instead of a single row we have two rows after the value change took place.

Type 3 Dimensions

Track the changing values in the same row but different columns. This type of tracking of changes is implemented rarely because it leads to complicated queries and maintenance headaches:

Prior to SSIS, you would have to create multiple tasks within DTS to compare each column within the dimension table with the corresponding columns in the data source. If any changes were detected then you'd have to use another task to add or update records as appropriate.

Fortunately with SSIS, implementing Type 2 slowly changing dimensions is a matter of running a wizard, as follows:

• Create an SSIS project and identify your data source within BIDS.
• Next, navigate to the Data Flow tab and drag Slowly Changing Dimension (SCD) transformation to the Data Flow designer.
• Drag the green arrow from your data source to the SCD transformation; then double click SCD transformation. This activates the SCD wizard.
• Skip the "welcome" screen and choose the correct data destination; then select the dimension table for which you wish to manage SCD. Notice that you must choose the business key - this is necessary for uniquely identifying the changing record.
• The next screen in the wizard explains different methods for tracking changes in the dimension member values.
• The next screen allows you to specify the column which will be used for tagging records as current or obsolete. You can use bit columns and specify true / false values. Alternatively, you could use a date and time column to specify when the record becomes expired (or obsolete).

The following screen allows you to specify the support for inferred members. Inferred members are created when you load the fact table and the corresponding dimension record does NOT exist. Each record in the fact table must be associated with a record in a dimension table. There are multiple ways of handing this in a data warehouse and Analysis Services. The wizard allows you to create an inferred member record with all attributes set to NULL. It is often better to have a special "unknown" member in your dimension (perhaps with the dimension key equal to -999 or another value that stands out) as opposed to creating inferred members. For now, uncheck the default selection and click next. That is all the information the wizard needs to come up with a fairly sophisticated data flow.

Now we can update a few members in the transactional database and then run the SSIS package to see if it updates the dimension table correctly.