Thursday, April 12, 2012

Dynamic Expression evaluation in Informatica

Informatica is one of the leading Data Integration - ETL tool in the market for several years now.
One of the objective all the big ETL tool companies strive to accomplish is the need for data transfer speed with complex business rule transformations. Informatica provides various transformations in a typical source DB to target DB mapping. One of the recent advances included making expression evaluation dynamic. i.e expression string itself can now be placed as a parameter in the parameter file.

This feature provides avenues for materializing several ideas for dynamic rule changing. Let us take an example.

In this simple example Name is output port with following expression  : Name = First Name || Last Name

In normal situation, If the expression string needs to be changed so that target Name field to contain only initial character of Last Name, then mapping needs to be modified, tested and moved to production. Now if there was a mapping parameter created like $$Name and isExpression property turned to TRUE, then you could just assign required expression to the parameter in parameter file and use the parameter in the Name port instead of hardcoded concatenation string.

This feature become more practical in situations like bonus calculations, price formulas, scoring etc. where the change of expressions are more frequent.

Kiran Padiyar

1 comment: