Informatica Reference

Dynamic Lookup Cache Example

Posted on: 12/16/2011

Use a dynamic lookup cache when you need to insert and update rows in the target. When you use a dynamic lookup cache, you can insert and update the cache with the same data you pass to the target to insert and update.

For example, you need to update a table that contains customer data. The source data contains rows of customer data to insert or update in the target. Create a dynamic cache that represents the target. Configure a Lookup transformation to lookup customers in the cache.

The following figure shows a mapping that has a dynamic cache:

Dynamic Lookup Cache Example

Dynamic Lookup Cache Example

The Lookup transformation uses a dynamic lookup cache. When the session starts, the Integration Service builds the lookup cache from a Customer_List table. The Customer_List table is also the target in the mapping. When the Integration Service reads a row that is not in the lookup cache, it inserts the row in the cache.

The Lookup transformation returns the row to a Router transformation. The Router transformation directs the row to the UPD_Insert_New or the UPD_Update_Existing transformation. The Update Strategy transformations mark the row as insert or update before passing it to the target.

The Customer_List table changes as the session runs. The Integration Service inserts new rows and updates existing rows in the lookup cache. The Integration Service keeps the lookup cache and Customer_List table synchronized.

To generate keys for the target, use Sequence-ID in the associated port. The sequence ID generates primary keys for new rows the Integration Service inserts into the target table.

You increase session performance when you use a dynamic lookup cache because you build the cache from the database once. You can continue to use the lookup cache even though the data in the target table changes.


Get every new post delivered to your Inbox.

Join 66 other followers

%d bloggers like this: