Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(1 Rating)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

MAPPING TEMPLATES: Dynamic lookup caching


Content

This mapping uses Dynamic Lookup Cache functionality to detect duplicate records coming from the source.


 

Source data often contains duplicate records. It is typically desirable to insert only one instance of a unique record into the target. This mapping provides an alternative for detecting duplicate records from the source.

There are a few different methods of eliminating duplicate records that exist in a source table. This mapping template illustrates a method of using a PowerMart / PowerCenter dynamic lookup to check whether the record is new or if it already exists in the target. Dynamic lookups update the lookup cache "on the fly" and can determine if a record is an insert or update to the target. Unlike un-cached lookups, the cache file is updated each time a row is inserted/updated into the target table.

Implementation Guidelines

The mapping in this template uses a Lookup transformation object called lkp_PRODUCT_DIMENSION. The Lookup transformation uses a dynamic lookup cache. When the session starts, the Informatica Server builds the lookup cache from the target table, PRODUCT_DIMENSION. When lkp_PRODUCT_DIMENSION evaluates a row from a source that does not exist in the lookup cache, it inserts the row into cache and assigns the NewLookupRow output port the value of 1. Whenlkp_PRODUCT_DIMENSION evaluates a row from the source that does exist in the lookup cache, it does not insert the row into cache and assigns NewLookupRow output port the value of 0.

The filter in this mapping flt_DUPLICATE_ROW checks if the row is a duplicate or not by evaluating the NewLookupRow output port. If the value of the port is 0, the row is filtered out, as it is a duplicate row. If the value of the port is not equal to 0, then the row is passed out to the target table.

In this example you can test the mapping with 3 scenarios:

  1. The first time the mapping is run, all records should be inserted into the target if every row is unique.
  2. If the mapping is run again with the same source data and the target table with the data from the previous run, no records will be inserted.
  3. If you modify the source table to have three records that are all duplicates when the session is run, the row will only be inserted once.


Pros

  • Duplicates can be detected real-time during the session run.

Cons

  • If the target table starts to grow very large the cache file will become very large, potentially increasing the amount of time required to create the cache.
More Information


Reference


Attachments


Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:

Last Modified Date: 5/9/2011 4:24 PM ID: 16309
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)