Lookup Transformation in Informatica
The Lookup transformation is used to look up a source, source qualifier, or target to get the relevant data. You can look up the flat file and relational tables. The Lookup transformation in Informatica works on similar lines as the joiner, with a few differences. For example, lookup does not require two sources. Lookup transformations can be connected and unconnected. They extract the data from the lookup table or file based on the lookup condition.
This is the type passive transformation which allows you to perform the lookup on a relational table. Flat files, synonyms, and views.
When the mapping contains the work up transformation, the integration service queries the lock up data and compares it with lookup input port values.
The lookup transformation is created with following type of ports
Input port (I)
Output port (O)
Look up Ports (L)
Return Port (R)
The lookup transformation supports horizontal merging such as equijoin and nonequijoin.
Informatica Lookup Transformation Uses
Lookup transformation is used to return following tasks. - get a related value - in updating slowly changing dimension
Connected Lookups in Informatica
In Connected lookup transformation, we connect input and output ports to other transformation in the mapping.
For each input row, the Integration Service queries the lookup source or cache based on the lookup ports and the condition in the transformation.
If the transformation is uncached or uses a static cache, the Integration Service returns values from the lookup query.
If the transformation uses a dynamic cache, the Integration Service inserts the row into the cache when it does not find the row in the cache. When the Integration Service finds the row in the cache, it updates the row in the cache or leaves it unchanged. It flags the row as insert, update, or no change.
The Integration Service passes return values from the query to the next transformation. If the transformation uses a dynamic cache, you can pass rows to a Filter or Router transformation to filter new rows to the target.
If there is no match for the lookup condition, the Integration Service returns the default value for all output ports.
Informatica UnConnected Lookups
An unconnected lookup is not part of mapping data flow
It is neither connected to the source nor connected to the target
An unconnected look up transformation can receive the multi input ports but returns single
output port which is designated by return port (R)
An unconnected look up transformation is commonly used when the look is not needed for every record.
An unconnected look up transformation that supports to write expression
The look up function used in conditional statement
[sociallocker id=14714]
Difference between connected and unconnected look up
| Connected |
Unconnected |
- Part of mapping data flow
|
Separate from mapping data flow |
- Return the multiple values
|
Return the one value by checking the return (R)port option for output that provides the return value. |
- Executed for every record passing through the transformation
|
Only executed when the look up function is called |
- More visible shows where the look up values are used
|
Less visible, are the look up is called from an expression with in another T/R |
- Default values are used
|
Default values are ignored |
[/sociallocker]
Note:
The condition is evaluated from each row, but the look up function is only call if the condition evaluates to true.
IFF(ISNULL(ITEM ID),:LKP.ABC(order _id),item Id)
Business purpose:
A source table (or) file may have a percentage of records with incomplete data
The holes in the data can be filled by performing a look up to another table (or) table.
As only a percentage of the rows are affected, it is better to perform the look up on only those rows that need it and not the entire data set.
Procedure:
- Create a source definition with the name emp
- Create a target definition with the name EMP _LKp
- Emp no, ename, job, sal, deptno, dname
- Create the mapping with the name M_unconnected _LKP
- Drop the source and target definitions
- Create the transformation type look up which performs a look up on dept table
- Create the transformation type expression
- From source, qualifier copy the required ports to expression transformation
- Double click on the look up transformation select the ports tab from tool bar click on add a new port
| Port Name |
Data type |
Precision |
Scale |
IOV |
| In- deptno |
Decimal |
2 |
0 |
|
- For a port name a dname select return port(R)
- Select properties tab
|
Transformation Attribute
|
Value |
| Connection Information |
BSR - Reader |
- Select the condition tab from tool bar click on add a new condition look up table column.
| Transformation Attribute |
operator |
T/R port |
| deptno |
= |
IN- Deptno |
- Click Apply and click ok
- Double click on expression transformation select the port tab from tool bar click on add a new ports
| Port Name |
Data type |
Precision |
Scale |
I O V |
Expression |
| DName |
string |
15 |
|
|
:LKP.LKP-Dept(Deptno) |
From express transformation connect the ports to target
Reusable transformations
A reusable transformation is a reusable meta data object created with business logic using single transformation There are 2 different ways to create reusable transformations
- Converting a non-reusable transformation into a reusable transformation
- Using transformation developer tool
Limitation Source qualifier transformation can’t be used as reusable T/R
Procedure
- Converting nonreusable transformation into a reusable T/R select the mapping, drop to the mapping designer workspace
- Double click on the transformation, from the transformation tab
- Select make reusable, click on yes
- Click apply, click ok
Using transformation developer
- From tools menu select transformation developer tool
- From transformation, menu select create
- Select the transformation type sequence generator
- Enter the name click on create and done
- From repository menu click on save
User defined function
A user defined function is a power center object which is created using a set of built in function to meet specific requirements. A user defined function created using power center designer client.
Built – in – function
LTrim Trim LTrim (RTrim (arg1)) Rtrim
Procedure
- From repository navigation window select the user-defined function sub folder.
- From tool menu select user defined functions to click on new
- Enter the name Trim, select the type public
- From arguments click on add new argument
| Name |
Data type |
Precision |
| Arg1 |
String |
10 |
- Click on count editor, develop the following express
- LTRIM(RTRIM(ARG1))
- Click on validate, click ok, click ok
- From repository menu click on save
Note: A user-defined function is defined with the following identifier count _ UDF
Unconnected stored procedure
- An unconnected stored procedure is not part of mapping data flow
- It is neither connected to the source nor connect to the target
- An unconnected stored procedure can receive the multiple but return single output port by default.
- An unconnected stored procedure act as a function that can be set in a transformation that supports to write an expression
- An unconnected stored procedure can be set with the following expression
- :SP. Stored procedure transformation name(:SP.SPTR NAME)
Step1: Create the following stored procedure in the target database
SQL> create procedure annual _ proc (SAL IN NUMBER , A SAL OUT NUMBER)
Is
Begin
Asal := Sal*12;
END;
- Create the source and target definitions
- Empno, ename, job,sal, annual,deptno
- Create a mapping with the name M_ unconnected _proc
- Drop the source and target definition
- Create the transformation type stored procedure and expression
- From the source, qualifier copy the required ports to the expression transformation
- Double click on expression transformation select the ports tab
| Port Name |
Data type |
Precision |
Scale |
I O V |
Value |
| ANUAL |
Decimal |
|
2 |
|
:SP .USP(Sal , proc - Result) |
- Click Apply, click ok
- Double click on unconnected stored procedure transformation
- Select the properties tab
| Transformation Attribute |
Value |
| Connection Information |
Batch _ Trg - Writer |
Lookup Transformation Examples
- Create a source definition with the name emp
- create a target definition with name emp-aggr-LKP deptno, dname, sum(sal)
- create a mapping with the name m-aggr-lookup
- drop the source and target definition
- create the transformation type sorter
- from source qualifier copy the following ports to the sorter transformation (deptno, sal)
- double click on sorted transformation select the ports tab
- for a port name Dep no slece Key
- click apply
- click ok
- create the transformation type aggregator
- from sorter transformation copy the ports the aggregator
- double click on aggregator transformation select he ports tab
- for a port name deptno select group by
- for a port, name salary uncheck output port
- from tool bar click on a new port
| Portname |
Datatype |
Precession |
Scale |
I O V |
Expression |
| sum(sal) |
decimal |
? |
2 |
|
sum(sal) |
- select the properties tab
| Learn Informatica by Tekslate - Fastest growing sector in the industry. Explore Informatica Training and course is aligned with industry needs & developed by industry veterans. Tekslate will turn you into Informatica Expert. |
Transformation Attribute Value
- sorted input
- click apply click ok
- From aggregator, transformation connect the ports the target
- From transformation, menu select create
- select the transformation type lookup
- enter the name LKP-source
- click on create
- Select the source select the table dept click ok from aggregate transformation copy the port dept no to the lookup transformation
- Double click on the lookup transformation select the condition tab
- from tool bar click on odd a new condition
| look up table column |
operator |
T/T port |
| Deptno |
= |
Dept no 1 |
- From lookup transformation connect the dame port to the target
Lookup Transformation Run-time Properties
Lookup Caching Enabled - When It is enabled, the Integration Service queries the lookup source once, caches the values, and looks up values in the cache. When you disable caching, each time a row passes into the transformation, the Integration Service issues a select statement to the lookup source for lookup values. The Integration Service always caches flat-file lookups.
- Lookup Data Cache Size -Maximum size the Integration Service allocates to the data cache in memory. Default is Auto.
- Lookup Index Cache Size -Maximum size the Integration Service allocates to the index in memory. Default is Auto.
- Cache File Name Prefix -Prefix for the cache file. You can specify the cache file name prefix for a persistent lookup cache.
- Pre-build Lookup Cache -Allows the Integration Service to build the lookup cache before the Lookup transformation receives the data.
- Lookup Cache Directory Name - Directory used to build the lookup cache files when you configure the Lookup transformation to cache the lookup source.
- Re-cache from Lookup Source - Rebuilds the lookup cache to synchronize the persistent cache with the lookup table.
Lookup Transformation Advanced Properties
Lookup Cache Persistent
- Indicates whether the Integration Service uses a persistent lookup cache. Case Sensitive String Comparison
- The Integration Service uses case-sensitive string comparisons when performing lookups on string columns when the lookup source is a flatfile.
Null Ordering
- You can choose to sort null values high or low. By default, the Integration Service sorts null values high for flatfile. For relational lookups, null ordering is based on the database default value.
Tracing Level - Sets the amount of detail that appears in the log for this transformation.
Update Else Insert
- Applies to dynamic lookup cache only. The Integration Service updates the row in the cache if the row type entering the Lookup transformation is update, the row exists in the index cache, and the cache data is different than the existing row. The Integration Service inserts the row in the cache if it is new.
Insert Else Update
- Applies to dynamic lookup cache only. The Integration Service inserts the row in the cache if the row type entering the Lookup transformation is insert and it is new. If the row exists in the index cache but the data cache is different than the current row, the Integration Service updates the row in the data cache.
Output Old Value
- on Update The Integration Service outputs the value that existed in the cache before it updated the row. Otherwise, the Integration Service outputs the updated value that it writes in the cache.
Update Dynamic Cache Condition
- Applies to dynamic lookup cache only. An expression that indicates whether to update the dynamic cache. The Integration Service updates the cache when the condition is true and the data exists in the cache. Default is true.
Connection
- Connection to the relational database that contains the relational lookup source.
Sorted Input
- Indicates that input data is presorted by groups.
Datetime Format
- Define a DateTime format and field width.
Thousand Separator
- Value is None. This field is read-only.
Decimal Separator
- Value is a period. This field is read-only.
*The last four properties are applicable only for flatfiles.
