Creating a Slowly Growing Target Mapping - Informatica

The slowly growing target mapping filters source rows based on user-defined comparisons, and then inserts only those found to be new to the target. Use the slowly growing target mapping to determine which source rows are new and to load them to an existing target table. In the slowly growing target mapping, all rows are current.

Use the slowly growing target mapping to load a slowly growing fact or dimension table, one in which existing data does not require updates.

For example, you have a site code dimension table that contains only a store name and a corresponding site code that you update only after the company opens a new store. Although listed stores might close, you want to keep the store code and name in the dimension for historical analysis. With the slowly growing target mapping, you can load new source rows to the site code dimension table without deleting historical sites.

Handling Keys

When you create a slowly growing target mapping, 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 slowly growing target 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
  • Filters out existing rows
  • Generates a primary key for new rows
  • Inserts new rows to the target

The following figure shows a mapping that the Getting Started Wizard creates when you create a slowly growing target mapping:

Understanding the Mapping

The slowly growing target mapping uses a Lookup and an Expression transformation to compare source data against existing target data. When you step through the Getting Started Wizard you enter the logical key columns in the source to compare against the existing target. When the Expression transformation detects source rows without matching key columns in the target, it flags the row new.

A Filter transformation passes only new rows to the Update Strategy transformation. The Update Strategy transformation marks new rows for insert and a Sequence Generator creates a new primary key value for each row written to the target.

Understanding the Transformations

The following table describes the function of each transformation in the slowly growing target mapping:

UnderstandingtheTransformations

Steps to Create a Slowly Growing Target Mapping

To create a slowly growing target mapping:

  1. In the Mapping Designer, click Mappings > Wizards > Getting Started.
  2. Enter a mapping name and select Slowly Growing Target, and click Next.
  3. The naming convention for mapping names is m_MappingName.
  4. Select a source definition to be used in 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 from the Target Table Fields list that you want the Integration Service to use to look up data in the target table. 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 a 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.

    Note:You cannot add a port using the name, FILLER, to the Logical Key field list.

  10. Click Finish.
  11. The new mapping appears in the workspace. Make necessary edits to the mapping.

    Note:The Fields to Compare for Changes field is disabled for the slowly growing target mapping.

Configuring a Slowly Growing Target Session

The slowly growing target mapping flags new source rows, and then inserts them to the target with a new primary key. The mapping uses an Update Strategy transformation to indicate new rows must be inserted. Therefore, 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 for each relational target.

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

Informatica Topics