Informatica – Parent child hierarchy implementation (Unconnected Lookup)
January 21, 2012
This mapping looks to be simple but a lot of learning for a newbie like me in informatica. Let me share this interesting mapping in this post.
The transformations needed are,
The source qualifier, two unconnected lookups, expression transformation and target(say a flat file)
Key points:
Here we take a Sequence number ID as SID value which acts as a surrogate key to refer to every row transaction.
Considering this, we make use of two lookup transformation, one to make reference to the parent SID value with an input of Customer SID value and the second lookup for a purpose to make a reference on the respective Customer ID value passing on the Customer SID.
The expression transformation ports(click on image for zoom),
Also to remember a key step on the expression transformation about the Variable settings, which depends on the number of levels or depth of the parent child relationship are to be required.
As example here we go for three levels of parent child hierarchy. So we define the expression on the Variables as “LKP.LKP_get_parentsid(Cust_SID)”, passing the Customer SID value and getting the Parent SID from the lookup.
The lookup ports (click on image for zoom),
The output ports similarly will lookup for the Customer ID – ”LKP.LKP_get_parent_id (v_Parent_SID)” passing the repective variable ports.
