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 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.
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.
| Do you want to master Informatica? Then enroll in "Informatica Training" This course will help you to master Informatica |
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.
Advanced Properties for Expression Transformation
From SQ-Emp copy the required ports to expression transformation double click on expression transformation select the ports tab from the toolbar click on add a new port
| port name | datatype | precession | sale | I.O.V | expression |
| tsal | decimal | 7 | 2 |
IIF (ISNULL(cinn), SAL, SAL+Com
SAL+IIF(ISNULL(comm),0,comm
click apply click ok
From expression, transformation connects the ports to the target
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.
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)
From the Transformation menu select Create select the transformation type rank
From filter transformation copy the ports to the rank transformation.
From the Transformation menu select create
From Rank, transformation copy the ports to an expression transformation (Except Rankindex)
From the toolbar click on Add a new port
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. |
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
Example
Design a mapping which rejects the records contains null. Procedure:-
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.
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 |
Informatica transformations create, modify, or pass data to a defined target structure (tables, files, or other targets). Their two main goals are:
They ensure the loading of data quality into the target.
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.
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:
The types of joins you can create using joiner transformation are:
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.
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 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 |
The options to configure an SQL transformation are:
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 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 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.
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.
You can do the Not NULL check by using the Informatica ISNULL function. There are two ways to do this:
IIF(NOT ISNULL(value), ' Input is not null',' No it is null')Checking with the return value.
IIF(ISNULL(value)=0, ' Input is not null',' No it is null')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.
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.
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
The advantages of the Informatica data integration tool are:
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 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 |
DESCRIPTION |
|
Aggregator Transformation |
Aggregator transformation performs an aggregate calculated based on the passed data through the transformation. |
|
Expression Transformation |
Expression transformation performs a calculation based on values within a single row. |
|
Filter Transformation |
Informatica filter transformation specifies a filter condition passed rows through this transformation. |
|
Rank Transformation |
Rank transformation sets conditions for rows in a rank. |
|
Router Transformation |
Router transformation routes data into multiple transformations based on a group expression. |
|
Update Strategy Transformation |
Update strategy transformation flags a row to update, insert, delete, or reject. This transformation controls updates to a target based on some applied conditions. |
Expression Transformation has the following ports:
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 ]
The expression transformation has the following components or tabs:
There are two main factors to consider when developing an Expression transformation:
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.
The steps to generate sequence numbers with expression transformation are:
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.
You liked the article?
Like: 1
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
