Slowly Changing Dimension (SCD)

There are different types of Slowly Changing Dimension:

• Type 1 (overwrites the original information) (http://www.1keydata.com/datawarehousing/scd-type-1.html)
• Type 2 (a new record is added to the table to represent the new information, keeps all historical information) (http://www.1keydata.com/datawarehousing/scd-type-2.html)
• Type 3 (keeps original value, and current value in different columns of the same record) (http://www.1keydata.com/datawarehousing/scd-type-3.html)
• Type 4: uses "history tables", where one table keeps the current data, and an additional table is used to keep a record of some or all changes
• Type 6 (2+3+1) (hybrid approach that combines the three fundamental SCD techniques)

Note: SSIS SCD control only supports Type1 and Type2. But by identifying columns with the fixed attribute update type, you can capture the data values that are candidates for Type 3 changes.

• Slowly Changing Dimension Transformation (http://msdn2.microsoft.com/en-us/library/ms141715.aspx)
• Configuring Outputs Using the Slowly Changing Dimension Wizard (http://technet.microsoft.com/en-us/library/ms141662.aspx)
• http://en.wikipedia.org/wiki/Slowly_changing_dimension

Slowly Changing Dimension Transformation Outputs
Output	Description	Data flow requirements
Changing Attributes Updates Output	The record in the lookup table is updated. This output is used for changing attribute rows.	An OLE DB Command transformation updates the record using an UPDATE statement.
Fixed Attribute Output	The values in rows that must not change do not match values in the lookup table. This output is used for fixed attribute rows.	No default data flow is created. If the transformation is configured to continue after it encounters changes to fixed attribute columns, you should create a data flow that captures these rows.
Historical Attributes Inserts Output	The lookup table contains at least one matching row. The row marked as “current” must now be marked as "expired". This output is used for historical attribute rows.	Derived Column transformations create columns for the expired row and the current row indicators. An OLE DB Command transformation updates the record that must now be marked as "expired". The row with the new column values is directed to the New Output, where the row is inserted and marked as "current".
Inferred Member Updates Output	Rows for inferred dimension members are inserted. This output is used for inferred member rows.	An OLE DB Command transformation updates the record using an SQL UPDATE statement.
New Output	The lookup table contains no matching rows. The row is added to the dimension table. This output is used for new rows and changes to historical attributes rows.	A Derived Column transformation sets the current row indicator, and an OLE DB destination inserts the row.
Unchanged Output	The values in the lookup table match the row values. This output is used for unchanged rows.	No default data flow is created because the Slowly Changing Dimension transformation performs no work. If you want to capture these rows, you should create a data flow for this output.

SQL Server 2005 Books Online (September 2007)