What is Transformation?
Transformations in Informatica are the objects which create, modify or pass data to the defined target structures (tables, files, or any other target).
The purpose of the transformation in Informatica is to modify the source data as per the requirement of the target system. It also ensures the quality of the data being loaded into the target.
Informatica provides various transformations to perform specific functionalities.
For example, performing tax calculations based upon source data, data cleansing operations, etc. In transformations, we connect the ports to pass data to it, and transformation returns the output through output ports.
What Are Data Transformations?
Data transformations are the core operations performed on
data during the ETL (Extract, Transform, Load) process. They involve changing
the structure, format, or values of data to meet the desired output
requirements.
Significance of Transformations
Transformations are vital for several reasons:
Filter Transformation: Unearthing Data Precision
What Is a Filter Transformation?
A Filter Transformation is a specific type of transformation
in Informatica that does exactly what its name suggests - it filters data. It's
like a gatekeeper, allowing only the data that meets certain conditions or
criteria to pass through while discarding the rest.
Working of Filter Transformation
Filter Transformations work based on predefined conditions,
also known as filter expressions. These expressions determine which rows of
data will be allowed to flow through the transformation, and which will be filtered
out. This is particularly useful when you need to extract specific subsets of
data from a larger dataset.
Use Cases of Filter Transformation
Filter Transformations find applications in various
scenarios:
Implementing Filter Transformation in Informatica
Configuration and Customization
To utilize Filter Transformations in Informatica, you need
to configure them within your mapping. You define the filter conditions using
SQL-like expressions, and only rows satisfying these conditions will be
included in the output.
Performance Considerations
Efficient use of Filter Transformations is crucial for maintaining optimal performance. Overly complex filter conditions or applying them too early in the transformation process can impact performance negatively.
Classification of Transformation
Transformation is classified into two categories, one based on connectivity, and the other based on the change in the of rows. First, we will look at the transformation based on connectivity.
Types of transformation based on connectivity
Connected Transformations
Unconnected Transformations
In Informatica, during mappings, the transformations which are connected to other transformations are called connected transformations.
For example, Source qualifier transformation of Source table EMP is connected to filter transformation to filter employees of a dept.
Those transformations that are not connected to any other transformations are called unconnected transformations.
Their functionality is used by calling them inside other transformations like Expression transformation. These transformations are not part of the pipeline.
The connected transformations are preferred when for every input row, a transformation is called or is expected to return a value. For example, for the zip codes in every row, the transformation returns the city name.
The unconnected transformations are useful when their functionality is only required periodically or based upon certain conditions. For example, calculate the tax details if the tax value is not available.
Types of transformations based on the change in the rows
Active Transformations
Passive Transformations
Active Transformations are those that modify the data rows and the number of input rows passed to them. For example, if a transformation receives ten rows as input, and it returns fifteen rows as an output then it is an active transformation. The data in the row is also modified in the active transformation.
Passive transformations are those that do not change the number of input rows. In passive transformations the number of input and output rows remain the same, only data is modified at row level.
In the passive transformation, no new rows are created, or existing rows are dropped.
Following is the 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 as it changes the no of records.
Using the filter transformation, we can filter the records based on the filter condition. Filter transformation is an active transformation as it changes the no of records.
For example, for loading the employee records having deptno equal to 10 only, we can put filter transformation in the mapping with the filter condition deptno=10. So only those records which have deptno =10 will be passed by filter transformation, the rest other records will be dropped.
How to use filter transformation-
Step 1 – Create a mapping having source “EMP” and target “EMP_TARGET”
Step 2 – Then in the mapping
Select Transformation menu
Step 3 – Then in the creative transformation window
Select Filter Transformation from the list
Enter Transformation name “fltr_deptno_10”
Step 4 – The filter transformation will be created, select the “Done” button in the creative transformation window
Step 5 – In the mapping
Drag and drop all the Source qualifier columns to the filter transformation
Link the columns from filter transformation to the target table
Step 6 – Double click on the filter transformation to open its properties, and then
Select the properties menu
Click on the Filter condition editor
Step 7 – Then in the filter condition expression editor
Enter filter condition – deptno=10
Select OK button
Step 8 – Now again in the edit transformation window in the Properties tab you will see the filter condition, select the OK button
Now save the mapping and execute it after creating the session and workflow. In the target table, the records having deptno=10 only will be loaded.
In this way, you can filter the source records using filter transformation.
Rank Transformation in Informatica |
Joiner transformation in Informatica |
Router Transformation in Informatica |
Aggregator Transformation in Informatica |