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.
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:
The following figure shows a mapping that the Getting Started Wizard creates when you create a slowly growing target 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:
Steps to Create a Slowly Growing Target Mapping
To create a slowly growing target mapping:
All available source definitions appear in the Select Source Table list. This list includes shortcuts, flat file, relational, and Application sources.
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.
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:
Using The Designer
Working With Sources
Working With Flat Files
Working With Targets
Mapping Parameters And Variables
Working With User-defined Functions
Using The Debugger
Viewing Data Lineage
Managing Business Components
Creating Cubes And Dimensions
Using The Mapping Wizards
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.