Expression Transformation in Informatica

What is Informatica?

Informatica is a software development company, which offers data integration products. Some of them are ETL, data quality, data replica, data masking, master data management, and more. What makes Information better than the other companies in the market? It offers a wide range of product editions. Users can choose separate editions based on their requirements. Informatica is available for all widely used platforms. In this blog, we will dig deep into Informatica transformations. We will explore the steps to create expression in Informatica.

 

Expression Transformation in Informatica

Expression transformations are used for row-wise manipulation. For any manipulation, you wish to perform on an individual record, use an Expression transformation. The Expression transformation accepts the row-wise data, manipulates it, and passes it to the target. The transformation receives the data from the input port and sends the data out from output ports.

This is of type passive transformation which allows you to calculate the expressions for each record. An expression transformation in Informatica supports writing expression either in variable ports or only in output ports. In this Informatica Tutorial for Beginners, we will learn about Expression Transformation, its uses, and examples. We will also learn about Rank Index and Rank Transformation in other posts.

 

Informatica Expression Transformation Uses

Use an Expression Transformation to derive the new attributes from changing the inconsistent data into a consistent format.

Use Expression Transformations for any row-wise calculation, such as if you want to concatenate the names, get the total salary, and convert it to upper case.

An expression transformation is created with the following types of ports.

• Input port (I)
• Output Port (O)
• Variable Port (V)

Do you want to master Informatica? Then enroll in "Informatica Training" This course will help you to master Informatica

Variable Ports

A transformation variable is created by creating a port and selecting the V checkbox. When V is checked, the I and O checkboxes are greyed out. This indicates that a variable port is neither an input nor an output port. Variables Ports are not visible in Normal view, only in Edit view.

• Simplify complex expressions, e.g., extract month from a date for use in several output ports
• Provide temporary storage
• Improve efficiency
• Variables are initialized (numeric to 0, string to “”) when the Mapping logic is processed

Ports are evaluated in the order

• All input ports
• Variable ports in the display order (expressions can refer to input ports and variable ports that appear earlier in the port list)
• Output ports (expressions can reference input and variable ports

Advanced Properties for Expression Transformation

Expression Transformation in Informatica Example

 

Business Logic

Calculate the tax(sal*0.17) for the top 3 employees based on salary, Who belongs to the ‘sales’ department sales department Id is 30.

   

 

Procedure for Creating Transformations

• Create a source-target definition as described in DFD
• Create a mapping with the name M_Tax_calculation
• Drop definitions of origin and target to the mapping designer workspace.
• From transformation, the menu selects create and selects the transformation type filter.

Enter the name filter_tax_cal click on create

Click Done

From the source qualifier (SQ-EMP) Copy the required ports to the filter transformation (EmpName, Eno, Job, sal, Dept no)

• Double-click on the filter transformation and select the properties tab
• Header Names are
• Transformation attribute                                                              Value
• Filer condition                                                                                 Dept no-30
• Click validate  click ok
• Click Apply
• Click ok

From the Transformation menu select Create select the transformation type rank

• Enter the name Rank-tax-cal
• Click on Create and Done

From filter transformation copy the ports to the rank transformation.

• Double-click on Rank Transformation
• Select the ports tab
• For port name Sal select rank port
• Select the properties tab
• Transformation attribute                                                              Value
• Top/Bottom                                                                                      Top
• Number of Ranks                                                                             3
• Click Apply
• And click ok

From the Transformation menu select create

• Select the transformation type expression
• Enter the name Exp-Tax-cal
• Click on Create and Done

From Rank, transformation copy the ports to an expression transformation (Except Rankindex)

• Double-click on Expression Transformation select ports tab.

From the toolbar click on Add a new port

• Data type precision scale I O V Expression
• Tax decimal                                7                              2                              dsl*0.17
• Click Apply click on ok

From the expression, Transformation connects the ports to the target from the repository menu and click on save.

Read [note style="" bg="" border="" bordercolor="{{bordercolor}}" color=""] Read about Router Transformation here [button url="http://tekslate.com/router-transformation/" class="" bg="" hover_bg="" size="0px" color="" radius="0px" width="0px" height="0px" target="_self"] Router Transformation [/button] [/note]

These core tutorials will help you to learn the fundamentals of Informatica Data Quality Tutorials. For an in-depth understanding and practical experience, explore Informatica Data Quality Tutorials.

 

Power Center Transformation Language

The power centre transformation language is a set of built-in functions that are similar to SQL functions.

The Built-in functions are used to develop the business logic or transformation rules.

The function is categorized into the following types

• String functions
• Numeric functions
• Data functions
• Aggregate functions
• Test functions
• Variable functions
• Data cleansing functions
• Financial functions
• Scientific functions
• Miscellaneous functions

Example

Design a mapping which rejects the records contains null. Procedure:-

• Source definition (EMP)
• Target definition (EMP-NULL-Validate)
• Target columns (Empno, emp name, job, comm., deptno)

Create the mapping with name  m_employee_NULL)reject Drop the source and target definitions Create the transformation type filter

