User-Defined Functions (UDFs) in Informatica Cloud (IICS)

1. Introduction

In the process of building mapping components within a project, we encounter scenarios where a complex expression logic is required for data transformation.  Informatica provides a feature which allows saving this complex expression logic into a reusable object which can be used across several mappings. This approach not only streamlines development process but also mitigates the need for redundant implementation of the same logic.

In this article, let us understand how to create and utilize this powerful feature called User-Defined Functions (UDFs).

2. What are User-Defined Functions (UDFs) in Informatica Cloud?

User-Defined Functions in Informatica Cloud are reusable functions that can be used in field expressions. User-defined functions allows you to create and store complex transformation logic using built-in functions and reuse them across mappings.

User-Defined Functions can be used in following components:

• Mapping
• Mapplet
• In a Field Expression of a Mapping task
• In another User-defined function

User-Defined Functions are not supported in following components

• In an expression in a Synchronization task.
• Mappings in Advanced Mode.

3. How to create User-Defined Functions (UDFs) in Informatica Cloud?

As an example, in this demonstration,
let us create a UDF which can extract the “date” value from a “timestamp” field.

Follow below steps to create a User-defined function in Informatica Cloud.

1. Navigate to New > Components > User-Defined Function and then click Create.

2. In the General tab, enter details of UDF Name, Location and Return Type of value that UDF returns. The supported return types are binary, date, numeric and string.

3. In the Arguments tab, create the Arguments to be used in the expression logic of UDF. The user-defined function supports creation of multiple arguments up to 10 in a single UDF component.

Note that if multiple arguments are created in a UDF, they are passed to the function in the order in which they appear on the Arguments tab. To rearrange the order, select an argument and click Move up or Move down.

4. In the Expression tab, create the field expression logic using the built-in functions and arguments created.

5. Validate and Save the User-defined Function.

4. How to use User-Defined Functions (UDFs) in Informatica Cloud Mappings?

The User-defined function can be used in any field expression of a transformation in the mapping.

Follow below steps to use User-defined function in a mapping.

1. Create an output/variable field with a data type identical to the return type of the user-defined function.

2. In the field expression editor of the transformation, under User-defined functions, list of all the valid UDFs will be displayed.

3. Select the required UDF into the expression editor and pass the input fields from upstream transformation as argument to the UDF.

The User-defined function selected in the expression editor should be prefixed with “:UDF.” as shown below. 

:UDF.<UDF_Name>( argument1, argument2)

The below image shows the output of the field Updated_Date using the GetDate_From_Timestamp user-defined function.

5. Practical scenarios for the application of User-Defined Functions (UDFs) in Informatica Cloud

Below are some of the real-time use cases of UDFs in Informatica Cloud

5.1. Setting Default Values for Nulls

In a real-time data integration scenarios, UDFs can be employed to set default values for missing or null data. This ensures consistency across not null fields as they all are assigned with same default values.

UDF Name: SetDefaultValueForNull

UDF Argumnets: Input_Field

UDF Expression: 
IIF(ISNULL(LTRIM(RTRIM(Input_Field))), 'N/A', LTRIM(RTRIM(InputField)))

UDF Usage:
:UDF.SetDefaultValueForNull(Address)

5.2. Creating a Master Data List for Data Mapping

UDFs help in building a Reference Data Repository. For instance, you can construct a repository designed to retrieve currency information by supplying a country code as an argument to UDF. Any addition of new country information into the UDF is easily propagated to all mappings using UDF without the need to edit the mapping.

UDFName: GetCurrency

UDF Arguments: COUNTRY

UDF Expression:
DECODE(COUNTRY,
'AUS','AUD',
'CAN','CAD',
'IND','INR',
'JPN','JPY',
'USA','USD',
'N/A')

UDF Usage: 
:UDF.GetCurrency(Country_Code)

5.3. Advanced Pattern Matching with Regular Expressions

UDFs help in storing complex transformation logic involving Advanced Pattern Matching with Regular expressions. For instance, you can construct a UDF which accepts any field as an argument and removes all special characters from the field value and replace them with null using regular expressions.

UDFName: RemoveSpecialCharacters

UDF Arguments: Input_Field

UDF Expression:
REG_REPLACE(Input_Field,'[^[:print:]]','')

UDF Usage:
:UDF.RemoveSpecialCharacters(Customer_Remarks)

5.4. Storing Custom Complex Formulae

UDFs allow you to store and apply unique business rules involving complex custom formulae for calculations. For instance, you might have a unique business rule that calculates monthly mortgage payments based on the principal loan amount, annual interest rate, and loan term. You can encapsulate this complex mortgage calculation logic in a UDF for consistent and real-time application.

UDF Name: CalculateMonthlyMortgagePayment

UDF Arguments: PrincipalAmount, MonthlyInterestRate, LoanTenureInMonths

UDF Expression:
PrincipalAmount * (MonthlyInterestRate * POWER((1 + MonthlyInterestRate),LoanTenureInMonths)) / (POWER((1 + MonthlyInterestRate),LoanTenureInMonths) - 1)

UDF Usage:
:UDF.CalculateMonthlyMortgagePayment (Loan_Amount, Interest_Rate, Tenure)

6. Updating and Deleting User-Defined Functions (UDFs)

Updating a user-defined function can affect the expressions and functions that use it. Follow below guidelines for updating and deleting User-defined functions.

• If a user-defined function is edited and the updated function is valid, Data Integration propagates the changes to all expressions and user-defined functions that use the function.
• If a user-defined function is edited but the updated function is not valid, Data Integration does not propagate the changes to the expressions and user-defined functions that use it.
• A User-defined function cannot be renamed after it is saved.
• A User-defined function can only be deleted after removing it from all expressions and functions that use it.

7. Difference between User-Defined Functions (UDFs) and Mapplets

Both User-Defined Functions (UDFs) and Mapplets are reusable components in Informatica Cloud for data transformation and processing. But they serve a different purpose and have own distinct characteristics.

• UDFs are primarily used for defining custom data transformation logic in field expressions. They allow you to encapsulate specific data manipulation logic in a reusable function that can be applied within a mapping or across different mappings.
• Mapplets contain reusable transformation logic that can contain multiple transformations, source definitions, and target definitions. They are designed to encapsulate a set of transformations for reuse across multiple mappings.

Subscribe to our Newsletter !!

Related Articles: