Exercise 7: How to create and use Pipeline Lookup or How to create Active Lookup?

Exercise 7: How to Achieve Target below

(Source)
ins_id,premium_year,premium_Amount
101,2010,NULL
101,2011,4500
101,2012,NULL
102,2010,NULL
102,2011,6000
102,2012,NULL

Note: NULL is just a representation. There is no value over there.

(Target)
ins_id,premium_year,premium_Amount
101 2010 4500
101 2011 4500
101 2012 4500
102 2010 6000
102 2011 6000
102 2012 6000

Let's Chat: I know this exercise we have already solved in Exercise 6 CASE 2. Here I am taking same scenario and will resolved it though Active pipeline lookup.

What is Pipeline Lookup?
A pipeline lookup is used do lookup upon an application source that is not a relational table or flat file.
A Source Qualifier has been used as a lookup table for Pipeline Lookup.
A Source Qualifier which has been used as a lookup table remains abandon in mapping.

What is the Use of Pipeline Lookup?
To improve performance when processing relational or flat file lookup sources, create a pipeline Lookup transformation instead of a relational or flat file Lookup Transformation. 

Pipeline Lookup can be created in Connected or Unconnected mode.


Theory/Analysis:
After analyzing source and target we can easily conclude that the premium amount value of each id will be repeating for each and every row of that id but here value is coming in second row not in first row then it's quite little bit challenging and for this we will create a Active Pipeline Lookup to take all rows of ID and Pass only that value where Premium Amount is NOT NULL. If you want to create Active Lookup then simply one Check box you have to click while creating lookup.

Practically:

Step 1:
Drag and Drop your source twice and Target once.

Step 2:
Lookup

While creating Select Source Qualifier and Select check box Return all Values on Multiple Match (See Picture Below)

Connect Ins_ID, Premium_Year from Source qualifier (Upon which you are not doing Lookup) to Lookup Transformation.
Condition Tab
Ins_Id = Ins_Id 1

Step 3
Filter
Connect Ins_ID, Premium_Year (Column of Source) and Premium_Amount Column of Lookup from Lookup to Filter Transformation.

Filter Condition: NOT ISNULL(Premium_Amount)

Step 4
Connect Your Columns from Filter to Target.

Step 5:
Give the Source connection or Location/File_Name for both source qualifier.
No need to give Source connection or Location/File_Name for Lookup Table.

Note: Your Second Source and Source Qualifier upon which you are doing Pipeline Lookup will be abandon in mapping no need to do anything with them. Your mapping will look like Image below...

BINGO!!!

Note: You can also achieve this by doing Active Lookup directly  upon your source. Approach explained above is just a representation that how to create Pipeline Lookup.

Feel free to post your doubt in comment section.

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