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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: