Sorter Transformation in Informatica

  • Share this blog:

Sorter Transformation in Informatica

Sorter transformation in Informatica is used to sort the data in an ascending or descending order based on single or multiple keys. This is the type an active transformation which sorts the data either in ascending order or descending order. The port which is participated in sor operation is defined as "key". Sorter transformation is used for "eliminating duplicates" hence the sorted transformation is called as active transformation the sorter functions as "order by clause" in SQL.

A sample mapping showing Sorter transformation is displayed in the below screenshot.

Informatica - Sorter Transformation Example

In this mapping, we wish to sort the data based on the DEPARTMENT_ID field. To achieve this, mark the key port for the DEPARTMENT_ID columns in the Sorter transformation and select from the drop-down list what you wish to have as the Ascending or Descending sorting, as shown in the below screenshot

Informatica - Ascending or Descending Transformation


Informatica Sorter transformation properties

Sorter Cache Size: The Integration Service uses the Sorter Cache Size property to determine the maximum amount of memory it can allocate to perform the sort operation. The Integration Service passes all incoming data into the Sorter transformation before it performs the sort operation. You can configure a numeric value for the Sorter cache, or you can configure the Integration Service to determine the cache size at run time. If you configure the Integration Service to determine the cache size, you can also configure a maximum amount of memory for the Integration Service to allocate to the cache.

If the Integration Service runs a partitioned session, it allocates the specified amount of Sorter cache memory for each partition.
If it cannot allocate enough memory, the Integration Service fails the session.
Sorter cache size is set to 16,777,216 bytes by default.

If you want to enrich your career and become a professional in Informatica, then visit Tekslate - a global online training platform: "Informatica Training" This course will help you to achieve excellence in this domain.

Case Sensitive: When you enable the Case Sensitive property, the Integration Service sorts upper case characters higher than lower case characters
Work Directory: You must specify a work directory the Integration Service uses to create temporary files while it sorts data. After the Integration Service sorts the data, it deletes the temporary files. By default, the Integration Service uses the value specified for the $PMTempDir process variable.
Distinct Output Rows: You can configure the Sorter transformation to treat output rows as distinct. You can configure the Sorter transformation to treat output rows as distinct.
Tracing Level: Configure the Sorter transformation tracing level to control the number and type of Sorter error and status messages the Integration Service writes to the session log.
Null Treated Low: Enable this property if you want the Integration Service to treat null values as lower than any other value when it performs the sort operation. Disable this option if you want the Integration Service to treat null values as higher than any other value.
Transformation Scope
Transaction Applies the transformation logic to all rows in a transaction. Choose Transaction when a row of data depends on all rows in the same transaction but does not depend on rows in other transactions.
All Input. Applies the transformation logic on all incoming data. When you choose All Input, the PowerCenter drops incoming transaction boundaries. Choose All Input when a row of data depends on all rows in the source.
Read about Source Qualifier Transformations here.
About Author
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.