'Lookup in Informatica PowerCenter

Why do we use Lookup Transformation in Fact Table construction in Informatica PowerCenter? And what exactly is the difference between Lookup and Joiner Transformation ?

informatica-powercenter


Solution 1:[1]

The differences are many, but the first thing to know is that 80% of the solutions where you chose one of them, you may as well have chosen the other. A non-complete list of differences:

  • lookup can only do 'left-outer' joins
    • VS a joiner can do both inner, left-, right- and full-outer joins
  • lookup can return the first,last,any,all rows that matches
    • VS a joiner can only retur all matches
  • a lookup can match on =, >=, <=
    • VS a joiner can only match on =
  • a lookup can only be 'blocking' during session initialisation (except in uncached mode - see below)
    • VS a joiner can do a 'sorted merge' and output rows before the last row from one or the other source has arrived
  • a lookup cannot initialize its cache based on other power center transformations- it has to read directly from the result of a piece of sql (of flat file source)
    • VS a joiner that can join two streams of data that does pretty much any transformation, and from any valid data source (MQ, web service, whatever)
  • a lookup cache can be altered during execution via the 'dynamic lookup cache' parameters
    • VS a joiner that always has a 'static' cache
  • a lookup can run 'uncached' and issue 1 sql select per incoming row instead of the default 'caching' behavior (warning: uncached is often quite slow)
    • VS a joiner that always fills cache before receiving first row(perhaps only for one 'group' in sorted merge mode)

Some of these features are obscure, I know, but good to know of.

Solution 2:[2]

Below are the differences in lookup and joiner transformations in informatica: 1) Joiner supports only = operator lookup supports<=,>=,=,!= operators 2)Joiner doesn't have provision of caches. but different caches(like persistent, dynamic) are possible in Lookup transformation which improves performance 3) You can write lookup override in Lookup transformation, however joiner doesn't have facility to write lookup override. 4) Joiner supports inner join, left,right,full outer join however lookup supports left outer join Hope this helps!

Solution 3:[3]

You will have N number of dimension tables and we use LKP transformation to check whether the dimension key exists in Dim table/to get surrogate keys before loading into Fact.

It is very expensive to do outer join on all the Dim tables which will result in huge data movement. Hence, Informatica provides us LKP transformation with different caching mechanisms which will allow us to minimise the number of database transactions.

Solution 4:[4]

The transformations that exist in powercenter can be replaced by queries in overrides, but their mapping would not be agnostic, that is, if you configure your workflow connections to run in a different database, there is little or no chance of the flow running smoothly. By using Informatica Powercenter transformations you can "guarantee" that your flow will run on any database.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Saurabh Deshmukh
Solution 3 Aswin
Solution 4 joaoferr

Related Questions