Creating a Type 1 Dimension Mapping - Informatica

The Type 1 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 by overwriting the existing dimension. In the Type 1 Dimension mapping, all rows contain current dimension data.

Use the Type 1 Dimension mapping to update a slowly changing dimension table when you do not need to keep any previous versions of dimensions in the table.

For example, you might have a site dimension table with store code, location, and overhead that you update after the company opens a new store. This dimension is used for sales and overhead calculations. Since you do not need to know the previous address of the same store or the store overhead from the previous year, you do not need previous dimension data in the table. With the Type 1 Dimension mapping, you can keep current data without a historical log.

Handling Keys

When you use the Type 1 Dimension option, the Designer creates an additional column in the mapping target, PM_PRIMARYKEY. In this column, the Integration Service generates a primary key for each row written to the target, incrementing new key values by 1.

Understanding the Mapping

The Type 1 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 changed rows.
  • Generates a primary key for new rows.
  • Inserts new rows to the target.
  • Updates changed rows in the target, overwriting existing rows.

The following figure shows a mapping that the Slowly Changing Dimensions Wizard creates when you select the Type 1 Dimension option:

Understanding the Mapping

The Type 1 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.

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 separate 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, and a Sequence Generator creates a primary key for each row.

In the second data flow, the FIL_UpdateChangedRecord Filter transformation allows only changed rows to pass to the Update Strategy transformation, UPD_ChangedUpdate. UPD_ChangedUpdate replaces existing rows in the target with the updated source rows.

Understanding the Transformations

The following table describes the function of each transformation in the Type 1 Dimension mapping:

Understanding the Transformations

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.

The following table describes the data flow for new rows:

Data Flow for New Rows

Data Flow for Changed Rows

For each changed row in the source, this data flow marks the row for update and overwrites the corresponding row in the target.

The following table describes the data flow for changed rows:

Data Flow for Changed Rows

Steps to Create a Type 1 Dimension Mapping

To create a slowly growing target mapping, complete the following steps.

To create a Type 1 Dimension mapping:

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

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

Note:In the Type 1 Dimension mapping, the Designer uses two instances of the same target definition to enable inserting and updating data in the same target table. Generate only one target table in the target database.

Configuring a Type 1 Dimension Session

The Type 1 Dimension mapping inserts new rows with a new primary key and updates existing rows. When you create a session for the mapping, configure the session as follows:

  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