79

Informatica Transformations

Informatica Transformations are repository objects which can create, read, modifies, or passes data to the defined target structures such as tables, files, or any other targets.

In Informatica, the purpose of transformation is to modify the source data according to the requirement of the target system. It also ensures the quality of the data being loaded into the target.

A Transformation is used to represent a set of rules, which define the data flow and how the data is loaded into the targets.

Informatica provides multiple transformations to perform specific functionalities.

In transformations, To passing the data, we need to connect the ports to it, and through the output ports, it returns the output.

Classification of Transformation

Transformation is classified into two categories-the first one based on connectivity and second based on the change in several rows. First, we will look at the transformation based on connectivity.

1. Here are two types of transformation based on connectivity, such as:

• Connected Transformations
• Unconnected Transformations

In Informatica, one transformation is connected to other transformations during mappings are called connected transformations.

For example, Source qualifier transformation of Source table Stud is connected to filter transformation to filter students of a class.

Those transformations whose not link to any other transformations are called unconnected transformations.

Their functionality is used by calling them inside other transformations. And these transformations are not part of the pipeline.

The connected transformations are preferred when the transformation is called for every input row or expected to return a value.

The unconnected transformations are useful if their functionality is required periodically only or based upon certain conditions. For example, calculate the tax details if tax value is not available.

1. Here are two types of transformations based on the change in several rows, such as:

• Active Transformations
• Passive Transformations

Active Transformations are those who modify the data rows, and the number of input rows passed to them. For example, if a transformation receives 10 numbers of rows as input, and it returns 15 numbers of rows as an output, then it is an active transformation. In the active transformation, the data is modified in the row.

Passive Transformations do not change the number of input rows. In passive transformations, the number of input and output rows remains the same, and data is modified at row level only.

In the passive transformation, we cannot create new rows, and no existing rows dropped.

List of 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

What is Filter Transformation?

Filter Transformation is an active transformation because it changes the number of records. We can filter the records according to the requirements by using the filter condition.

For example, for loading the student records having rollno equal to 20 only, we can put filter transformation in the mapping with the filter condition rollno=20. So only those records which have rollno =20 will be passed by filter transformation, rest other records will be dropped.

Step 1: Create a mapping having source “Stu” and target “Stu_target”.

Step 2: Then in the mapping

1. Select Transformation menu
2. Select the create option.

Step 3: In the create transformation window

1. Select Filter Transformation from the list.
2. Enter Transformation name fltr_rollno_20
3. Select create option

Step 4: The filter transformation will be created, click on the Done button in the creative transformation window.

Step 5: in the mapping,

1. Drag and drop all the source qualifier columns from the filter transformation.
2. And Link the columns of filter transformation to the target table.

Step 6: Double click on the filter transformation to open its properties, and

1. Select the properties menu.
2. Click on the filter condition editor.

Step 7: Then,

1. Enter filter condition rollno=20.
2. Click on the OK button.

Step 8: Again in the edit transformation window,

1. We will see the filter condition in the properties tab.
2. Click on the OK button.

Now save the created mapping and execute this after creating session and workflow. In the target table, only the rollno = 20 will be loaded from the record.

---