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.
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
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.
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.
