Informatica Workshop: Dynamic Cache

1. What is a lookup cache?

Cache is the temporary memory that is created when you execute the process\session.

2. When is lookup cache created?

Cache is created automatically when the process starts and is deleted automatically once the process is completed.

3. Where is the Cache created? Path?

$PMCacheDir --> $PMRootDir/cache
[ Note: $PMRootDir --> D:/Informatica/9.5.1/Server/InfaShared ]

4. What is the default cache?

By default, Non Persistent Cache is created. i.e Static Cache.

5. What are the types of cache?

1. Persistent Cache
2. Non Persistent Cache - Static Cache and Dynamic Cache

6. What is Persistent Cache?

By default, caches are created as non persistent, it will get deleted once the session run is completed.
You can configure to permanently store data.

Example 1: A mapping/process runs every day and the mapping has the lookup transformation to lookup references table that is not supposed to change for 6 months,

Solution 1: We can opt for Persistent cache,
Select the below properties:

1. Lookup Cache Persistent
2. Cache file name prefix (Cache file such as data and index files are created with the mentioned name)
3. Re-Cache from lookup source (If we need a rebuild of the cache in case there is a update of the lookup table)

Lookup Transformation - Persistent Cache - Property Setting

7. What are the differences between Static Cache and Dynamic Cache?

Static Cache :

A Cache is said to be Static if it does not change with the changes happening in the lookup table,
Integration Service does not update the Cache when it is processing the date.
The Static Cache is not synchronized with the target lookup
It is the default cache.

Dynamic Cache:

A Cache is said to be dynamic if it changes with the changes happening in the lookup table.
Integration service updates the cache while it is processing the data.
The Dynamic Cache is synchronized with the target table.
Set the below properties:

Dynamic Lookup Cache
Insert Else Update

A new output port is created NewLookupRow.

NewLookupRow = 0 (Unchanged - IS performs no change)
NewLookupRow = 1 (Insertion - Integration Service inserts the row into the cache)
NewLookupRow = 2 (Update - Integration Service updates the row into the cache)

Reference Pics Below

Lookup Transformation - Dynamic Cache - Property Settings

Using Lookup Transformation - Dynamic Cache

With a dynamic cache, the Integration Service inserts or updates rows in the cache. When you cache the target table as the lookup source, you can look up values in the cache to determine if the values exist in the target. The Lookup transformation marks rows to insert or update the target.