From the source, qualifier copy the required ports to the filter transformation

Double click on filter transformation  select the properties tab

Transformation attribute                                                              Value

Filter condition                                                                  IIF(ISNULLcomm), False, )

Click Apply click on ok

From filter, transformation connects the ports to the target

Example

Design a mapping to verify the null for every source column, reject the record if any column contains null.

Procedure:

• Create source and target definition
• Create mapping with the name m_EMP_NULL_validate
• Create the transformation type filter from source unifier copy the required ports to filter

Double click the filter transformation select the properties tab

Transformation attribute                                                              Value

Filter validation                                                                 IIF (ISNULL empno)OR

ISNULL(ENAME)OR

ISNULL(MGR)OR

ISNULL(SAL)OR

ISNULL(COMM)OR

ISNULL(DEPTNO), False, True)

Click Apply click ok

From filter, transformation connect ports to the target

Example

Design a Mapping which can load the employees whose Ename Start with ‘S’.

 create source and target definitions

 create the mapping with the Name M-EMP-5

 Drop the source and target definitions

 create the transformation type filter and develop the following filer condition substr (EName, 1,1,)='S'

from filter, transformation connects the ports to the target

Example

Design a mapping which can load employees whose Ename is having > =6 characters

Procedure:

Create the filter transformation with the following condition length (Ename)>=6

Ex:- Design a mapping which can load employees whose Empno is an even number

Procedure:

MOD(Ename.2)=0

Checkout Our Blog on Aggregator Transformation in Informatica

Steps to Create Expression Transformation in Informatica

Transformation

Informatica transformations create, modify, or pass data to a defined target structure (tables, files, or other targets). Their two main goals are: 

• They modify the source data as per the target system’s requirements. 

• They ensure the loading of data quality into the target. 

Types of transformation 

There are two categories of transformation. 

Based on connectivity 

• Connected Transformations: You can use connected transformations when you get a transformation request for every input row or when the value is expected to return. 

• Unconnected Transformations: The unconnected transformations are only useful periodically or based upon certain conditions. 

Based on the change in the number of rows

• Active Transformations: Active transformations modify the data rows and the number of input rows. 

• Passive Transformations: Passive transformations do not change the number of input rows. 

Joiner Transformation 

It is an active and connected transformation that allows you to create joins in Informatica. The advantage of joiner transformation is that you can create joins for different databases. The two sources used for joins are: 

• Master Source 
• Detail Source 

The types of joins you can create using joiner transformation are: 

• Master outer join: The join returns all the records from the detail source. Only matching rows from the master source are returned. 

• Detail outer join: Only matching rows are returned from the detail source. All rows from the master source are returned. 

• Full outer join: All records from both the sources are returned. 

• Normal join: Only matching rows are returned from both sources. 

SQL Transformation 

The Informatica SQL transformation is a connected type transformation. It processes SQL queries in a pipeline’s midstream. We can use SQL transformation to insert, update, delete, and retrieve rows from the run time database. 

Here are the SQL statements used in the SQL transformation. 

• Data Definition statements (CREATE, ALTER, DROP, TRUNCATE, RENAME) 

• Data Manipulation statements (INSERT, UPDATE, DELETE, MERGE) 

• Data Retrieval statement (SELECT) 

• Data Control Language statements (GRANT, REVOKE) 

• Transaction Control statements (COMMIT, ROLLBACK) 

👉Read these latest Informatica Data Quality Interview Questions that help you grab high-paying jobs

SQL Transformation Configuration 

The options to configure an SQL transformation are:

• Mode: SQL transformation runs either in script mode or query mode. 

• Active/Passive: SQL transformation is an active transformation by default. You can configure it as passive transformation. 

• Database type: SQL transformation connects to a database type. 

• Connection type: You can either pass a database connection or use a connection object. 

The SQL Case Statement 

