ACTIVE LookUp, To Unlock the Limitations of JOINER Transformation

Johnson Cyriac Nov 8, 2012
|

Informatica user defined error handling
Joiner Transformation can be used to achieve the functionality of SQL join Operation including  full outer join. Additionally we can use Joiner to join data from heterogeneous data sources. But it is limited with the operators, which can be used in the join condition, it can use only 'equal to' operator in the join condition. In this article lets see how we can unlock this limitation using Informatica PowerCenter Active LookUp transformation.

To overcome this disability we will be using Active LookUp Transformation, which is available from Informatica PowerCenter Version 9x.

What is Active LookUp

From Informatica PowerCenter Version 9x onwards we can configure the lookup transformation to return all the rows from the lookup table matching the lookup condition. This Lookup transformation becomes an active transformation. For the Active LookUp 'Lookup Policy on Multiple Match' property will be 'Use All Values'. This property becomes read-only and cannot be changed after the transformation is created.

How to Configure Active LookUp

Just like any other transformation start creating the transformation.
Informatica powerCenter Active Lookup
Choose the LookUp Table from the popup window and select 'Return All Values on Multiple Match'. This property will set the lookup as active lookup transformation.
Informatica powerCenter Active Lookup
From the properties tab you can see the 'Lookup Policy on Multiple Match' property is set as 'Use All Values' and it is a read only property and cannot be changed after the transformation is created.
Informatica powerCenter Active Lookup

Unlock the JOINER Transformation Limitations

Lets consider a simple scenario where you are given with flat file with a list of customers and you need to pull all the orders from the relational table by a customer from a give date.

We can not use JOINER transformation to combine these two data sources and get all the orders form a customer just because of the fact that, we need to use greater than (>) operator to get all the records and only equal (=) operator is supported in JOINER.

So we can create the mapping with the Active LookUp Transformation to over come the limitation.

After the source definition is pulled into the designer, create the lookup transformation as shown below. Select 'Return All Values on Multiple Match'. to set the Active LookUp. 

Informatica powerCenter Active Lookup
From the properties tab you can see the 'Lookup Policy on Multiple Match' property is set as 'Use All Values' and it is a read only property and cannot be changed after the transformation is created.
Informatica powerCenter Active Lookup
Give the lookup condition to get all customer orders for the date DATE as in the below image. 
Note : JOINER transformation does not allow operators other than equal (=).  
Informatica powerCenter Active Lookup
After the active LookUp is configured, map all the columns to the target table. Below is the structure of the completed mapping.
Informatica powerCenter Active Lookup
Hope you enjoy this tutorial, Please let us know your comments and feedback.




About US Contact US Advertise Guest Post Terms and Conditions Privacy Policy Disclaimer

© 2012-2013 Data Intelligence Solution, All Rights Reserved
The contents in this site is copyrighted to Data intelligence Solution and may not be reproduced on other websites.