Expression Macros in Informatica IICS – IDMC

Macros in IICS

Macros in IICS: An expression macro is a macro that you use to create repetitive or complex expressions in mappings. You can use an expression macro to perform calculations across a set of fields or constants.

There are three types of Macros

1. Vertical Macros (Most Used in Realtime)
2. Horizontal Macros
3. Hybrid Macros

Vertical Macros

1. A vertical macro expands an expression vertically. The vertical macro generates a set of similar expressions to perform the same calculation on multiple incoming fields.
2. To start with the Vertical macros, Create an Expression Transformation in IICS mapping and create an input macro and give any name as Input_Macro_Space.

3. Then Create an output Macro, select the input macro under Input Macro Field and give the precision as Maximum.

4. Give your Macro Expression under Expression configure, for the output Macro
5. For example I wanted to remove all the Space before or after the string also I want to Reverse the string, then use Ltrim, Rtrim and Reverse built functions then give as below

Note: Macros Will not be Validated in Expression Transformation. Macros will be always evaluates at run time.

6. To Overcome this, Create an Input parameter called Field_Mapping with type of Filed mapping as shown below.

7. Under filed mapping at Target TR, select as Completely Parameterized.

8. Then Create a MCT and Resolve the field mapping in MCT level as shown below.
9. In the below screenshot, I have mapped all the string fields with _out. And the remaining I have connected as it as.

10. Let’s have scenario like I wanted to apply the macro to all string fields only. Then we should have two flows from source to expression to target
11. In the First flow, just apply Exclude the fields by Data type other than String and text as shown below.

12. And In the Second flow, just apply Exclude the fields by Data type String and text fields as shown below.

13. Now if you see the filed mapping in MCT, you will see out_ suffixed only to String fields, but not to other fields.

Create a Generic Mapping To apply the same macro for more mappings:

1. Create two Input Parameters named, Src_Table, Tgt_Table. And parameterize source and target tables in mapping.
2. Select the Any different Source and Target table in MCT Level.
3. Now automatically MCT will show the _out Prefix only to String fields as shown in below.
4. So, whenever you wanted to Use different source and target tables, Just Re-use the same mapping (use this as a template) and Create as many MCTs as you want for other source and target tables.
5. So, for a Realtime Scenario, use this mapping as a Data cleansing template to Cleanse the data from all Source t Stage tables.

6. Few Useful macros:

•  To Remove Left space, right space, and reverse the character:
   ltrim(rtrim(Reverse(%Input_Macro_Space%)))
• to Remove Left space, right space and convert to upper case:
  ltrim(rtrim(Upper(%Input_Macro_Space%)))
• to Remove Left space, right space and in between space case:
  ltrim(rtrim(Replacechr(0,%Input_Macro_Space%,’ ‘,”)))
• to Remove Left space, right space, in between space case and also Special characters:
  ltrim(rtrim(Replacechr(0,REG_REPLACE(%Input_Macro_Space%,'[^a-z0-9A-Z]’,”),”,”)))

7. Few Useful Expressions can be used in macros

a. REG_REPLACE(PORT_NAME, ‘[^\w,-. ]’, ”)
This function removes the special characters and retains only alphanumeric characters, commas, dashes, and periods.
b. REG_REPLACE(PORT_NAME,'[^a-z0-9A-Z]’,”)
This function retains alphanumeric characters only.
c. REG_REPLACE(PORT_NAME,'[^[:print:]]’,”)
This function looks for ‘[^[:print:]]’ which means its searching for non-printable characters in the field which we are passing and it’s been replaced with ” (NULL).

Horizontal Macros:

1. A horizontal macro expands an expression horizontally. The horizontal macro generates one extended expression that includes a set of fields or constants.
2. Let’s Take an Example that, I have an Emp table which have 8 columns , in which Few values are nulls as shown like below.

3. In the above image, I have few nulls in hiredate, Comm columns. Now my requirement is the rows which are having nulls in any columns should go one target table “stg_emp_null” and the rows which are having not nulls should go to another target tale “stg_emp”.
4. To Achieve this, I will write a Horizontal macro as like below in expression transformation.

5. In the above image I have used an Horizontal Expansion built in function OPR_SUM like below.

%OPR_SUM[ IIF(ISNULL(%in_MCRO%),1,0) ]%

The above function will evaluate each row horizontally and gives flag=1 if any row contains Null And it will give Flag=0 if any row does not have null.

6. After running the mapping like below image, The Rows which are having nulls are routed to stg_emp_null and the rows which are having not nulls are routed to stg_emp

7. Output of the tables as shown below.

Table which are Nulls in any column:

Table which are not having Nulls in any column:

Hybrid Macros:

A hybrid macro expands an expression both vertically and horizontally. A hybrid macro generates a set of vertical expressions that also expand horizontally.

Configure a hybrid macro based on your business requirements. Use the configuration guidelines for vertical and horizontal macros to create a hybrid macro.