Lookup Transformation is both connected and unconnected.
Lookup Transformation is both active and passive.
From Informatica PowerCenter Version 9x onwards we can configure the lookup transformation to return all the rows from the lookup table matching the lookup condition.
Choose the LookUp Table from the popup window and select 'Return All Values on Multiple Match'. This property will set the lookup as active lookup transformation.
We cannot use JOINER transformation to combine two data sources and get all the orders form a customer just because of the fact that, we need to use greater than (>) operator to get all the records and only equal (=) operator is supported in JOINER.
So we can create the mapping with the Active LookUp Transformation to over come the limitation.
From the properties tab you can see the 'Lookup Policy on Multiple Match' property is set as 'Use All Values' and it is a read only property and cannot be changed after the transformation is created. This option 'use all values' automatically will be selected when the configure lookup as active.
In normal or passive lookup transformation Lookup policy on multiple match will show 4 options as mention in the image below
By default 'lookup policy on multiple match' selects 'use any value' option, as a result it returns first value.
Lookup Transformation is used to lookup data in a flat file or a relational database. Lookup is used when we have 2 tables and if we want to lookup data on one table based on other table. Both the table should have common columns and based on that we give condition in lookup transformation where lookup table 1 = lookup table 2. Lookup condition should be on the primary key column.
Lets say there is an EMP1 table in source and TGT_EMP1_LOOKUP table in target. If we perform lookup on DEPT table to get the department name and location with our join condition(deptno) then only the matching rows will be loaded into the target TGT_EMP1_LOOKUP.
Connected Lookup :-
Unconnected Lookup :-
- In unconnected lookup we dont give direct connection, we create a port in_deptno and select input port.
- We need to select an return port to display the result.
- In Expression transformation, we create a Dname port for output and from the functions select lookup, inorder to select :lkp. expression.
- Add Deptno to give input to :Lkp. expression.
- We cannot put more than 1 return ports in unconnected lookup
- Connected lkp returns multiple columns from each and every row where as Unconnected lkp returns only one column from each row
Steps to create the lookup transformation :-
- Login to the Power center Designer. Open either Transformation Developer tab or Mapping Designer tab.
- Click on the Transformation in the toolbar, and then click on Create.
- Select the lookup transformation and enter a name for the transformation. Click Create
- Now you will get a "Select Lookup Table" dialog box for selecting the lookup source, choosing active or passive option.
- You can choose one of the below option to import the lookup source definition
Source definition
Target definition
Source qualifier
Import a relational source
- In the same dialog box, you have an option to choose active or passive lookup transformation. You can see this option in red circle in the above image. To make the lookup transformation as active, check the option "Return All Values on Multiple Match". Do not check this when creating a passive lookup transformation. If you have created an active lookup transformation, the value of the property "Lookup policy on multiple match" will be "Use All Values". You cannot change an active lookup transformation back to a passive lookup transformation
- Click OK or Click Skip if you want to manually add ports to lookup transformation
- Click OK or Click Skip if you want to manually add ports to lookup transformation
- For unconnected lookup transformation, create a return port for the value you want to return from the lookup.
- Go to the properties and configure the lookup transformation properties
- For dynamic lookup transformation, you have to associate an input port, output port or sequence Id with each lookup port.
- Go the condition tab and add the lookup condition.
Based on cache Lookup is categorized into 3 types :-
- Static cache lookup
- Dynamic cache lookup
- Persistent cache lookup
Static cache :-
---------------
Integration service creates Static Cache by default while creating lookup cache. In Static Cache the Integration Service does not update the cache while it processes the ideas .In Static Cache when the Lookup condition is true it return value from lookup table else returns Null values .In Static Cache the important thing is that you cannot insert or update the cache.
Here once when the cache is created, integration service always queries the cache instead of lookup table. Here integration service does not update the cache while it process transformation. Data in the cache will stay the same for the entire session.
Dynamic cache :-
-------------------
In Dynamic Cache we can insert or update rows in the cache when we pass the rows. The Integration Service dynamically inserts or updates data in the lookup cache and passes the data to the target. The dynamic cache is synchronized with the target.
Here integration service dynamically inserts or updates data in the lookup cache and pass the data to the target.
After selecting the 'dynamic lookup cache' option in properties tab of lookup transformation, additionally we need to check two more options 'synchronize dynamic cache and output old values on update' option.
We should also select associated ports for lookup updates.
You must associate each lookup port with an input source port or sequence ID, or an expression.
For every insert integration service assigns new lookup row as 1
For every update integration service assigns new lookup row as 2
For every reject integration service assigns new lookup row as 0
Persistent cache :-
--------------------
Persistent cache remains the same, if we want to use a source table again and again and that source doesn't change then we will follow this logic.
- Select persistent option from lookup transformation.
- To reuse cache files across the sessions, specify a cache file name prefix
- With this persistent cache, cache can be used in another session, when we have mutiple sessions.
- So as we use persistent cache, First session will create new cache files.
- Later on session session will reuse the existing cache files of first session.
- Session log shows as refreshing cache files. But for it we need to use same cache which is used for first session and set two option, that is lookup persistent option and cache file name prefix
We can refresh Persistent Cache by
- 'Re-cache' from source is the option checked from properties tab.
- You can save the lookup cache files and reuse them the next time.
Non-Persistent Lookup :-
-------------------------
- By default lookup is non-persistent.
- Lookup deletes the cache files as soon as the session finished executing when we don't select persistent option in lookup transformation.- If persistent option is not select, then lookup is considered as non-persistent lookup, here it delete all the lookup cache files.
Share Lookup cache :-
-----------------------------------------
Share cache is furture divided based on whether its name or unnamed.
- unnamed cache cannot be used in muliple mapping
- name cache can be used in multiple mappings
Lookup Transformation is both active and passive.
From Informatica PowerCenter Version 9x onwards we can configure the lookup transformation to return all the rows from the lookup table matching the lookup condition.
Choose the LookUp Table from the popup window and select 'Return All Values on Multiple Match'. This property will set the lookup as active lookup transformation.
We cannot use JOINER transformation to combine two data sources and get all the orders form a customer just because of the fact that, we need to use greater than (>) operator to get all the records and only equal (=) operator is supported in JOINER.
So we can create the mapping with the Active LookUp Transformation to over come the limitation.
From the properties tab you can see the 'Lookup Policy on Multiple Match' property is set as 'Use All Values' and it is a read only property and cannot be changed after the transformation is created. This option 'use all values' automatically will be selected when the configure lookup as active.
In normal or passive lookup transformation Lookup policy on multiple match will show 4 options as mention in the image below
By default 'lookup policy on multiple match' selects 'use any value' option, as a result it returns first value.
Lookup Transformation is used to lookup data in a flat file or a relational database. Lookup is used when we have 2 tables and if we want to lookup data on one table based on other table. Both the table should have common columns and based on that we give condition in lookup transformation where lookup table 1 = lookup table 2. Lookup condition should be on the primary key column.
Lets say there is an EMP1 table in source and TGT_EMP1_LOOKUP table in target. If we perform lookup on DEPT table to get the department name and location with our join condition(deptno) then only the matching rows will be loaded into the target TGT_EMP1_LOOKUP.
Connected Lookup :-
Unconnected Lookup :-
- We need to select an return port to display the result.
- In Expression transformation, we create a Dname port for output and from the functions select lookup, inorder to select :lkp. expression.
- Add Deptno to give input to :Lkp. expression.
- We cannot put more than 1 return ports in unconnected lookup
Steps to create the lookup transformation :-
- Login to the Power center Designer. Open either Transformation Developer tab or Mapping Designer tab.
- Click on the Transformation in the toolbar, and then click on Create.
- Select the lookup transformation and enter a name for the transformation. Click Create
- Now you will get a "Select Lookup Table" dialog box for selecting the lookup source, choosing active or passive option.
- You can choose one of the below option to import the lookup source definition
Source definition
Target definition
Source qualifier
Import a relational source
- In the same dialog box, you have an option to choose active or passive lookup transformation. You can see this option in red circle in the above image. To make the lookup transformation as active, check the option "Return All Values on Multiple Match". Do not check this when creating a passive lookup transformation. If you have created an active lookup transformation, the value of the property "Lookup policy on multiple match" will be "Use All Values". You cannot change an active lookup transformation back to a passive lookup transformation
- Click OK or Click Skip if you want to manually add ports to lookup transformation
- Click OK or Click Skip if you want to manually add ports to lookup transformation
- For unconnected lookup transformation, create a return port for the value you want to return from the lookup.
- Go to the properties and configure the lookup transformation properties
- For dynamic lookup transformation, you have to associate an input port, output port or sequence Id with each lookup port.
- Go the condition tab and add the lookup condition.
Based on cache Lookup is categorized into 3 types :-
- Static cache lookup
- Dynamic cache lookup
- Persistent cache lookup
Static cache :-
---------------
Integration service creates Static Cache by default while creating lookup cache. In Static Cache the Integration Service does not update the cache while it processes the ideas .In Static Cache when the Lookup condition is true it return value from lookup table else returns Null values .In Static Cache the important thing is that you cannot insert or update the cache.
Here once when the cache is created, integration service always queries the cache instead of lookup table. Here integration service does not update the cache while it process transformation. Data in the cache will stay the same for the entire session.
Dynamic cache :-
-------------------
In Dynamic Cache we can insert or update rows in the cache when we pass the rows. The Integration Service dynamically inserts or updates data in the lookup cache and passes the data to the target. The dynamic cache is synchronized with the target.
Here integration service dynamically inserts or updates data in the lookup cache and pass the data to the target.
After selecting the 'dynamic lookup cache' option in properties tab of lookup transformation, additionally we need to check two more options 'synchronize dynamic cache and output old values on update' option.
We should also select associated ports for lookup updates.
You must associate each lookup port with an input source port or sequence ID, or an expression.
For every insert integration service assigns new lookup row as 1
For every update integration service assigns new lookup row as 2
For every reject integration service assigns new lookup row as 0
Persistent cache :-
--------------------
Persistent cache remains the same, if we want to use a source table again and again and that source doesn't change then we will follow this logic.
- Select persistent option from lookup transformation.
- To reuse cache files across the sessions, specify a cache file name prefix
- So as we use persistent cache, First session will create new cache files.
- Later on session session will reuse the existing cache files of first session.
- Session log shows as refreshing cache files. But for it we need to use same cache which is used for first session and set two option, that is lookup persistent option and cache file name prefix
We can refresh Persistent Cache by
- 'Re-cache' from source is the option checked from properties tab.
- You can save the lookup cache files and reuse them the next time.
-------------------------
- By default lookup is non-persistent.
- Lookup deletes the cache files as soon as the session finished executing when we don't select persistent option in lookup transformation.- If persistent option is not select, then lookup is considered as non-persistent lookup, here it delete all the lookup cache files.
Share Lookup cache :-
-----------------------------------------
Share cache is furture divided based on whether its name or unnamed.
- unnamed cache cannot be used in muliple mapping
- name cache can be used in multiple mappings
No comments:
Post a Comment