Cygnus-X1 Consulting Ltd. - User Defined Functions in Informatica

User Defined Functions in Informatica

Written by Administrator

Monday, 25 February 2008

Have you ever found yourself repeating the same expression over and over in your code, just replacing the input values each time? For instance, you may have several string fields from a fixed width source file that you need to trim whitespace from both the leading and trailing edges?

Assuming we had 6 fields that needed this treatment - we would need to create an exression transformation, drag each of the fields in and create an output only copy:

EXP_TrimData
Port Name	Type	In/Out	Expression
i_ColumnA	string(10)	Input
i_ColumnB	string (50)	Input
i_ColumnC	string (1000)	Input
...
o_ColumnA	string (10)	Output	LTRIM(RTRIM(i_ColumnA))
o_ColumnB	string (50)	Output	LTRIM(RTRIM(i_ColumnB))
o_ColumnC	string (1000)	Output	LTRIM(RTRIM(i_ColumnC))
...

Okay, so to cut/paste the code a half dozen times within one EXP transformation isn't such a big deal (although risks you forgetting to replace the original column name with the one you want to assign to the output), but what if you have 50 fields, or 20 different source files with the same problem?

You could create a reusable expression to do this - lets call it EXP_TrimString - with one input port (i_INPUTSTRING), and one output port (o_OUTPUTSTRING) - and use it wherever we need a neatly trimmed string. Of course to ensure reusability, we would have to make both ports the maximum string length allowed by Informatica.

Great - now lets use it in our hypothetical mapping... drop half a dozen instances of EXP_TrimString in, and give them meaningful names (you do change the names from EXP_TrimString1, EXP_TrimString2 to EXP_TrimString_columnA, EXP_TrimString_columnB etc in your coding shop don't you ?!)

Hmm, so that was worth doing wasn't it? A cluttered mapping, loss of visibility of string lengths, and probably more time and effort expended than doing it the old cut 'n' paste way.

Fortunately, Informatica 8 solves this problem by introducing user defined functions. These allow you to define, at folder level reusable snippets of code that can be used within transformation expressions.

Having defined a user defined function "TRIM" we can use it in our original transformation:

EXP_TrimData
Port Name	Type	In/Out	Expression
i_ColumnA	string(10)	Input
i_ColumnB	string (50)	Input
i_ColumnC	string (1000)	Input
...
o_ColumnA	string (10)	Output	:UDF.TRIM(i_ColumnA)
o_ColumnB	string (50)	Output	:UDF.TRIM(i_ColumnB)
o_ColumnC	string (1000)	Output	:UDF.TRIM(i_ColumnC)
...

Okay, so in this particular case, the typing is not much less - but the advantage is that the user defined function appears in the list of functions within the Expression Editor dialog box (useful if you are a mouse-click programmer or wish to share your efforts with other team members).

Below is an informatica export with some other simple but useful User Defined Functions:

<?xml version="1.0" encoding="ISO-8859-1"?>

<!DOCTYPE POWERMART SYSTEM "powrmart.dtd">

</EXPRMACRO>

</EXPRMACRO>

</EXPRMACRO>

</EXPRMACRO>

</EXPRMACRO>

</FOLDER>

</REPOSITORY>

</POWERMART>

Last Updated ( Monday, 25 February 2008 )

Next >

[ Back ]

Newsflash

Main Menu