The CASE statement goes through conditions. It returns a value when the first condition is fulfilled (for example, IF-THEN-ELSE statement). Once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE, and no conditions are true, it returns NULL. 

Normalizer Transformation 

Normalizer transformation is an active transformation that converts a single row into multiple rows and vice versa. You can organize your data more effectively with normalizer transformation. 

Informatica Substring function 

The substring function retrieves a particular piece of the string. Substring counts blank characters in the string. For example, 

Substring( Australia, 4, 7) 

“4” is the position of the starting character of the substring. 

“8” is the position of the ending character of the substring. 

Informatica Not NULL 

You can do the Not NULL check by using the Informatica ISNULL function. There are two ways to do this: 

1. Using the NOT Operator 

IIF(NOT ISNULL(value), ' Input is not null',' No it is null')

2. Checking with the return value. 

IIF(ISNULL(value)=0, ' Input is not null',' No it is null')

Verbose initialization mode in Informatica 

In the verbose initialization mode, Informatica writes the log details as same as normal mode. It also writes about the data and index files used and transformation statistics. 

Regular Expression in Informatica 

In Informatica, a regular expression includes characters representing the source character types, source character sets, and string or word boundaries in the source columns. It can also include quantifiers that decide how many times a character can occur in the source data. Regular expressions are case sensitive. 

Use cases of Informatica 

Few use cases of Informatica are: 

• Data cleansing 

• You can use it to set up enterprise data warehouses.

• Integrating data from heterogeneous systems (multiple databases and file-based systems) 

• Migrating from existing legacy systems to new database systems 

Advantages of Informatica 

The advantages of the Informatica data integration tool are: 

• It enables effective and efficient data communication and transformation between different sources. 

• It is faster, cost-effective, and easy to learn. 

• It easily monitors jobs, recovers failed jobs and points out slow jobs. 

• It has many powerful features like database information, data validation, migration of projects from a database to another, etc. 

Informatica PowerCenter 

Informatica Corporation developed the Informatica PowerCenter, which is one of the Enterprise Data Integration products. Information PowerCenter is an ETL (extraction, transformation, and loading) tool. It extracts data from the source, transforms, and loads data into the target. The functions of the ETL tool are: 

• The Extraction part understands, analyses, and cleans the source data.

• The Transformation part cleanses the data more precisely and modifies data to meet business requirements.

• The Loading part assigns dimensional keys and loads them into the warehouse. 

 

Transformation types in Informatica 

Types of ports in Informatica Expression Transformation 

Expression Transformation has the following ports: 

• Input port: The input port in Informatica receives data from upstream transformations. 

• Output port: The output port in Informatica contains the expression’s return value. 

• Pass-through port: The pass-through port in Informatica passes data through the transformation without changing the value. 

• Variable port: The variable ports in Informatica temporarily store data to use in expressions. 

• Dynamic port: The dynamic port in Informatica receives or returns ports in a dynamic mapping. 

• Generated Port: The generated port in Informatica represents a single column within a dynamic port. 

Checkout: [ Sorter Transformation in Informatica ]

Components of Expression Transformation 

The expression transformation has the following components or tabs: 

• Transformation: Enter the name and description of the transformation. The expression transformation can also be made reusable. 
• Ports: Configure the new ports. 
• Properties: Configuring the tracing level to set the transaction detail amount to logged. 
• Metadata Extensions: Specifying the extension name, data type, precision, value, and also creating reusable metadata extensions. 

Factors to consider when developing an Expression Transformation 

There are two main factors to consider when developing an Expression transformation: 

• The port type. 
• The expressions. 

How is Expression Transformation passive? 

A passive transformation in Informatica does not change the number of rows that pass through the transformation. It also maintains the transaction boundary and the row type. Therefore, Expression Transformation is passive because it does not change the number of output rows. 

Generating sequence numbers with expression transformation

The steps to generate sequence numbers with expression transformation are: 

• Create a variable port in expression transformation and increase it by 1. 

• Assign the variable port to an output port 

• In the expression transformation, the ports are: 

  • V_count=V_count+1 

  • O_count=V_count

 

Wrapping Up

Informatica offers rich features like row-level operations on data, data integration from multiple structured, semi-structured, or unstructured systems, data operation scheduling, and so on. According to research, Forbes has said that Informatica might be the next Microsoft. In the current age, Information has an advantage over its competitors in the industry. 

 

For an in-depth understanding of Informatica, click on

• Lookup Transformation in Informatica
• ETL Project Architecture
• Informatica Interview Questions