Sorter Transformation

By Unknown - Friday, May 20, 2016 - No Comments

Sorter Transformation in Informatica

Sorter transformation is an active and connected transformation used to sort the data. The data can be sorted in ascending or descending order by specifying the sort key. You can specify one or more ports as a sort key and configure each sort key port to sort in ascending or descending order. You can also configure the order of the ports in which the integration service applies to sort the data.

The sorter transformation is used to sort the data from relational or flat file sources. The sorter transformation can also be used for case-sensitive sorting and can be used to specify whether the output rows should be distinct or not.

When we create a Sorter transformation in a mapping, we specify one or more ports as a sort key and configure each sort key port to sort in ascending or descending order. We also configure sort criteria the Power Center Server applies to all sort key ports and the system resources it allocates to perform the sort operation.

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 we want to use as the sort criteria.

Sorter Transformation Properties

1. Sorter Cache Size:

The Power Center Server uses the Sorter Cache Size property to determine the maximum amount of memory it can allocate to perform the sort operation. The Power Center Server passes all incoming data into the Sorter transformation Before it performs the sort operation.

We can specify any amount between 1 MB and 4 GB for the Sorter cache size.
If it cannot allocate enough memory, the Power Center Server fails the Session.
For best performance, configure Sorter cache size with a value less than or equal to the amount of available physical RAM on the Power Center Server machine.
Informatica recommends allocating at least 8 MB of physical memory to sort data using the Sorter transformation.

2. Case Sensitive:

The Case Sensitive property determines whether the Power Center Server considers case when sorting data. When we enable the Case Sensitive property, the Power Center Server sorts uppercase characters higher than lowercase characters.

3. Work Directory

Directory Power Center Server uses to create temporary files while it sorts data.

4. Distinct:

Check this option if we want to remove duplicates. Sorter will sort data according to all the ports when it is selected.

Example: Sorting data of EMP by ENAME

Source is EMP table.
Create a target table EMP_SORTER_EXAMPLE in target designer. Structure same as EMP table.
Create the shortcuts in your folder.

Creating Mapping:

1. Open folder where we want to create the mapping.

2. Click Tools -> Mapping Designer.

3. Click Mapping-> Create-> Give mapping name. Ex: m_sorter_example

4. Drag EMP from source in mapping.

5. Click Transformation -> Create -> Select Sorter from list. Give name and click Create. Now click done.

6. Pass ports from SQ_EMP to Sorter Transformation.

7. Edit Sorter Transformation. Go to Ports Tab

8. Select ENAME as sort key. CHECK mark on KEY in front of ENAME.

9. Click Properties Tab and Select Properties as needed.

10. Click Apply -> Ok.

11. Drag target table now.

12. Connect the output ports from Sorter to target table.

13. Click Mapping -> Validate

14. Repository -> Save

Create Session and Workflow as described earlier. Run the Workflow and see the data in target table.
Make sure to give connection information for all tables.

Sample Sorter Mapping :

Creating Sorter Transformation

Follow the below steps to create a sorter transformation

In the mapping designer, create a new mapping or open an existing mapping
Go the toolbar->Click on Transformation->Create
Select the Sorter Transformation, enter the name, click on create and then click on Done.
Select the ports from the upstream transformation and drag them to the sorter transformation. You can also create input ports manually on the ports tab.
Now edit the transformation by double clicking on the title bar of the transformation.
Select the ports you want to use as the sort key. For each selected port, specify whether you want the integration service to sort data in ascending or descending order.

Configuring Sorter Transformation

Configure the below properties of sorter transformation

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

Performance Tuning:

Sorter transformation is used to sort the input data.

While using the sorter transformation, configure sorter cache size to be larger than the input data size.
Configure the sorter cache size setting to be larger than the input data size while Using sorter transformation.
At the sorter transformation, use hash auto keys partitioning or hash user keys Partitioning.

Sorter Transformation Examples

1. Create a mapping to sort the data of employees on salary in descending order?

2. Create a mapping to load distinct departments into the target table?

News Ticker

Sorter Transformation

Sorter Transformation in Informatica

Share This:

Post Tags:

Tharun Katanguru

Social Links:

No Comment to " Sorter Transformation "