Expression Macros in Informatica Cloud (IICS)

1. Introduction

Expression is a passive transformation which allows you to create Output Field and Variable Field types to perform non-aggregate calculations and create new fields in the mapping. Expression transformation also provides two other field types in Informatica Cloud Data Integration which are Input Macro Field and Output Macro Field.

A Field expression transforms source data before loading into target. The scope of a field expression you define for an Output or a Variable field is within that field itself. It cannot be extended for other fields. In order to extend the field expression logic to other fields you need to define same logic for each field separately.

For example, you need to convert source data into Upper case before loading into target. To implement this, you need to apply the upper conversion logic for each field separately.

Let us discuss in detail how this process can be simplified using Input Macro field and Output Macro field and how to implement them.

2. What are Expression Macros?

Expression Macros provide a solution to define the field expression logic once and extend them to all the required fields in expression transformation. They allow you to create repetitive and complex expressions in mappings.

An Input Macro Field expression represent the data of multiple source fields.
An Output Macro Field expression represents the calculations that you want to perform on each input source field.

The expression macros can be implemented in 3 different types.

1. Vertical Macro
2. Horizontal Macro
3. Hybrid Macro 

3. Vertical Macro

A Vertical Macro expands an expression vertically. That implies vertical macro generates a set of same expression conditions on multiple incoming fields.

Let us understand in detail with an example. Consider the following as the Source data.

"ID","FirstName","LastName"
"101"," John","Chen "
"102","Alexander  "," Khoo"
"103","   Sigal"," "
"104","Karen","  "
"105","Matthew"," Weiss  "

 It can be observed that the source data has lot of spaces which needs to be trimmed before loading into the target. This can be implemented using Vertical Macros.

3.1 Design a mapping to trim spaces in source fields data using Vertical Expression Macros

3.1.1 Implementing Vertical Macros with a Dynamic target

3.1.1.1 Source

Create a mapping and select the CSV file with data as shown above as source object.

3.1.1.2 Expression

Pass the data from Source to an expression transformation.

• In the expression transformation create a new filed of type Input Macro Field and enter the name as Source_Fields.

• Once you click to enter the expression value of the input macro field, you will see options as shown below. You will have option to select All Incoming fields or only particular incoming fields or fields by text or pattern. Select All Incoming Fields and click OK.

• The field expression for the input macro will be auto populated as {“Source_Fields”:”All Ports”}
• Create an Output Macro field. The Input Macro Field value is auto populated (If there are multiple input macro fields, you can select by using the drop down menu).
• The Suffix value is also auto populated as ‘_out’ which can be modified as per the user requirement. The suffix value implies the output field names created by macro will be named as your incoming field names with a suffix value entered here.
• Enter the Precision value as per the source data.

• Enter the expression value for output macro field as LTRIM(RTRIM(%Source_Fields%))
• The expression fields will be as below

NOTE: In the example discussed here all the fields are considered as string. If you are working with different datatypes, you will have to create separate input and output macros fields for each type.

3.1.1.3 Target

• Pass the data from expression to a target transformation.
• In the Incoming Fields section, select Field Selection Criteria as Fields by Text or Pattern and specify the suffix as ‘_out’. Because we will be getting two sets of data here, the actual source fields data and fields created by expression macro with suffix as ‘_out’
• In the Target section, create a dynamic flat file target and run the mapping.
• Below is the final output where the field data is trimmed and observe field names are added with suffix ‘_out’

"ID_out","FirstName_out","LastName_out"
"101","John","Chen"
"102","Alexander","Khoo"
"103","Sigal",""
"104","Karen",""
"105","Matthew","Weiss"

This is how the vertical expression macro expand the field expression for N input fields

3.1.2 Implementing Vertical Macros with an existing target

In the above example we have seen expression macro implementation with a dynamic target. There is a slight difference in implementation if your target is an existing database table or flat file.

• In this example I have created a flat file with just header and used it as a target.
• The entire implementation until the target transformation is same as we discussed in above example.
• If your target is dynamic, you need not worry about the Field Mapping section of the target.
• But if your target is not dynamic, you will see Field Mapping section as below. The Macro output field cannot be mapped directly to any target field as it holds all input fields.
• Mapping a macro field directly to any target field will result in the failure during the mapping run.

When your target is not dynamic you need to parameterize the Field Mapping while using vertical expression macros.

