What is lookup? Whoever is familiar with Excel’s VLOOKUP might understand what this is about, but, anyway, let’s check what the official documentation says about it:
“Use a Lookup transformation in a mapping to look up data in a flat file, relational table, view, or synonym. You can import a lookup definition from any flat file or relational database to which both the PowerCenter Client and Integration Service can connect. You can also create a lookup definition from a source qualifier. You can use multiple Lookup transformations in a mapping. The Lookup transformation can be an active or passive transformation. You can configure a connected or unconnected Lookup transformation.”
For certification purposes I guess it is important to understand that a lookup transformation can be either active or passive and either connected or unconnected (more on those terms in a future post).
A simple lookup
Here is how to perform a simple lookup. In this example I have a SQL source and a Flat file source. I will use my flat file as lookup so I can gather some data from that flat and insert into a target table in my SQL database.
Here’s what I got on my database:
customerBase table, wich hold some of my customer’s info.
Here what I got on that flat file I will use as lookup:
Some users and an Income attribute.
What I want? To input that income attribute inside a new target table inside my sql database.
Here’s how I did it:
First, create a new mapping and drag your SQL source database.
Then, create a target based on your source qualifier.
Now add a new transformation on your mapping, a lookup one. It will ask for a name and then for a table to lookup for. So I went source > flat file > customerSalary > OK.
This is what I got so far:
But I realize that I will need a new column on my target. So I go to target > select my target table > edit it > add a new column named Income of the same data type and data lenght. Once it’s done I use Powercenter to create this target table inside my SQL database.
So here we are:
Now I drag CustomerID from my Source Qualifier to my Lookup transformation.
I then edit the lookup transformation > condition > create a new one > customerID = CustomerID01. This way I’m telling Informatica Powercenter that those two columns is how my data is related.
Now, perform the attribute to attribute mapping. This is the result:
Create a workflow and run it. Here’s the final result:
Unconnected Lookup
The lookup we did above is called connected lookup, because there are lines connecting the lookup transformation and other objects, like target and source.
Here, in the unconnected lookup we have a different way of doing the same thing. So, to perform an unconnected lookup you must have this setup:
Create a new mapping. Drag your source and add your lookup transformation. I will also drag my target, since it is the same target we created in the previous topic.
In your lookup transformation, add a new field, here it’s named CustomerID_SRC. Also, add a condition to connect it to your lookup id. Make sure the field you want to lookup is set as RETURN in your lookup transformation.
Note: you can only have one attribute looked up at a time per unconnected lookup transformation, meaning that, if you need another field other than Income you will need to create a new lookup transformation and perform all steps explained here.
Now add another transformation. For this example I’m using a expression transformation.
Add a new field on that expression transformation, Income. Set data type properly.
Uncheck Input port and adjust expression to :LKP.T_LOOKUP(CustomerID)
Now, drag your fields to target. And it’s done. The target result in the database is exactly the same.
Connected and Unconnected Lookups
The following table lists the differences between connected and unconnected lookups:
Lookup Policy On Multiple Match
What happens when you try to perform a lookup but your lookup table, or lookup columns, have multiple matches? Usually what happen is that your lookup might return the first match, return an error or something like that.
Here are the list of option your normal lookup transformation have: use first, use last, report error, use any value.
So far we have been using this lookup table:
But, what if we change this to this:
And we change our lookup transformation to Use Last Value:
Here’s the result:
As you can see, we have 91 rows, and our first customer, ALFKI, shows INCOME of 2808, which is the last value on that lookup table we have. Notice here that sort order is important to determine first and last values.
Return All Values on Multiple Match
What if we don’t want only one row, but instead we want all the rows from that customer? To do that, here is what we need:
- create a new mapping, just like the previous one
- we can’t use the old lookup transformation, we need a new one
This option is only available when we create the lookup transformation the first time:
Once you’ve checked that checkbox we have to do some steps in a different way, and this is because our lookup transformation is now a active one (not passive like before – more on this later).
As shown in the image above, we still have the same source, source qualifier and target, however our lookup changed. Now we dragged all fields that we want to persist in our target to the lookup transformation itself.
Also, of course, we still need our proper condition
Here’s the Ports configuration. It’s not really different from what we’ve seen so far, the main point is that all attributes must now flow trough our lookup transformation.
Here is the result for our multi valued lookup transformation.
Active vs Passive Transformations
To summarize very quickly:
- Active means that a transformation that change the row number.
- Passive means do not change row number or data types, etc.
I will write more in detail soon about this active vs passive and connected vs unconnected, and there’s also native vs non-native.
Pedro Carvalho 
2 thoughts on “Informatica Powercenter – Lookup Transformation”