Creating a Type 2 Dimension/Flag Current Mapping - Informatica

The Type 2 Dimension/Flag Current 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 flagging the current version of each dimension and versioning the primary key. In the Type 2 Dimension/Flag Current target, the current version of a dimension has a current flag set to 1 and the highest incremented primary key.

Use the Type 2 Dimension/Flag Current mapping to update a slowly changing dimension table when you want to keep a full history of dimension data in the table, with the most current data flagged. Versioned primary keys track the order of changes to each dimension.

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

  • PM_CURRENT_FLAG. The Integration Service flags the current row “1” and all previous versions “0.”
  • PM_PRIMARYKEY. The Integration Service generates a primary key for each row written to the target.

Flagging the Current Value

The Integration Service generates a current flag of 1 for each row written to the target. This flag indicates the dimension is new or newly updated. If the row is an update to an existing dimension, the Integration Service resets the existing dimension current flag to 0.

As a result, all current versions of a dimension appear in the target with a current flag of 1. All previous versions have a current flag of 0.

For example, the following dimension data is current because the current flag is set to 1:

Flagging the Current Value

When these dimensions change, the Integration Service inserts the updated versions with the current flag of 1. The Integration Service also updates the existing rows in the target. It finds the previous current version (where the current flag is set to 1 and updates the current flag to 0:

Flagging the Current Value

Handling Keys

When you use the Flag Current option, the Integration Service generates a primary key value for each row written to the target, incrementing key values by one. An Expression transformation increments key values by 1,000 for new dimensions. When updating an existing dimension, the Integration Service increments the existing primary key by 1.

For example, the following dimensions are current dimension data (current flags set to one). Their primary keys are multiples of 1,000. This indicates they are both the first version of a dimension:

Handling Keys

When the Integration Service inserts updated versions of these dimensions into the target, it sets the current flag to 1. It also creates a new primary key for the updated row by incrementing key of the existing dimension by one.

The Integration Service indicates the existing dimension is no longer current by resetting its current flag to 0:

Handling Keys

Understanding the Mapping

The Type 2 Dimension/Flag Current 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 two data flows: one for new rows, one for changed rows.
  • Generates a primary key and current flag for new rows.
  • Inserts new rows to the target.
  • Increments the existing primary key and sets the current flag for changed rows.
  • Inserts changed rows in the target.
  • Updates existing versions of the changed rows in the target, resetting the current flag to indicate the row is no longer current.

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

Understanding the Mapping

The Type 2 Dimension/Flag Current 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 source columns that you want the Integration Service to compare against the existing target.

For each source row without a matching 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 new row. The Expression transformation, EXP_KeyProcessing_InsertNew, increases the increment between keys by 1,000 and creates a current flag of 1 for each new row.

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, increments the primary key by one and creates a current flag of 1 to indicate the updated 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 dimensions in the target. The Expression transformation, EXP_UpdateChanged, sets the current flag to 0. This changes the status of the previous dimension from current to not-current.

Understanding the Transformations

The following table describes the function of each transformation in the Type 2 Dimension/Flag Current 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 and increments it by 1,000. It also flags the row current and inserts it 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 increments the existing primary key by 1, flags the row current, and inserts it 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/Flag Current Mapping

To create a Type 2 Dimension/Flag Current 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.
  11. The wizard adds selected columns to the Fields to Compare for Changes list.

    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 (lookup) 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. Select Mark the ‘Current’ Dimension Record with a Flag.
  13. Click Finish.
  14. The new mapping appears in the workspace. Make the necessary edits to the mappings.

Note:In the Type 2 Dimension/Flag Current 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/Flag Current Session

The Type 2 Dimension/Flag Current 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