Sorter Transformation in Informatica

Sorter Transformation:

This is one of the most frequently used transformation in Informatica during the mapping design.

• It is active and connected transformation.
• It is used to sort the input data either in ascending order or in descending order based on the key port.
• Key port is also called as sorted port.
• 'Sorter Transformation' allows multiple key ports.
• Sorter Transformation in Informatica sorts the data based on the order of the ports when multiple keyports are selected.
• It is also used to eliminate the duplicate records from different type of sources and other transformations also.

Sorter Transformation is active or passive:

Without any doubt, we can say that the Sorter Transformation is Active because it can remove the duplicate records. When the duplicates records were removed from the sources or from any other transformations then definitely there will be changes in the total number of rows as there will be reduction in the total number of records. Hence, we can say that sorter transformation as active. Select 'Distinct' Property in the sorter transformation to eliminate the duplicates.

Sorter Cache in Informatica:

In case of Sorter Transformation, Informatica Server builds a cache which is called as sorter cache(cache memory) at the time of execution. It loads all the input data into sorter cache and performs sorting based on key column and sends the output. It releases the cache once the sorting is done. The following transformations in Informatica require cache memory at the time of execution.

• Sorter Transformation
• Aggregator Transformation
• Rank Transformation
• Joiner Transformation
• Lookup Transformation

For the other transformations like Router Transformation or any other cache will not be created.
Sorter Transformation is created with the following 3 types of ports.

1. Input Port
2. Output Port
3. Key Port

Informatica Scenarios using Sorter Transformation:

 

Import the emp table as our source first using the source analyzer and then follow the below steps.

1. Create a target with the name emp_sort with the same structure as source(emp)
2. Create a mapping with the name m_sorter
3. Drag the source (emp)
4. Drag the target (emp_sort)
5. Create a transformation of type sorter with the name sort_sal and then Connect the ports from source qualifier to the Sorter Transformation
6. Go to the header of sorter and then select ports tab.
7. Select the key for the required ports on salary and then apply and ok
8. Connect the ports from the sorter to target.
9. Save the mapping, session and run the workflow.

How to display null values at bottom in Sorter Transformation:

• By default, Informatica Server displays null values first while sorting the data.
• Go to the header of the sorter and select the ports tab.
• Select keyports for the comm and set direction as descending.
• Select properties tab and set the property as Null Treated Low  and then save the mapping.

Performance Tuning in Sorter Transformation:

There are very  less chances to discuss about this topic in the Informatica Interviews. As we know that sorting can also be done at the database level itself. If we use sorter immediately after the relational source, it is better to do the sorting in the database. Because sorting in Informatica takes long time when compared to the sorting in the Relational databases like Oracle, Mysql, SQL Server. If the source is a flat file then there is no other option. At that situation, we have to this transformation only. One more Performance tuning method is that to use this transformation as close to the source. So that you can remove the records which are having the duplicates at the initial stages itself.