• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Rank Transformation in Informatica

What is Rank Transformation ?

The Rank Transformation in Informatica is an active, connected transformation used to select a bottom or top range of data. While string value ports can be ranked, the Informatica Rank Transformation is most commonly 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 ranked instead of a single value or record. The rank transformation is created with following types of ports.

Input port (I)
Output port (O)
Variable port (V)
Rank Port (R)

Rank Port

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

Variable Port

A  port which allows you to develop expression to store the data temporarily for rank calculation is known as a variable port. Variable port support to write expressions which are required for rank calculation.

Set the if properties to calculate the ranks

Top or bottom
Number of Rank’s

Ports in a Rank Transformation :

PortsNumber RequiredDescription
I1 MinimumPort to receive data from another transformation.
O1 MinimumPort we want to pass to other transformation.
Vnot neededcan use to store values or calculations to use in an expression.
ROnly 1Rank port. Rank is calculated according to it. The Rank port is an input/output port. We must link the Rank port to another transformation. Example: Total Salary

Configuring the Rank Transformation

Configure the following properties of Rank transformation

Cache Directory: Directory where the integration service creates the index and data cache files.

Top/Bottom: Specify whether you want to select the top or bottom rank of data.

Number of Ranks: specify the number of rows you want to rank.

Case-Sensitive String Comparison: Used to sort the strings using case sensitive or not.

Tracing Level: 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. You can set a numeric value or Auto for the data cache size. In 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. You can set a numeric value or Auto for the index cache size. In case of Auto, the Integration Service determines the cache size at runtime.

Learn more about Informatica Interview Questions in this blog post. Interested in mastering Informatica? 
Check out this blog post to learn more Informatica Training Videos

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. You can pass the rank index to another transformation in the mapping or directly to a target.

Advanced Properties for Rank Transformation

Top/Bottom – Specifies whether you want the top or bottom ranking for a column.
Number of Ranks – Number of rows to include in the top or bottom ranking.
Case-Sensitive – String Comparison Specifies whether the Data Integration Service uses case-sensitive string comparisons when it ranks strings. Clear this option to have the Data Integration Service ignore case for strings.
Cache Directory– Local directory where the Data Integration Service creates the index cache files and data cache files. Default is the CacheDir system parameter.
Rank Data Cache Size – Data cache size for the transformation. Default is Auto.
Rank Index Cache Size Index – cache size for the transformation. Default is Auto.
Tracing Level – Amount of detail that appears in the log for this transformation. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
Defining Groups
Like the Aggregator transformation, the Rank transformation lets you group information.
Example: If you want to select the 10 most expensive items by manufacturer, you would first define a group for each manufacturer.

Rank Transformation in Informatica with Examples

Procedure to create and configure Rank Transformation

In the Mapping Designer, open a Mapping.

Click Transformation > Create. Select Rank transformation.

Enter a name and click Done.

You will see one port RANK INDEX port already there. This port store the ranking of each  record  and can be used to populate target as well

Add all additional port from source input which are going to be used in the following transformation.

Open the port tab and first check the Group by option for desired column (for example dept no in our case)

Also check the Rank (R) option for the port which you want to do ranking. For example salary in our case.

RankTransformation

We can define Group by indicator for multiple port, but  Ranking can be done on single port only.

Go to the properties tab, select the Top/Bottom value as Top and the Number of Ranks property as per need.

RankTransformation-Example

Click OK.

Connect output ports of Rank Transformation to other transformation or target

For In-depth Knowledge on Informatica, click on below

Summary
Review Date
Reviewed Item
Rank Transformation in Informatica
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

1 Responses on Rank Transformation in Informatica"

  1. garima says:

    good explanation!! Thanks…

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.

Support


Please leave a message and we'll get back to you soon.

I agree to be contacted via e-mail.