Informatica Common components aggregator three using sort inputs

You can use the Sort input option to improve aggregate conversion performance. When you use sort input, PowerCenter assumes that all data is sorted by group. When PowerCenter reads a set of rows, it performs an aggregation calculation. When needed, it stores group information in memory. To use the Sort input option, you must pass the sorted data to the aggregation transformation. When you configure a session with multiple partitions, you can improve performance through the sorted ports.

When you do not use sort input, PowerCenter performs an aggregation calculation of its reads. However, because the data is not sorted, PowerCenter will store the data for each group until it reads the entire source, ensuring that all aggregation calculations are accurate.

For example, the aggregation transformation has a store_id and ITEM group by port with the selected sorted input option. When the following data is passed by aggregation, once PowerCenter finds a new group 201/battery, the aggregation is performed on the 101/battery for three rows:

If you use sort input and do not pre-sort the data correctly, you will receive unexpected results.

Sort input criteria

Do not use sort input if any of the following conditions are true:

• An aggregate expression uses nested aggregate functions.

• The session uses an incremental aggregation.

Pre-sorted data

  To use sort input, the sorted data should be passed by aggregation.

The data must be sorted as follows:

• Aggregation groups are based on the order of the ports, in the order in which they appear in the aggregation transformation.

• Use the order in which the sessions are configured in a consistent sequence. If the data is not strictly ascending or descending based on the sort of session, PowerCenter terminates the session. For example, if you configure a session to use French sorting, you must use the French sort arrangement to pass data to the aggregation transformation.

For a relationship or file source, you can use the sequencer to transform the data in the sort map before passing the data to the aggregation transformation. If there is no conversion to change the order of sorted data, you can place the Sequencer transformation anywhere in the map before aggregating. The order in which columns are grouped in the aggregation transformation must be in the same order as they appear in the sequencer transformation.

If the session uses a relationship source, you can also sort the group by column in the source database by using the sorted ports option in the Source qualifier transformation. The GROUP By column must be in the same order in the aggregation and source qualifier conversions.

Informatica Common components aggregator three using sort inputs