What is lookup transformation in informatica

Informatica Tips 

Informatica General Notes 

What is lookup transformation in informatica ?

HOW TO Use a Joiner transformation instead of Lookup Transformation

HOW TO De-normalize data/pivot rows into columns



What is lookup transformation in informatica?


Lookup is a transformation to look up the values from a relational table/view or a flat file. The developer defines the lookup match criteria. Lookup cannot be used dynamicly with un-connected. Some of the categories lookup has include conn & un-conn, static & dynamic. Different caches can also be used with lookup like static, dynamic, persistent, and shared. Each of these has its own identification. For more details, the book "Informatica Help" can be useful.

Hope you are aware with the basics of Informatica. Now proceeding through lookup transformation.

Lookup transformation is Passive and it can be both Connected and UnConnected as well. It is used to look up data in a relational table, view, or synonym. Lookup definition can be imported either from source or from target tables.

For example, if we want to retrieve all the sales of a product with an ID 10 and assume that the sales data resides in another table called 'Sales'. Here instead of using the sales table as one more source, use Lookup transformation to lookup the data for the product, with ID 10 in sales table.

Difference between Connected and UnConnected Lookup Transformation: 1. Connected lookup receives input values directly from mapping pipeline whereas UnConnected lookup receives values from: LKP expression from another transformation.

2. Connected lookup returns multiple columns from the same row whereas UnConnected lookup has one return port and returns one column from each row.

3. Connected lookup supports user-defined default values whereas UnConnected lookup does not support user defined values.



Select dname from dept,emp where: emp.deptno=dept.deptno


Connected LKPs

1.Connected LKP trasformation is one which is connected to Pipe line.

2.Connected LKP trasformation will process each and every Row.

3.If you want to Use Dynamic LKP cache, use the connected LKP transformation.

4.If the LKP condition is not matched the LKP transformation will return the Default Value.

5.it cannot be called

6.it returns multiple values.

7.it can use static or dynamic cache


Unconnected LKPs

1.Unconnected LKP trasformation is one which is not connected to the Pipe line.

2.It should be called either from expression or Update Stragey.

3.It will not process each and evry row. It will return the values based expression Condition.

4.If no match found for the LKP condition, the LKP transformation will return Null Values.

5.it is a reusable trnsformation. The same LKP trnans can be called multiple times in same mapping

6.it will return only one value.

7.it can use only static cache


Dynamic Lookups

Dynamic Lookups are used for implementing Slowly Changing dimensions. The ability to provide dynamic caching gives Informatica a definetive edge over other vendor products. In a Dynamic Lookup, everytime a new record is found (based on the lookup condition) the Lookup Cache is appended with that record.

Founded in 1993, Informatica's headquarters are located in Redwood City, California. Informatica is the market leader (source IDC) in the fast emerging data integration space. Informatica addresses enterprise and large organization data migration (platform modernization), data synchronization (software as a service, BPO and ITO outside the firewall), data consolidation (merging applications following M&A), meta data management (supplier, product and customer hubs) and data warehousing (ETL for business intelligence).


Extract, Transform and Load

The Informatica ETL product is called PowerCenter. It was one of the earliest ETL tools on the market and uses a user-friendly GUI designer interface to build jobs that run on one or more transformation servers. In PowerCenter 8 the product has pushdown optimization that executes transformation steps on a database server. This gives the an ETLT capability - extract, transform, load and transform.

PowerCenter 8 comes with options for expanding the capabilities of the ETL tool, these are a selection that impact how ETL jobs execute:

·Partitioning Option: for partitioning data to process it in parallel across multiple CPUs or servers.

·Real Time Option: for turning PowerCenter jobs into real time services.

·Enterprise Grid Option: for automating the deployment and load balancing of PowerCenter jobs on a grid.