Sunday 1 March 2015

Scenario 26: How to create and use Active Lookup Transformation?

Scenario 26: How to create and use Active Lookup Transformation?

What is Active Lookup Transformation?
When lookup transformation return all the matching records from the Lookup table then it's called as an Active transformation.
This feature of Lookup has been introduced from 9.X version of Informatica Power Center tool.

Business Requirement: We have two table Product and currency now in the target table we have to populate all products with price after taking the all currency rate from currency table.

Note: There is no common column in both tables.

Source.


Source: Two tables

Product Table
Prod_ID
Prod_Name
Price_USD
2001
Laptop
300
2002
Desktop
200
2003
Mouse
20

 Currency Table
ID
Description
Rate
1
SGR
55
2
INR
62
3
EURO
42

Target Table: 
Prod_ID
Prod_Name
Price_USD
Price_SGR
Price_INR
Price_EURO
2001
Laptop
300
16500
18600
12600
2002
Desktop
200
11000
12400
8400
2003
Mouse
20
1100
1240
840
 
Let's Chat: I know! I know you will be saying hey we can achieve this though joiner transformation easily though we don't have column in both tables. A BIG THANK YOU... You have visited my previous posts and learned it well how to handle.
We all know there will be several way to accomplish business requirement. :-) Here I am opting Active Lookup approach to explain the use. Here we go....

Practically

Step 1: Import Product table. (Import your target table as well.)

Step 2: Expression.
 Create one output port. (This port will be used in condition tab of Lookup)
 SRC_DUMMY_ID (Output Port) = 1

Step 3: Lookup 
a) Create Lookup transformation.... Wait. while creating don't forget to check the option which is shown in Picture below.



b) Add a port manually
   DUMMY_ID (Decimal) Output & Lookup port should be checked.

c) Set condition Tab.

DUMMY_ID = SRC_DUMMY_ID

d) Override the SQL in properties tab.

SELECT CURRENCY.DESCRIPTION as DESCRIPTION, CURRENCY.RATE as RATE, 1 as DUMMY_ID, CURRENCY.ID as ID FROM CURRENCY

Step 4: Aggregator
Connect DESCRPTION, RATE and DUMMY_ID to Aggregator
(All port should be checked as GROUP port)

Step 5: Joiner
a) Create Joiner and Selected Sorted Input in Properties tab.
b) Now Drop all ports from Aggregator and Expression (Step2)
c) Select  Ports from Lookup as Master Port in Port Tab.
d) Condition
DUMMY_ID=SRC_DUMMY_ID

Step 6: Expression
Take Prod_ID, Prod_Name, Price, Description, Rate Port from Joiner.
Create three output port for calculating price.
INR_PRICE (Output Port)=IIF(DESCRIPTION='INR', USD_PRICE*RATE)
SGR_PRICE (Output Port)=IIF(DESCRIPTION='SGR', USD_PRICE*RATE)
EURO_PRICE (Output Port)= IIF(DESCRIPTION='EURO', USD_PRICE*RATE)

Step 7: Aggregator
Connect all ports from expression.
Select PROD_ID port as GROUP port.
Create three output port to de-normalize the data.
O_INR_PRICE (Output Port)=MAX(INR_PRICE)
O_SGR_PRICE (Output Port)=MAX(SGR_PRICE)
O_EURO_PRICE (Output Port)= MAX(EURO_PRICE)

Step 8: Connect to your target.

BINGO!!!



Feel free to post your doubt in comment section or though contact form which you can find below the page.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com