Transformations in Informatica with Example

 

Overview of Transformations in Informatica with Example

These days data is the most important part of any industry. This data can be unstructured and raw. In addition to this, you need to cleanse and transform the data as per user requirements. Transformations in Informatica are objects that create. Modify or pass data to a defined target. The source data can be modified as per the target system’s requirement. These transformations in Informatica are classified into connected and unconnected transformations. When a transformation is connected to some other transformation then it is connected and when it is a standalone transformation then it is unconnected. There are two types of transformations in Informatica that are active and passive. Let us have a look at these with examples.

Transformations in Informatica with Examples

Active transformations in Informatica are those that modify rows and number of input rows while passive ones do not change a number of input rows and no new rows are created or existing dropped.

Following are the types of active transformations in Informatica:

• 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

1. Source Qualifier Transformation

This transformation is active and connected. It is used to represent rows the read integration service. When a flat file or relational data source is involved at that time Source Qualifier Transformation comes into the picture. With this transformation how data is fetched and overridden can be defined.

Example: Modify source qualifier mapping ‘m_student_target’ to return only selected columns.

Step 1: Open mapping ‘m_student_target’ in mapping designer.

Step 2: Double-click on the source Qualifier transformation “SQ_STUD”. It will open edit transformation property window for it. Then

1. Click on the properties tab
2. Click on the SQL Query Modify option, this will open an SQL editor window

Step 3: In the SQL editor window enter the following query-

SELECT ROLLNO, STUDNAME, DEPT, HOD FROM STUD and click on OK.

Step 4: Go to “edit transformations” window,

Select the Ports tab from the menu

Under the ports tab, you will see all the ports. Keep only the ports ROLLNO, STUDNAME, DEPT, HOD and delete other ports. Once you delete these ports click on OK.

Step 5: Go to edit transformation again and you will be able to confirm that the data selected is correct and ready to load.

2. Aggregator Transformation

This transformation is used when a user wants to perform aggregate functions like sum, average, etc.

Example: To calculate the average marks of students department wise.

Step 1: Create a new database target table, for example, say “avg_mks_deptwise”

Step 2: Create a New mapping “m_ avg_mks_deptwise “.

In order to create a new mapping, we need a source table (STUD) and target table (avg_mks_deptwise) both in mapping designer for that we need to

1. Import the target table ” avg_mks_deptwise ” in the mapping.
2. Import the source table “STUD”

Step 3: In mapping, delete all columns other than marks and department. Now, create a new aggregator transformation using a toolbox menu. Click on the Aggregator icon and a new transformation will be created.

Step 4: Drag and drop marks & deptno columns from source qualifier (SQ_STUD) to the aggregator transformation

Step 5: Double-click on the aggregator transformation to open its properties, and then

1. Add a new port in the transformation
2. Rename the port name to AVG_MKS
3. Change the data type of this new port to double
4. Make this port as an output port by selecting the checkbox of the output port.
5. Click on the expression option

Step 6: In the expression window

1. Add expression- avg (MKS)
2. Select Ok Button, this will bring back the edit transformation window.

Step 7: In the edit transformation window, select the option “GroupBy” by marking the checkbox against the deptno column and Click Ok. Link deptno and avg_mks by using aggregator transformation.

3. Router Transformation

Router transformation is used to filter source data. It also enables to include discarded data in the mapping. In addition to these multiple filters, conditions can be applied to multiple data sets.

Example:

Step 1: Create a mapping having source “STUD” and target “STUD_TARGET.”

Step 2: Create a new transformation and in create window do below:

1. Select router transformation
2. Enter a name for the transformation “rtr_deptno_10”
3. Select Create option

Router transformation will be created in mapping.

Step 3: Drag and drop all columns from source qualifier to router transformation.

Step 4: Double-click on the router transformation, then in the transformation property of it.

1. Select the group tab and enter the group name as “deptno_20” and click on the filter.
2. Go to expression editor and filter deptno_20 and select OK.

Step 5: Connect the ports from the group deptno_20 of router transformation to target table ports

4. Joiner Transformation

This transformation is used when you want to create joins. The main advantage of these joins is that heterogeneous databases can be used to create these joins. Joins like master outer join, detail outer join, fuller outer join and normal join can be created.

Example: Join Stud table and Dept table.

Step 1: Create new mapping and import source tables Stud and Dept and target tables.

Step 2: Go to the transformation menu and select the create option. Enter transformation name ‘stud_jn_dept’ and choose to create an option.

Step 3: Drag and drop all the columns from both the source qualifiers to the joiner transformation

Step 4: Double click on joiner transformation and select condition tab. Click on add new condition icon and then select deptno in master and details columns list.

Step 5: Now go to the properties tab and select normal join and click OK.

Step 6: Once this is done then go to the ports tab and select any column as a particular source and then select OK. Link all columns from joiner transformation.

5. Transaction Control Transformation

This transformation allows committing and rollback transactions during execution of any mapping. There are five variables to handle these operations. These are TC_CONTINUE_TRANSACTION, TC_COMMIT_BEFORE, TC_COMMIT_AFTER, TC_ROLLBACK_BEFORE, and TC_ROLLBACK_AFTER.

Example: Commit data for department no 20

Step 1: Create a new transformation and then select transaction control. Enter a valid name like ‘tc_commit_dept20’ and create.

Step 2: Drag and drop all the columns from source qualifier to the transaction control transformation then link all the columns from transaction control transformation to the target table.

Step 3: Select property tab and and click on transaction control. Enter the expression as:

“iif(deptno=20,tc_commit_before,tc_continue_transaction)” and select OK.

Once this is done save the mapping.

Conclusion – Transformations in Informatica with example

Transformations in Informatica hence help data transformations and processing easier. With so many transformation options to provide Informatica will help you with your data in the best way.

Recommended Articles

This has been a guide to Transformations in Informatica with example. Here we have discussed the concepts and Different transformation in Informatica like aggregate, joiner, router, source qualifier, etc. You may also look at the following article to learn more –

1. Informatica Scenario based Interview Questions
2. Talend Vs Informatica PowerCenter
3. All Important Things About Informatica Developer Tool
4. What is Informatica Architecture?
5. Introduction to Transformations in Informatica
6. Informatica ETL Tools
7. Talend Tools