SCD - Creating a Type 2 Dimension - Effective Date Range

SCD Type 2:

Slowly Changing Dimension Type 2 - Effective Date Range:

The Slowly Changing Dimension Type 2 is used to maintain complete history in the target. The source rows based on user-defined comparisons and inserts both new and changed (as a new entry) dimensions into the target.

Changes are tracked in the target table by maintaining an effective date range for each version of each dimension in the target. In the Type 2 Dimension/Effective Date Range target, the current version of a dimension has a begin date with no corresponding end date.

The following example are additional fields in the target:

	·         PM_BEGIN_DATE. For each new and changed dimension written to the target, the Integration Service uses the system date to indicate the start of the effective date range for the dimension.
	·         PM_END_DATE. For each dimension being updated, the Integration Service uses the system date to indicate the end of the effective date range for the dimension.
	·         PM_PRIMARYKEY. The Integration Service generates a primary key for each row written to the target.

Note: The primary key from the source table will be Natural key in the dimension table, because surrogate key will be primary here. Because a Natural key will have multiple entries.

Handling Keys

When you use the Effective Date Range option, the Integration Service generates a primary key value for each row written to the target, incrementing key values by one.

SCD Type 2 can be designed in two ways:

1.    Design a job using normal/static lookup on table which will create cache ever time the job run. Click her for details mapping

2.    Design a job using dynamic lookup on table which will create cache first run and keep on checking, updating cache from next run. This will not check for the target table every time to create cache. Click her for details mapping