Creating a Type 2 Dimension/Effective Date Range Mapping - Informatica

The Type 2 Dimension/Effective Date Range mapping filters source rows based on user-defined comparisons and inserts both new and changed 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.

Use the Type 2 Dimension/Effective Date Range mapping to update a slowly changing dimension table when you want to keep a full history of dimension data in the table. An effective date range tracks the chronological history of changes for each dimension.

When you use this option, the Designer creates the following additional fields in the target:

  • PM_BEGIN_DATE. Foreach 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. Foreach 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.

Maintaining the Effective Date Range

The Integration Service generates a begin date for each new and changed dimension it inserts into the target, using the current system date. The end date for these dimensions is NULL.

Each time the Integration Service inserts a changed dimension, it updates the previous version of the dimension in the target, using the current system date to fill the previously null end date column.

As a result, all current dimension data in the Type 2 Dimension/Effective Date Range target have null values in the PM_END_DATE column. All previous versions of dimension data have a system date in PM_END_DATE to indicate the end of the effective date range for each version.

For example, the following dimensions are current dimension data since their end date columns are null:

Maintaining the Effective Date Range

When the Integration Service finds updated versions of these dimensions in the source, it inserts them into the target, using the system date to indicate the beginning of their effective date ranges and leaving the end dates null.

The Integration Service also updates the existing versions in the target, entering the system date to end the effective date range:

Maintaining the Effective Date Range

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.

Understanding the Mapping

The Type 2 Dimension/Effective Date Range mapping performs the following tasks:

  • Selects all rows.
  • Caches the existing target as a lookup table.
  • Compares logical key columns in the source against corresponding columns in the target lookup table.
  • Compares source columns against corresponding target columns if key columns match.
  • Flags new rows and changed rows.
  • Creates three data flows: one for new rows, one for changed rows, one for updating existing rows.
  • Generates a primary key and beginning of the effective date range for new rows.
  • Inserts new rows to the target.
  • Generates a primary key and beginning of the effective date range for changed rows.
  • Inserts changed rows in the target.
  • Updates existing versions of the changed rows in the target, generating the end of the effective date range to indicate the row is no longer current.

The following figure shows a mapping that the Type 2 Dimension/Effective Date Range option in the Slowly Changing Dimensions Wizard creates:

Understanding the Mapping

The Type 2 Dimension/Effective Date Range mapping uses a Lookup and an Expression transformation to compare source data against existing target data. When you step through the Slowly Changing Dimensions Wizard, you enter the lookup conditions (source key columns) and columns that you want the Integration Service to compare against the existing target.

For each source row without a primary key in the target, the Expression transformation marks the row new. For each source row with a matching primary key in the target, the Expression compares user-defined source and target columns. If those columns do not match, the Expression marks the row changed. The mapping then splits into three data flows.

The first data flow uses the Filter transformation, FIL_InsertNewRecord, to filter out existing rows. The Filter transformation passes only new rows to the UPD_ForceInserts Update Strategy transformation. UPD_ForceInserts inserts new rows to the target. A Sequence Generator creates a primary key for each row. The Expression transformation, EXP_KeyProcessing_InsertNew, uses the system date to indicate the start of the effective date range. The transformation leaves the end date null, whichindicates the new row contains current dimension data.

In the second data flow, the FIL_InsertChangedRecord Filter transformation allows only changed rows to pass to the Update Strategy transformation, UPD_ChangedInserts. UPD_ChangedInserts inserts changed rows to the target. The Expression transformation, EXP_ KeyProcessing _ InsertChanged, uses the system date to indicate the start of the effective date range. The transformation leaves the end date null, which indicates the changed row contains current dimension data.

In the third data flow, for each changed row written to the target, the Filter transformation, FIL_UpdateChangedRecord, passes the primary key of the previous version to the Update Strategy transformation, UPD_ChangedUpdate. UPD_ChangedUpdate updates rows in the target. The Expression transformation, EXP_UpdateChanged, updates the end date column with the system date. This changes the status of the dimension from the current version to a previous version.

Understanding the Transformations

The following table describes the function of each transformation in the Type 2 Dimension/Effective Date Range mapping:

Understanding the Transformations

Data Flow for New Rows

The following table describes the data flow for new rows. For each new row in the source, this data flow creates a primary key, sets the beginning of the effective date range, and inserts the row into the target:

Data Flow for New Rows

Data Flow for Changed Rows

The following table describes the data flow for changed rows. For each changed row in the source, this data flow creates a new primary key, sets the beginning of the effective date range, and inserts the row into the target:

Data Flow for Changed Rows

Data Flow to Update Existing Rows

The following table describes the data flow for existing rows. For each changed row in the source, this data flow updates the end date of the corresponding row in the target to indicate the existing row is no longer current:

Data Flow to Update Existing Rows

Steps to Create a Type 2 Dimension/Effective Date Range Mapping

To create a Type 2 Dimension/Effective Date Range mapping:

  1. In the Mapping Designer, click Mappings > Wizards > Slowly Changing Dimensions.
  2. Enter a mapping name and select Type 2 Dimension. Click Next.
  3. The naming convention for mappings is m_MappingName.
  4. Select a source definition to be used by the mapping.
  5. All available source definitions appear in the Select Source Table list. This list includes shortcuts, flat file, relational, and Application sources.

  6. Enter a name for the mapping target table. Click Next.
  7. The naming convention for target definitions is T_TARGET_NAME.
  8. Select the column or columns you want to use as a lookup condition from the Target Table Fields list and click Add.
  9. The wizard adds selected columns to the Logical Key Fields list.

    Tip:The columns you select should be a key column in the source.

    When you run the session, the Integration Service performs a lookup on existing target data. The Integration Service returns target data when Logical Key Fields columns match corresponding target columns. To remove a column from Logical Key Fields, select the column and click Remove.

  10. Select the column or columns you want the Integration Service to compare for changes, and click Add. The wizard adds selected columns to the Fields to Compare for Changes list.
  11. When you run the session, the Integration Service compares the columns in the Fields to Compare for Changes list between source rows and the corresponding target rows. If the Integration Service detects a change, it marks the row changed. To remove a column from the list, select the column and click Remove.

  12. Click Next.
  13. Select Mark the Dimension Records with their Effective Date Range. Click Finish.

The new mapping appears in the workspace. Make the necessary edits to the mappings.

Note:In the Type 2 Dimension/Effective Date Range mapping, the Designer uses three instances of the same target definition to enable the three separate data flows to write to the same target table. Generate only one target table in the target database.

Configuring a Type 2 Dimension/Effective Date Range Session

The Type 2 Dimension/Effective Date Range mapping inserts both new and updated rows with a unique primary key. It also updates existing rows in the target. When you configure a session for the mapping, complete the following steps:

  1. In the session properties, click the General Options settings on the Properties tab. Set Treat Source Rows As to Data Driven.
  2. In the session properties, click the Target Properties settings on the Mappings tab. To verify that the Integration Service loads rows to the target properly, select Insert and Update as Update for each relational target

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Informatica Topics