Advertisements

Informatica Unconnected Transformations

There are 3 Informatica transformations (External Procedure, Lookup, and Stored Procedure) that can be unconnected in a valid mapping.  An unconnected transformation is one that is not connected to the pipeline – that is, it is not connected to any other transformation via a link.  Unconnected transformations, especially unconnected lookup transformations, are widely in OBIA mappings.

Unconnected transformations are defined in a mapping and are called / invoked from another transformation in the mapping.  They can be called from any transformation that supports the transformation expression language.

Below is a list of the 3 Informatica Unconnected Transformations and how they are called or invoked from another transformation:

 Transformation  How its called / invoked
 External Procedure  :EXT.external_procedure_transformation(argument1, argument2, …)
 Lookup  :LKP.lookup_transformation(argument1, argument2, …)
 Stored Procedure  :SP.stored_procedure_transformation( argument1, argument2, [, PROC_RESULT])

Any mapping with an unconnected transformation must also include another transformation that calls the unconnected transformation.

All of the 3 Unconnected Transformations can also be used in connected mode.  However, when you want to execute the transformation logic for a subset of the rows passing through the mapping (especially when it’s a small percentage of the rows), you will want to consider using the transformations in unconnected mode and with conditional logic.  The conditional logic will cause the unconnected transformation to execute only when necessary, and therefore, may improve perfiormance.

The example below shows a call to an unconnected lookup transformation (lkp_item_id) that is invoked only when the desired item_id value is NULL.  In a case where only a small percentage of the rows have an item_id that is NULL, then the lookup would only be invoked for a small number of rows, as opposed to all rows if the transformation was connected.

                       IIF  ( ISNULL(item_id),   :LKP.lkp_item_id ( item_name ),   item_id )

As a side note, the Sequence Generator transformation can also be invoked from a function call, but it is a connected transformation.

 Transformation  How its called / invoked
 Sequence Generator  :SEQ.sequence_generator_transformation.CURRVAL
Advertisements

One Response to Informatica Unconnected Transformations

  1. Verissa Beatty says:

    Do you have an example for using the Sequence Generator command, :SEQ.sequence_generator_transformation.NEXTVAL in a function and a mapping?

    Is the function a User Defined Function with in Informatica?
    Thanks for you help in advance.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: