Why we use lookup transformations?

Lookup Transformations can access data from relational tables that are not sources in mapping. With Lookup transformation, we can accomplish the following tasks:

Get a related value-Get the Employee Name from Employee table based on the Employee IDPerform Calculation.

Update slowly changing dimension tables - We can use unconnected lookup transformation to determine whether the records already exist in the target or not.

Editorial / Best Answer

Answered by: prodyot Sarkar

  • Jul 31st, 2007


The following reasons for using lookups.....

1)We use Lookup transformations that query the largest amounts of data to improve overall performance. By doing that we can reduce the number of lookups on the same table.

2)If a mapping contains Lookup transformations, we will enable lookup caching if this option is not enabled .
We will use a persistent cache to improve performance of the lookup whenever possible.
We will explore the possibility of using concurrent caches to improve session performance.
We will use the Lookup SQL Override option to add a WHERE clause to the default SQL statement if it is not defined
We will add ORDER BY clause in lookup SQL statement if there is no order by defined.
We will use SQL override to suppress the default ORDER BY statement and enter an override ORDER BY with fewer columns. Indexing the Lookup Table
We can improve performance for the following types of lookups:
For cached lookups, we will index the lookup table using the columns in the lookup ORDER BY statement.
For Un-cached lookups, we will Index the lookup table using the columns in the lookup where condition.

3)In some cases we use lookup instead of Joiner as lookup is faster than joiner in some cases when lookup contains the master data only.

4)This lookup helps in terms of performance tuning of the mappings also.

Showing Answers 1 - 3 of 3 Answers

sithusithu

  • Jan 19th, 2006
 

Nice Question, If we don't have a look our datawarehouse will be have more unwanted duplicates 

Use a Lookup transformation in your mapping to look up data in a relational table, view, or synonym. Import a lookup definition from any relational database to which both the Informatica Client and Server can connect. You can use multiple Lookup transformations in a mapping

Cheers,

Sithu

  Was this answer useful?  Yes

Amaresh Das

  • Apr 12th, 2007
 

Lookup Transformations used to search data from relational tables/FLAT Files that are not used in mapping.

Types of Lookup:
           1. Connected Lookup
           2. UnConnected Lookup

rajumadarapu

  • Jul 2nd, 2007
 

The main use of lookup is to get a related value either from a relational sources or flat files

prodyot Sarkar

  • Jul 31st, 2007
 

The following reasons for using lookups.....


1)We use Lookup transformations that query the largest amounts of data to
improve overall performance. By doing that we can reduce the number of lookups
on the same table.


2)If a mapping contains Lookup transformations, we will enable lookup caching
if this option is not enabled .
We will use a persistent cache to improve performance of the lookup whenever
possible.
We will explore the possibility of using concurrent caches to improve session
performance.
We will use the Lookup SQL Override option to add a WHERE clause to the default
SQL statement if it is not defined
We will add ORDER BY clause in lookup SQL statement if there is no order by
defined.
We will use SQL override to suppress the default ORDER BY statement and enter an
override ORDER BY with fewer columns. Indexing the Lookup Table
We can improve performance for the following types of lookups:
For cached lookups, we will index the lookup table using the columns in the
lookup ORDER BY statement.
For Un-cached lookups, we will Index the lookup table using the columns in the
lookup where condition.


3)In some cases we use lookup instead of Joiner as lookup is faster than
joiner in some cases when lookup contains the master data only.


4)This lookup helps in terms of performance tuning of the mappings also.

siraj.khan

  • Aug 3rd, 2007
 

Look up Transformation is like a set of Reference for the traget table.For example suppose you are travelling by an auto ricksha..In the morning you notice that the auto driver showing you some card and saying that today onwards there is a hike in petrol.so, you have to pay more. So, the card which he is showing is a set of reference for there costumer..In the same way the lookup transformation works.
These are of 2 types :

a) Connected Lookup
b) Un-connected lookup

Connected lookup is connected in a single pipeline from a source to a target where as Un Connected Lookup is isolated with in the mapping and is called with the help of a Expression Transformation.

chandrarekha

  • Sep 6th, 2007
 

Look up tranformations are used to
Get a related value
Updating slowly changing dimension
Caluculating expressions

  Was this answer useful?  Yes

Thats a good question, suppose say you have 40 transformations in your mapping which invole complex agg, functions,  so in middle of the mapping there is a requirement to get the data from some  x table, what will you do now? Will you get this as a one of the source? if so think about he dataflow.. data has to travel thru your complex transformations... which will gives a degraded performance. Instead of it informatica guys come up with a look up concept, so that you can look for the data from any table/flat file in middle of the mapping or where ever it is.

Satya

  • Aug 9th, 2014
 

Use lookup transformation in a mapping to
--get related values
--perform complex calculation
--Handle slowly changing dimension.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions