Purpose code definitions in the DataStage® Slowly Changing Dimension stage
The Slowly Changing Dimension stage (SCD) provides nine purpose codes to support dimension processing.
- (blank)
- The column has no SCD purpose. This purpose code is the default.
- Business Key
- The column is a business key that is typically used in the lookup condition.
- Current® Indicator (Type 2)
- The column is the current record indicator for SCD Type 2 processing. Only one Current Indicator column is allowed.
- Effective Date (Type 2)
- The column is the effective date for SCD Type 2 processing. Only one Effective Date column is allowed.
- Expiration Date (Type 2)
- The column is the expiration date for SCD Type 2 processing. An Expiration Date column is required if there is no Current Indicator column, otherwise it is optional.
- SK Chain
- The column is used to link a record to the previous record or the next record by using the value of the Surrogate Key column. Only one Surrogate Key column can exist if you have an SK Chain column.
- Surrogate Key
- The column is a surrogate key that is used to identify dimension records.
- Type 1
- The column is an SCD Type 1 field. SCD Type 1 column values are always current. When changes occur, the SCD stage overwrites existing values in the dimension table.
- Type 2
- The column is an SCD Type 2 field. SCD Type 2 column values represent a point in time. When changes occur, the SCD stage creates a new dimension row.