• As you can see I have parameterized the field mapping using a parameter Macro_FieldMapping.

• Trigger the mapping and you will see that expression macro expands into fields and can be mapped to target fields during runtime.

4. Horizontal Macro

A Horizontal Macro expands an expression horizontally. That implies horizontal macro generates a set of same expression conditions on multiple incoming fields horizontally.

Let us understand with an example. Consider the same source data used for vertical macros

4.1 Design a mapping to filter out the records with NULL data using Horizontal Expression Macros

In this scenario if any of the field data is empty for any record it needs to be filtered out before loading into target.

How do we implement in regular scenario?

1. We will check if each field of the record has data.
2. If yes, set a value for the field as ‘0’ else set the value as ‘1’
3. Once the same logic is implemented for each field, check the sum of all values of fields.
4. If the sum is ‘0’, all fields have data else if the sum is ‘>0’ not all fields are with data.

The field expression to calculate sum will be as below for our example

IIF(LENGTH(LTRIM(RTRIM(ID)))=0,1,0) +
IIF(LENGTH(LTRIM(RTRIM(FirstName)))=0,1,0) + 
IIF(LENGTH(LTRIM(RTRIM(LastName)))=0,1,0)

Let us see how this can be simplified using Horizontal expression macros.

4.1.1 Source

Create a mapping and select the CSV file with data as shown above as source object.

4.1.2 Expression

• Pass the data from Source to an expression transformation.
• Create the Input and output macro fields similar to the example in vertical macros.
• Now create an extra output field Flag and set the value as below.

%OPR_SUM[IIF(LENGTH(LTRIM(RTRIM(%Source_Fields%)))=0,1,0)]%

• OPR_SUM is a Built-in Horizontal expansion function which uses the SUM function and expands an expression in an expression macro to return the sum of all fields

• The expression will be as below

4.1.3 Filter

Pass the data from expression to filter transformation. Set the filter condition as Flag=0

4.1.4 Target

• Pass the data from filter to target and rest of the steps are same as we discussed in vertical macros example.
• Run the mapping and the output will be as below

"ID_out","FirstName_out","LastName_out"
"101","John","Chen"
"102","Alexander","Khoo"
"105","Matthew","Weiss"

Observe that we have implemented both the Vertical and Horizontal macro in this example but using a separate field for each.

This is how the Horizontal expression macro expand the field expression for N input fields

5. Hybrid Macro

A Hybrid Macro expands an expression both horizontally and vertically.

Let us understand with an example. Consider the same source data used for vertical macros

5.1 Design a mapping to load entire fields data of a record as NULL if any of the source field data is empty

The expected target data is as below.

"ID_out","FirstName_out","LastName_out"
"101","John","Chen"
"102","Alexander","Khoo"
"NULL","NULL","NULL"
"NULL","NULL","NULL"
"105","Matthew","Weiss"

Since the LastName for records with ID 103 and 104 is empty in source data, all the fields in the record are loaded as empty.

This may not be an ideal real time scenario but it explains the hybrid macros implementation.

5.1.1 Source

Create a mapping and select the CSV file with data as shown above as source object.

5.1.2 Expression

• Pass the data from Source to an expression transformation.
• Create field expression as shown below.

5.1.3 Target

Pass the data from expression to a target transformation and rest of the steps are same as discussed in above examples.

Let us take a pause and understand what we have done here.

• The Input and output macro fields are created just as same as vertical macros example except that we have added an extra condition in it which is a horizontal macro.
• The horizontal macro finds the length of each field and if the field has data, sets the flag value to ‘0’ else ‘1’ and finally calculates the sum of the result of each field. If the sum is 0, all fields are with data else one or more fields are empty.
• The horizontal macro is now incorporated in a vertical macro hence the field expression expands both horizontally and vertically.

This is how the Hybrid expression macro expand the field expression for N input fields

6. Conclusion

The expression macros can be implemented in expression and aggregator transformations.

The examples used here are just to introduce you to the concept. There are larger real world use cases. They are extremely helpful if you are working with large number of input attributes and same repetitive logic needs to be applied for all.

Try to use them whenever you get a chance to implement them in your projects and leverage the advanced functionality.

7. Test Your Understanding

Expression Macros Quiz

Five Multiple Choice Questions

Start Quiz

Question

Your answer:

Correct answer:

Next

You got {{SCORE_CORRECT}} out of {{SCORE_TOTAL}}

Your Answers