78

Rank Transformation

Rank is an active and connected transformation that performs the filtering of data based on the group and ranks. The rank transformation also provides the feature to do ranking based on groups.

The rank transformation has an output port, and it is used to assign a rank to the rows.

In Informatica, it is used to select a bottom or top range of data. While string value ports can be ranked, the Informatica Rank Transformation is used to rank numeric port values. One might think MAX and MIN functions can accomplish this same task.

However, the rank transformation allows groups of records to be listed instead of a single value or record. The rank transformation is created with the following types of ports.

1. Input port (I)
2. Output port (O)
3. Variable port (V)
4. Rank Port (R)

Rank Port

The port which is participated in a rank calculation is known as Rank port.

Variable Port

A port that allows us to develop expression to store the data temporarily for rank calculation is known as a variable port.

The variable port will enable us to write expressions that are required for rank calculation.

Ports in a Rank Transformation

Ports	Number Required	Description
I	1 Minimum	Port to receive data from another transformation.
O	1 Minimum	Port we want to pass to other transformations.
V	not needed	It is used to store values or calculations for use in an expression.
R	Only 1	The Rank port is an input or output port. We have linked the Rank port to another transformation. For example: Total Salary

Configuring the Rank Transformation

Let’s see how to configure the following properties of Rank transformation:

• Cache Directory: The directory is a space where the integration service creates the index and data cache files.
• Top/Bottom: It specifies whether we want to select the top or bottom rank of data.
• Number of Ranks: It specifies the number of rows that we want to rank.
• Case-Sensitive String Comparison: It is used to sort the strings by using the case sensitive.
• Tracing Level: The amount of logging to be tracked in the session log file.
• Rank Data Cache Size: The data cache size default value is 2,000,000 bytes. We can set a numeric value or Auto for the data cache size. In the case of Auto, the Integration Service determines the cache size at runtime.
• Rank Index Cache Size: The index cache size default value is 1,000,000 bytes. We can set a numeric value or Auto for the index cache size. In the case of Auto, the Integration Service determines the cache size at runtime.

What is Rank Index?

The Developer tool creates a rank index port for each Rank transformation. The Data Integration Service uses the Rank Index port to store the ranking position for each row in a group.

After the Rank transformation identifies all rows that belong to a top or bottom rank, it then assigns rank index values. If two rank values match, they receive the same value in the rank index, and the transformation skips the next value.

The rank index is an output port only. We can pass the rank index to another transformation in the mapping or directly to a target.

Defining Groups

The Rank transformation gives us group information like the aggregator transformation.

For example: If we want to select the 20 most expensive items by manufacturer, we would first define a group for each manufacturer.

Example

Suppose we want to load top 5 salaried employees for each department; we will implement this using rank transformation in the following steps, such as:

Step 1: Create a mapping having source EMP and target EMP_TARGET

Step 2: Then in the mapping,

1. Select the transformation menu.
2. And click on the Create option.

Step 3: In the create transformation window,

1. Select rank transformation.
2. Enter transformation name “rnk_salary”.
3. And click on the Create button.

Step 4: The rank transformation will be created in the mapping, select the done button in the window.

Step 5: Connect all the ports from source qualifier to the rank transformation.

Step 6: Double click on the rank transformation, and it will open the “edit transformation window”. In this window,

1. Select the properties menu.
2. Select the “Top” option from the Top/Bottom property.
3. Enter 5 in the number of ranks.

Step 7: In the “edit transformation” window again,

1. Select the ports tab.
2. Select group by option for the Department number column.
3. Select Rank in the Salary Column.
4. Click on the OK button.

Step 8: Connect the ports from rank transformation to the target table.

Now, save the mapping and execute it after creating session and workflow. The source qualifier will fetch all the records, but the rank transformation will pass only records having three high salaries for each department.

---