1. Introduction – Static vs Dynamic Lookup
When you use a Lookup transformation in Informatica Cloud, by default lookup caching is enabled with in the transformation properties. That implies the Integration service builds a cache file when it processes the first lookup request and instead of looking up the actual object configured for lookup, the lookup transformation looks up the cache file for a match for each record.
The lookup cache can be either static or dynamic. If the lookup cache is static (which is by default), the data in the lookup cache doesn’t change as the mapping task runs. If you enable the lookup cache to be dynamic, the cache gets updated based on the changes in the task.
There is a major difference how static and dynamic lookups behaves when the lookup condition is TRUE or FALSE.
| CONDITION | STATIC LOOKUP | DYNAMIC LOOKUP |
| When the lookup condition is TRUE | The task returns a value from the lookup object or cache. | The task either updates the cache or leaves the cache unchanged based on the lookup configuration. |
| When the lookup condition is FALSE | The task returns a NULL value. | The task inserts the row in the cache. |
Related Article: Lookup Transformation in Informatica Cloud
2. When Dynamic Lookup is required?
A Dynamic lookup is helpful when you are using the target object also as a lookup object in the same mapping. The changes that are done on the target object during the mapping run are captured and updated in the lookup cache and it helps to process the data correctly when the source data contains duplicate primary keys.
The following example illustrates the advantage of dynamic cache rather than a static cache when the source has duplicate primary keys.
Consider below employees table with salary information.
| Employee_ID | Name | Salary |
| 100 | Jennifer | 2000 |
| 101 | Mark | 3000 |
You receive a source file with list of all employees and you need to insert the data of new employees into the target table. The source file is expected to have duplicates and the latest records needs to be considered to make entries into target table. You have built a mapping with employees table as a lookup transformation and use the employee table for both target and lookup.
Consider below as the source file with employees information.
| Employee_ID | Name | Salary |
| 100 | Jennifer | 2500 |
| 101 | Mark | 3000 |
| 102 | Henry | 4000 |
| 102 | Henry | 4400 |
If you have used a static cache, the first two records are considered for update since there is a match from lookup. The third row is marked as Insert as there is no match from lookup. The fourth record however which should be marked as an update will still be considered for insert since the cache do not have information of Henry. This results in duplicates in target or the target produces error as it cannot have two rows with same key.
This issue however will not arise if dynamic cache is enabled. When the task processes the rows, the first two records are marked for update and updates the cache with latest information. The third row is marked as insert and inserts the row in cache. The fourth row is marked as update since the row is cached already.
Before we look at a more real time SCD type-2 scenario as an example, let us understand more about how Dynamic lookup works and its other properties.
3. NewLookupRow in Dynamic Lookup
When a lookup is configured for dynamic cache, the transformation includes a new return field NewLookupRow, which describes the changes made to cache for each input row. Based on the value of NewLookupRow, you can configure a Filter or a Router transformation to route the insert and update records.
Based on the lookup condition is either true or false and the Lookup transformation properties configured, the mapping task performs one of the below action on dynamic cache for every row read from source.
Inserts the row into the cache
- The mapping task inserts the row when the row is not in the cache. The mapping task flags the row as INSERT.
Updates the row in the cache
- The mapping task updates the row when the row exists in the cache and the row has changed. The mapping task flags the row as an UPDATE.
Makes no change to the cache
- The mapping task makes no change to the cache when the row is present in the cache and nothing changes. The mapping task flags the row as UNCHANGED.
The NewLookupRow values returned for each change done to cache are
| NewLookupRow | Description |
| 0 | Unchanged |
| 1 | Insert |
| 2 | Update |
These NewLookupRow values can be used in the downstream transformations to flag a row for Insert, Update or dropped.
4. Dynamic Lookup properties in Informatica Cloud
When you configure the lookup transformation to use dynamic cache from Advanced properties tab, several other option that are associated to dynamic cache becomes available in Advanced tab. A new tab Field Mapping gets enabled in the properties section and in the Return Fields tab, Ignore In Comparison option is enabled.
Let us discuss in detail about each of these properties.
4.1 Field Mapping
When you use a dynamic lookup cache, you have to map the incoming fields with the fields present in the cache on the Field Mapping tab. The Field Mapping tab is only available when you configure the Lookup transformation to use a dynamic cache.
The fields in the lookup cache can be added or removed through Return Fields tab. Remove the fields which are not required for the lookup so that the comparison becomes easy as well as the cache size would be small.
You must map all the lookup cache fields in the Field Mapping tab with the incoming fields so that the cache can update as the task runs.
4.2 Ignore Fields in Comparison
When you use a dynamic lookup cache, you can configure to ignore some of the fields in comparison between lookup cache and incoming fields defined in Field Mapping tab.
Sometimes it is necessary to ignore the field mapping in cases where you are not actually having an incoming field to map with lookup cache fields like primary keys and audit fields which would be either calculated in the later stages of the mapping or not required in actual row comparison.
You can configure the fields to be ignored on the Return Fields tab of the Lookup transformation. To ignore a field, enable the Ignore In Comparison property for the field.
You must configure the transformation to compare at least one field other than the fields used in the lookup condition tab.
4.3 Insert Else Update
When you use a dynamic lookup cache, you can configure how the data integration handles the inserts and updates to the cache. To update existing rows in the dynamic lookup cache, enable the Insert Else Update advanced property for the transformation.
When you enable Insert Else Update property, the mapping task inserts the row into the cache if it is new. If the row already exists in the cache but the data cache is different than the current incoming row, the mapping task updates the row in the data cache.
If you do not enable Insert Else Update property, the mapping task inserts the row into the cache if it is new, and makes no change to the cache if the row exists.
The following table explains how the mapping task changes the lookup cache when the Insert Else Update advanced property is enabled.
| Insert Else Update Option | Row found in cache? | Data cache is different? | Lookup Cache Result | NewLookupRow Value |
| Enabled | Yes | No | No change | 0 |
| Enabled | No | – | Insert | 1 |
| Enabled | Yes | Yes | Update | 2 |
| Disabled | Yes | – | No change | 0 |
| Disabled | No | – | Insert | 1 |
If Insert Else Update property is not enabled, the NewLookupRow returns only either 0 or 1 as output value.The NewLookupRow returns output value as 2 only when Insert Else Update property is enabled.
4.4 Output Old Value On Update
When the NewLookupRow returns the output value as 2 indicating that the record needs to be updated in the target database, you still need the old values from cache to identify the record in target database to update the value with the incoming source row.
When an incoming source row is present in the lookup cache but is different, enable Output Old Value On Update advanced propertyto return the old value from cache for Return Fields before updating the row in dynamic cache. Do not enable the property if you want the return fields also to be same as the incoming fields which got updated into the dynamic cache.
4.5 Synchronize dynamic cache
To synchronize the cache with the lookup source (object configured in the lookup transformation. It could be same as your target), enable the Synchronize Dynamic Cache property for the Lookup transformation.
When you configure the Lookup transformation to synchronize the cache with the lookup source, the task performs a lookup on the dynamic lookup cache. If data does not exist in the dynamic lookup cache, the task performs a lookup on the lookup source. It then completes one of the following tasks:
- If data exists in the lookup source (The data could be updated by a parallel task which updates same object), the task inserts a row in the dynamic lookup cache with the columns from the lookup source. It does not update the cache with the incoming source row.
- If data does not exist in the lookup source, the task inserts the data into the lookup source and inserts the row into the dynamic cache.
In both cases, the NewLookupRow value is 1 and since the record already exists in the target (If the lookup and target are using same object), output from lookup need not be updated in the target.
When the transformation is configured to synchronize dynamic cache, and NewLookupRow is 1, you do not need to pass insert rows to the target.
If the record already exists in the dynamic cache, the update process is as usual. The dynamic cache will be updated if there is a change in data cache and NewLookupRow value will be returned as 2 else the value will be retuned as 0.
5. Dynamic Lookup cache and Target data synchronization
When you use a dynamic lookup cache, the mapping task inserts or updates the lookup cache before it writes to the target table. The lookup cache and target table can become unsynchronized if the task does not write the data to the target which is updated in lookup cache. For example, the target database might reject the data.
Consider the following guidelines to keep the dynamic lookup cache and the target table synchronized
- Use the Router transformation to pass rows to the cached target when the NewLookupRow value equals one or two.
- Use the Router transformation to drop rows when the NewLookupRow value equals zero. Or, output the rows to a different target.
6. Limitations of Dynamic Lookup Cache
- While dynamic lookup is supported with most types of lookup sources, it is not supported with flat file and Salesforce connection types.
- You cannot use a parameterized source, target, or lookup with a Lookup transformation that uses a dynamic cache.
- There are chances that dynamic lookup cache and target data becomes unsynchronized which results in unwanted results.
0 Comments