Surrogate keys in a DataStage® Slowly Changing Dimension stage

Surrogate keys are used to join a dimension table to a fact table in a star schema database.

When the Slowly Changing Dimension (SCD) stage performs a dimension lookup, it retrieves the value of the existing surrogate key if a matching record is found. If a match is not found, the stage obtains a new surrogate key value by using the derivation of the Surrogate Key column in the output table columns.

If you want the SCD stage to generate new surrogate keys by using a key source that you created with a Surrogate Key Generator stage, you must use the NextSurrogateKey function to derive the Surrogate Key column. If you want to use your own method to handle surrogate keys, derive the Surrogate Key column from a source column.

You can use Oracle, ODBC, or Db2 database sequences as connections for the SCD stage. The password for the connection needs to be parameterized to an encrypted password to run end to end.

Note: If you select a Db2® database sequence, you must configure the Db2 connection first. See Configuring Db2 connections to work with database sequences in DataStage.

When you choose ODBC as your source connector, then you can select Data source type: Snowflake, Db2, or Oracle. When you choose Snowflake, then you can only use Flow connection.

You can replace the dimension information in the source data stream with the surrogate key value by mapping the Surrogate Key column to the output link.

For more information, see Specifying information about a key source in the DataStage Slowly Changing Dimension stage and Creating derivations for dimension columns in the DataStage Slowly Changing Dimension stage.