Purpose codes in a DataStage® Slowly Changing Dimension stage

Building the lookup table

The SCD stage uses purpose codes to determine how to build the lookup table for the dimension lookup. If a dimension has only Type 1 columns, the stage builds the lookup table by using all dimension rows. If any Type 2 columns exist, the stage builds the lookup table by using only the current rows. If a dimension has a Current Indicator column, the stage uses the derivation value of this column in the output columns table to identify the current rows of the dimension table. If a dimension does not have a Current Indicator column, then the stage uses the Expiration Date column and its derivation value to identify the current rows. Any dimension columns that are not needed are not used. This technique minimizes the amount of memory that is required by the lookup table.

Detecting dimension changes

Purpose codes are also used to detect dimension changes. The SCD stage compares Type 1 and Type 2 column values to source column values to determine whether to update an existing row, insert a new row, or expire a row in the dimension table.

Updating the dimension table

Purpose codes are part of the column metadata that the SCD stage propagates to the dimension update link.

For more information, see Selecting purpose codes in the DataStage Slowly Changing Dimension stage and Purpose code definitions in the DataStage Slowly Changing Dimension stage.