Tutorial corner

Informatica,ETL,oracle,sql/plsql

Sorter Transformation in Informatica with example

Spread the love

Sorter Transformation in Informatica is Active and connected transformation. You can sort data with the Sorter transformation. You can sort data in ascending or descending order according to a specified sort key. You can also configure the Sorter transformation for case-sensitive sorting, and specify whether the output rows should be distinct. The Sorter transformation is an active transformation. It must be connected to the data flow.

Why sorter transformation is an active transformation?

Sorter Transformation in informatica allows to sort data either in ascending or descending order according to a specified field. It changes the order of rows. We can also specify whether the output rows should be distinct or not. Hence with distinct properties enabled, it will change the no of rows. With all above reason, Sorter transformation is active transformation.

Is Sorter Transformation Connected or Unconnected?

Sorter Transformation is Connected Transformation.

More about sorter transformation in Informatica

You can sort data from relational or flat file sources. You can also use the Sorter transformation to sort data passing through an Aggregator transformation configured to use sorted input. When you create a Sorter transformation in a mapping, you specify one or more ports as a sort key and configure each sort key port to sort in ascending or descending order. You also configure sort criteria the Integration Service applies to all sort key ports and the system resources it allocates to perform the sort operation.

Check here for Aggregator transformation in informatica.

Below mapping that uses a Sorter transformation. The mapping passes rows from a sales table containing order information through a Sorter transformation before loading to the target.

Example of Sorter Transformation in Informatica

Example of Sorter Transformation in Informatica

Sorting Data in Informatica

The Sorter transformation contains only input/output ports. All data passing through the Sorter transformation is sorted according to a sort key. The sort key is one or more ports that you want to use as the sort criteria.

You can specify more than one port as part of the sort key. When you specify multiple ports for the sort key, the Integration Service sorts each port sequentially. The order the ports appear in the Ports tab determines the succession of sort operations. The Sorter transformation treats the data passing through each successive sort key port as a secondary sort of the previous port.

At session run time, the Integration Service sorts data according to the sort order specified in the session properties. The sort order determines the sorting criteria for special characters and symbols.

Below image shows the Ports tab configuration for the Sorter transformation sorting the data in ascending order by order ID and item ID.

Sorter Transformation Port configuration in Informatica

Sorter Transformation Port configuration in Informatica

At session run time, the Integration Service passes the following rows into the Sorter transformation.

ORDER_ID ITEM_ID QUANTITY DISCOUNT
45 123456 3 3.05
45 456789 3 12.98
43 000246 5 34.23
41 000468 6 3.44

After sorting the data, the Integration Service passes the following rows out of the Sorter transformation.

ORDER_ID ITEM_ID QUANTITY DISCOUNT
41 000468 6 3.44
43 000246 5 34.23
45 123456 3 3.05
45 456789 3 12.98

Check here for Transaction control transformation in informatica.

Sorter Transformation Properties

You need to set below Sorter Transformation properties in Informatica.

Sorter Transformation Properties Description
Case Sensitive The integration service considers the string case when sorting the data. The integration service sorts the uppercase characters higher than the lowercase characters
Work Directory The integration service creates temporary files in the work directory when it is sorting the data. After the integration service sorts the data, it deletes the temporary files
Distinct Output Rows The integration service produces distinct rows in the output when this option is configured
Tracing Level Configure the amount of data needs to be logged in the session log file
Null Treated Low Enable the property, to treat null values as lower when performing the sort operation. When disabled, the integration service treats the null values as higher than any other value
Sorter Cache Size The integration service uses the sorter cache size property to determine the amount of memory it can allocate to perform sort operation

Steps to add Sorter Transformation in informatica

You will need to follow below steps in order to add Sorter Transformation in Informatica mapping.

  1. In the Mapping Designer, click Transformation > Create. Select the Sorter transformation. The naming convention for Sorter transformations is SRT_TransformationName.
  2. Enter a description for the transformation. This description appears in the Repository Manager, making it easier to understand what the transformation does.
  3. Enter a name for the Sorter and click Create.
  4. The Designer creates the Sorter transformation.
  5. Click Done.
  6. Drag the ports you want to sort into the Sorter transformation. The Designer creates the input/output ports for each port you include.
  7. Double-click the title bar of the transformation to open the Edit Transformations dialogue box.
  8. Select the Ports tab.
  9. Select the ports you want to use as the sort key.
  10. For each port selected as part of the sort key, specify whether you want the Integration Service to sort data in ascending or descending order.
  11. Select the Properties tab. Modify the Sorter transformation properties.
  12. Select the Metadata Extensions tab. Create or edit metadata extensions for the Sorter transformation.
  13. Click OK.

I hope you enjoyed this informatica tutorial on sorter transformation in informatica. We discussed about importance and properties of Sorter transformation.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme