SSIS - Slowly Changing Dimension Wizard
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:
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.