Re-Runnability for ETL Processes Which Uses Mapping Variables
Reusability is a great feature in Informatica PowerCenter which can be used by developers. Its general purpose is to reduce unnecessary coding which ultimately reduces development time and increases supportability. User Defined Functions is one among the reusability feature provided by Informatica PowerCenter. In this article lets understand User Defined Functions in detail.

What is User Defined Functions

User-defined functions are extensions to PowerCenter transformation language. It allows you to create complex expressions using the built-in functions and operators and reuse them in PowerCenter mappings. User-defined functions are two types.
  1. Public : Callable from any user-defined function, transformation expression, link condition expression, or task expression. 
  2. Private : Callable from another user-defined function. Create a private function when you want the function to be part of a more complex function. The simple function may not be usable independently of the complex function.

Creating User Defined Functions

You can create a user defined function from Informatica PowerCenter Designer tool. You can invoke the User-Defined Function dialog box from main menu Tools > User-Defined Functions > New. Below given is a user defined function to convert the phone number into (XXX) XXX-XXXX format.
User Defined Functions in Informatica PowerCenter
The user defined function PHONE_NUMBER takes a 10 digit number as input and output a string formatted in  (XXX) XXX-XXXX. The expression editor shows the logic used with in the user defined function.

We can see all the user defined functions from the repository navigator as shown below.
User Defined Functions in Informatica PowerCenter

Using User Defined Functions

User defined functions can be used in any expressions just like any other functions. You can choose the available user defined functions from the functions explorer and use in expressions as shown in below image.
User Defined Functions in Informatica PowerCenter
Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out user defined functions and subscribe to the mailing list to get the latest tutorials in your mail box.

Share with your friends

Readers Comments