Dimension update action in the DataStage Slowly Changing Dimension stage

Dimension update action in the DataStage® Slowly Changing Dimension stage

The Slowly Changing Dimension (SCD) stage updates the dimension table based on the results of the dimension lookup, plus the detection of a change in value for at least one SCD column.

Changes to Type 2 columns take precedence over changes to Type 1 columns. The following table describes how the SCD stage determines the update action for the dimension table:

Table 1. Determining update action
Lookup result	Type of column change	Update action
Dimension row not found	Not applicable	The stage creates a new dimension row by using all of the column derivations that are defined for the dimension update link.
Dimension row found	Type 2 column value changed, regardless of whether any Type 1 column value changed	The stage expires the current dimension row and creates a new row,maintaining the history that is required for Type 2 processing. To expire a row, the stage updates only the columns with a purpose code of Current Indicator and Expiration Date by using their Expire expressions on the Dim Update tab. To create a new row, the stage derives all of the columns from the Derivation expressions.
Dimension row found	Type 1 column value changed, but no Type 2 column value changed	The stage updates only the columns with a purpose code of Type 1 by using their Derivation expressions.
Dimension row found	No column values changed	No updates are needed.