EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Informatica Tutorial Transformations in Informatica
 

Transformations in Informatica

Aanchal Singh
Article byAanchal Singh
EDUCBA
Reviewed byRavi Rathore

Updated March 21, 2023

Transformations-in-Informatica

 

 

Introduction to Transformations in Informatica

Transformations in Informatica are the built-in features with Informatica products such as, Informatica PowerCenter that are used to transform and validate the source data as part of the ETL process flow. Transformation in Informatica is majorly two categories, known as active transformations and passive transformation. There are several business scenarios such as filtering of inputting data, routing the data, or shorting the Informatica mappings’ input data to develop the business requirements. Also, Informatica supports both connected and unconnected type of transformation functionalities as part of the product offering. Some of the common transformations in Informatica are Source qualifier; the Informatica developers widely use router, joiner and aggregator transformations.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Types of Transformations in Informatica

Transformations are mainly of two types. They can be connected or unconnected. The transformations connected to other transformations are known as connected transformations while the ones that are not connected to any other transformation are unconnected transformations. Similarly, there can be active transformations or passive transformations. Active transformations are those which modify data rows and the input which is passed to it. Passive transformations, on the other hand, do not change the number of rows. The input and output rows remain the same and data is modified at row level only.

Let us have a look at the different transformations provided by Informatica. They are listed as below:

  • Source Qualifier Transformation
  • Aggregator Transformation
  • Router Transformation
  • Joiner transformation
  • Rank Transformation
  • Sequence Generator Transformation
  • Transaction Control Transformation
  • Lookup and Re-usable transformation
  • Normalizer Transformation
  • Performance Tuning for Transformation
  • External Transformation
  • Expression Transformation

We will have a look at a few of these in detail as below.

1. Joiner Transformation

This transformation is active and connected. When two sources are to be joined on certain conditions, then this transformation is used. These can be any two relational sources, a relational source and a flat file, a relational source, and an XML file or two flat files. There should be at least one port that is common between both these sources. While joining these two sources, you must specify a condition. Once could be a master and the other detail.

Example of joiner transformation:

 Joiner Transformation

This is a joined where conditions are applied on below ports:

 Joiner Transformation

The master port is BE and Activity_code. These are present in both sources. The data is filtered and joined on the above conditions. The other properties of these joins can be found in the properties tab as below:

 Joiner Transformation

The joiner can have following joins.

  • Normal join: This join will remove all records which will not match the joiner condition.
  • Master outer join: This will discard all records in the master source which do not match, and all rows from detail source will be taken into the output.
  • Detail Outer join: In this join, all records from the master source will be considered while only matching rows of detail source will be taken into consideration.
  • Full outer join: This will keep all rows from both master and detail tables.

2. Lookup Transformation

This transformation is used to look up for data into a relational source. The data can be looked up into either source or target. These transformations can either be connected or unconnected. The connected lookup will take inputs indirectly from any mapping. At the same time, unconnected will receive its inputs from lookup expression or some other transformation. Connected lookups will return multiple columns while unconnected one will return only one column from each row.

The below transformation is being used to check for existing records in the target table CL1_PA_HIST.

Transformations in Informatica

The ports that are being looked up are given in L Column. The ones which are checked are being looked up in the target. The conditions on these ports can also be specified in the condition tab. This can be done as below.

Transformations in Informatica

3. Router Transformation

The router transformation is active and connected. The router transformation is similar to filter transformation. It will consider the data which captures data where the condition does not match. In the filter condition, the data where the condition matches are considered. Router, on the other hand, has the data where the condition does not match are stored. It is useful to check multiple conditions. The below transformation can be used to insert and update records. This router is used to group the records based on the return value of lookup if the return value is not null and mark it as an update else insert.

The filter condition can be applied to groups. These can be done by going to the Groups tab as below:

 Router

Here the conditions are applied on four columns, and data is traversed ahead on these conditions as multiple conditions can also be specified. The update conditions are also specified in this transformation.

 Router

The data can be easily segregated and routed to the next transformations. There is also a default group that can have records sets that do not satisfy the condition.

4. Expression Transformation

This transformation can be used to calculate values present in a single row before it is passed on to the target. The transformation can also be used to pass through the data from one transformation to another. Expressions can be user-defined and be specified as per user.

The below example is of expression transformation, which sends the data to the next transformation. All ports are sent as it is.

Expression

5. Update Strategy Transformation

This transformation is used to update data that is present in the target table. This may include maintaining the history of data or data about changes that are done recently. It can also be specified which rows are to be treated to update source rows.

The below transformation Marks records for Update or Insert according to GG_OP_TYPE.

 Update Strategy

The strategy is mentioned in the update strategy expression.

Conclusion

Informatica has many transformations that help extract, transform, and load data to the target as per user needs. The interface provides is easy to use, and with basic knowledge of SQL data can be transformed and used in various reports and analysis. Informatica is a widely used tool because of the wide variety of transformations it provides. All transformations are beneficial and can be used as per the needs of the user.

Recommended Article

This has been a guide to Transformations in Informatica. Here we discuss an introduction to Transformations in Informatica and different types along with examples. You can also go through our other suggested articles to learn more –

  1. What are Informatica | Examples?
  2. Education to Careers In Informatica
  3. Informatica Scenario Based Interview Questions
  4. Informatica vs Datastage

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW