Understanding Slowly Changing Dimensions in EPM

EFF_START_DT and EFF_END_DT

For type 1 slowly changing dimensions, the EFF_START_DT and EFF_END_DT columns are assigned default values. EFF_START_DT is set to Jan-01-1753 and EFF_END_DT is set to Dec-31-9999.

For type 2 slowly changing dimensions, the EFF_START_DT and EFF_END_DT columns serve to partition the related dimension records and indicate which version is active. When a changed record is extracted into an MDW dimension table, the new record is assigned an EFF_START_DT value, which is derived from the EFFDT column. The old record is assigned an EFF_END_DT value equal to the new EFFDT minus one day (EFFDT - 1 day = EFF_END_DT of old record), and the new record is assigned an EFF_END_DT value equal to Dec-31-9999.

CURRENT_IND

When a control (dimension) table in the source system has multiple records with the same business keys and different effective dates, the corresponding tables in the MDW also have multiple records with the same business keys and different EFF_START_DT and EFF_END_DT values. The applications only use the row that is currently valid (when the system date falls between the EFF_START_DT and EFF_END_DT values).

To help determine which records are valid and active, the CURRENT_IND column has been added to dimension tables and it indicates whether a row is active for a given system date. The CURRENT_IND column uses two-valued logic. Current rows are marked with CURRENT_IND = 'Y' and past and future dated rows are marked with CURRENT_IND = 'N'.

For a type 1 slowly changing dimension implementation, this column will have a have default value of 'Y'.

Source Query

The source query for a type 1 slowly changing dimension has a correlated sub query to take the latest effective dated row from the source table in the source DRS (Dynamic Relational Stage).

The source query for the type 2 slowly changing dimension does not have a correlated sub query; instead it uses an ORDER BY clause based on the effective date from the source table in the source DRS (Dynamic Relational Stage).

Target DRS Stage

There is only one target DRS stage for a type 1 slowly changing dimension and it uses an update existing rows or insert new rows logic for its loading strategy.

There are two target DRS stages for the type 2 slowly changing dimension:

• The first target DRS stage uses an update existing rows only logic for its loading strategy.

  The link with update existing rows only has the constraint SCDFlag='Y' so that it will update the EFF_END_DT and CURRENT_IND columns of the old dimension record.

• The second target DRS stage uses an update existing rows or insert new rows logic for its loading strategy.

Image: Slowly changing dimension and target DRS stage

This example illustrates the fields and controls on the Slowly changing dimension and target DRS stage. You can find definitions for the fields and controls later on this page.

Target Lookup Stage

If the incoming rows already exist in the dimension table, the type 1 slowly changing dimension lookup stage retrieves the SID value using a lookup on the target dimension that matches the business keys from the incoming row with those of the target table. If the keys match, the existing SID is extracted.

There are two target lookup stages for the type 2 slowly changing dimension:

• The first target lookup stage retrieves the latest SID in case the incoming rows are already there in the target dimension table.

  The first lookup should have EFFDT as key column and it must be joined with incoming row to get the SID value if the incoming dimensional row is already there in the target table.

• The second target lookup stage indicates whether the incoming row falls under slowly changing dimension logic.

  It is loaded in the same job with the latest EFFDT and SID value to compare it with incoming data. If the incoming row falls under slowly changing dimension logic, the SID is retrieved and the EFF_END_DT column is updated for the old dimension row. As soon as we processed the SCD logic, we will update the second lookup in the same job.

  Please refer the figure below to get the information about the lookups (HASH_PS_D_EMPL_JOB_SCD1 and HASH_PS_D_EMPL_JOB_SCD) that are used to determine the SCD logic. The following Stage Variables are added to determine the SCD logic: SCDFlag, EFFENDDTUPD and EFFENDDT.

Image: Slowly changing dimension and target lookup stage

This example illustrates the fields and controls on the Slowly changing dimension and target lookup stage. You can find definitions for the fields and controls later on this page.