Creating a Type 3 Dimension Mapping - Informatica

The Type 3 Dimension mapping filters source rows based on user-defined comparisons and inserts only those found to be new dimensions to the target. Rows containing changes to existing dimensions are updated in the target. When updating an existing dimension, the Integration Service saves existing data in different columns of the same row and replaces the existing data with the updates. The Integration Service optionally enters the system date as a timestamp for each row it inserts or updates. In the Type 3 Dimension target, each dimension contains current dimension data.

Use the Type 3 Dimension mapping to update a slowly changing dimension table when you want to keep only current and previous versions of column data in the table. Both versions of the specified column or columns are saved in the same row.

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

  • PM_PREV_ColumnName. The Designer generates a previous column corresponding to each column for which you want historical data. The Integration Service keeps the previous version of dimension data in these columns.
  • PM_PRIMARYKEY. The Integration Service generates a primary key for each row written to the target.
  • PM_EFFECT_DATE. An optional field. The Integration Service uses the system date to indicate when it creates or updates a dimension.

Saving Previous Values

When you step through the Slowly Changing Dimensions Wizard, you choose the columns in which you want to detect changes. The Designer creates an additional column for each column you select and names the additional column after the original, PM_PREV_ColumnName. The Integration Service uses these columns to store previous dimension values.

When the Integration Service writes a new dimension to the target, the previous columns remain null. Each time the Integration Service updates a dimension, it writes existing data into the corresponding previous column, and then writes updates into the original column. As a result, each row in a Type 3 Dimension target contains current dimension data. Each row also contains the previous versions of dimension data, if the dimension has changed.

For example, the first time the Integration Service writes the following dimensions to the target, the previous column, PM_PREV_STYLES remains null:

Saving Previous Values

When the Integration Service updates these rows, it writes the values in the STYLES column (20 and 25) into PM_PREV_STYLES, and then replaces the style data with new style data (14 and 31):

Saving Previous Values

Handling Keys

In the Type 3 Dimension mapping, the Integration Service generates a primary key value for each new row written to the target, incrementing key values by one. Updated rows retain their original key values.

Marking the Effective Date

The Type 3 Dimension mapping can optionally note the date on which the Integration Service creates or updates a dimension. If you choose this option, the Designer creates the PM_EFFECT_DATE column. The Integration Service enters the system date in this column each time it creates a new row or updates a row.

Understanding the Mapping

The Type 3 Dimension 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 updating changed rows.
  • Generates a primary key and optionally notes the effective date for new rows.
  • Inserts new rows to the target.
  • Writes previous values for each changed row into previous columns and replaces previous values with updated values.
  • Optionally uses the system date to note the effective date for inserted and updated values.
  • Updates changed rows in the target.

The following figure shows a mapping that the Type 3 Dimension option in the Slowly Changing Dimensions Wizard creates:

Understanding the Mapping

The Type 3 Dimension 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. The Designer creates additional columns for the change columns to hold historic data.

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 two data flows.

The first data flow uses the Filter transformation, FIL_InsertNewRecord, to filter out 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. If you select the Effective Date option in the mapping wizard, the Designer creates an Expression transformation, EXP_EffectiveDate_InsertNew.

The Integration Service uses the system date to indicate when it creates a new row.

In the second data flow, the FIL_UpdateChangedRecord Filter transformation allows only changed rows to pass to the Update Strategy transformation UPD_ChangedInserts. In addition, the Filter transformation updates the changed row: it takes the new versions of data from the source qualifier, and uses existing versions of dimension data (passed from the Lookup transformation) to populate the previous column fields. UPD_ChangedInserts inserts changed rows to the target. If you select the Effective Date option in the mapping wizard, the Designer creates an Expression transformation, EXP_EffectiveDate_InsertChanged. The Integration Service uses the system date to indicate when it updates a row.

Understanding the Transformations

The following table describes the function of each transformation in the Type 3 Dimension 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, optionally notes the load date, and inserts the row into the target:

Data Flow for New RowsData Flow for New 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 corresponding row in the target, copying existing data into previous columns, updating new data, and optionally notes the date the row changed:

Data Flow to Update Existing Rows

Steps to Create a Type 3 Dimension Mapping

To create a Type 3 Dimension mapping:

  1. In the Mapping Designer, click Mappings > Wizards > Slowly Changing Dimensions.
  2. Enter a mapping name and select Type 3 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.

    Note:Select the columns for which you want to keep previous values. To keep previous values in the target, the Designer creates an additional column for each column in this list. It names the columns PM_PREV_ColumnName. To remove a column from the list, select the column and click Remove.

  12. Click Next.
  13. If you want the Integration Service to timestamp new and changed rows, select Effective Date.
  14. The wizard displays the columns the Integration Service compares and the name of the column to hold historic values.

  15. Click Finish.

Note:In the Type 3 Dimension mapping, the Designer uses two instances of the same target definition to enable the two separate data flows to write to the same target table. Generate only one target table in the target database.

Configuring a Type 3 Dimension Session

The Type 3 Dimension mapping inserts new rows and updates existing rows in the target. When you configure a session, complete the following steps:

  1. Click the General Options settings on the Properties tab. Set Treat Source Rows As to Data Driven.
  2. 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