Pages

Saturday, March 16, 2013

What is Active Lookup Transformation || How to configure a Lookup as Active?

Informatica 9x allows us to configure Lookup transformation to return multiple rows. So now we can retrieve multiple rows from a lookup table thus making Lookup transformation an Active transformation type.

To use this option, while creating the transformation, we must configure the Lookup transformation property "Lookup Policy on Multiple Match" to Use All Values. Once created we cannot change the mode between passive and active. When ever theLookup policy on multiple match attribute is set to Use All Values. The property becomes read-only.

Implementing a Lookup As Active:


Scenario: Suppose we have customer order data in a relational table. Each customer has multiple orders in the table. We can configure the Lookup transformation to return all the orders placed by a customer.
Now check the below simple mapping where we want to return all employees in the departments.
Go to Transformation and click Create. Select Transformation Type as Lookup and enter a name for the transformation.
Create LookUp Transformation
Next check the option Return All Values on Multiple Match.
Return All Values on Multiple Match
Here our source is the DEPT table and the EMP table is used a lookup. The lookup condition is based on the department number.
Mapping to implement Active Lookup
Properties Tab of Active Lookup Transformation
Ports Tab of Lookup Transformation
Basically we try to achive the result as the below sql select:-
SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, EMP.ENAME, EMP.SAL 
FROM DEPT LEFT OUTER JOIN EMP ON DEPT.DEPTNO = EMP.DEPTNO 
Active Lookup Transformation Restrictions:
  1. We cannot return multiple rows from an unconnected Lookup transformation
  2. We cannot enable dynamic cache for a Active Lookup transformation.
  3. Active Lookup Transformation that returns multiple rows cannot share a cache with a similar Passive Lookup Transformation that returns one matching row for each input row.


No comments:

Post a Comment