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.
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:
Handling duplicate rows using a PowerCenter mapping (15175)
What can we do to improve this information (2000 or fewer characters)