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 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.

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.