Tutorial corner

Informatica,ETL,oracle,sql/plsql

Rank Transformation in Informatica with Example

Spread the love

Rank Transformation in Informatica, is a connected and active transformation which select top/bottom rows of   input. It is something similar to Rank analytical data function or oracle. Only difference is that, it also filter out the remaining rows  (which are not a part of top/bottom threshold). In below article we will go through the properties of Rank Transformation. We will also discuss the steps of adding /configuring Rank  transformation in Informatica Mapping. We will also check  some performance tuning related guidelines for Rank Transformation.

Scenarios of Rank Transformation in Informatica

Rank transformation can be used to filter top /bottom records depending upon as per our need.

How Rank Transformation works in Informatica ?

It first cache all the input data and then performs the rank calculation per group , filter out the unwanted records.

Is Rank Transformation Active or Passive ?

Rank Transformation is an active transformation as it is changing the no of output row (by keeping only top/ bottom records)

Rank Transformation Components

When you configure a Rank transformation, define the following components:

  • Transformation tab : You can rename the transformation and add a description.
  • Properties tab : You can specify the tracing level.
  • Port tab : You can define input/output port here. Variable port can also be created. Most important port is Rank port which define the basis of ranking

Ports in Rank Transformation:

Ports
Number Required
Description
I
Minimum of one
Input port. Create an input port to receive data from another transformation.
O
Minimum of one
Output port. Create an output port for each port you want to link to another transformation. You can designate input ports as output ports.
V
Not Required
Variable port. Can use to store values or calculations to use in an expression. Variable ports cannot be input or output ports. They pass data within the transformation only.
R
One only
Rank port. Use to designate the column for which you want to rank values. You can designate only one Rank port in a Rank transformation. The Rank port is an input/output port. You must link the Rank port to another transformation. Example : Salary port in Employee data can be used as Ranking port

Configuring Rank Transformation Ports in Informatica

You can create input ports by copying them from another transformation or by manually creating them on the Ports tab.

Port Details:

  • Port name:  Add the name of the port.
  • Datatype, precision, and scale:  Configure the datatype and set the precision and scale for each port.
  • Default Value: In case port data is null , you can define its default value..

Configuring Rank Transformation Properties:

Modify the Rank Transformation properties by clicking on the Properties tab.

Property Description
Cache Directory  Local directory where the Integration Service creates the index and data cache files. By default, the Integration Service uses the directory entered in the Workflow Manager for the process variable $PMCacheDir. If you enter a new directory, make sure the directory exists and contains enough disk space for the cache files.
Top/Bottom  Specifies whether you want the top or bottom ranking for a column.
Number of Ranks  Number of rows you want to rank.
Case-Sensitive String
Comparison
When running in Unicode mode, the Integration Service ranks strings based on the sort order selected for the session. If the session sort order is case sensitive, select this option to enable case-sensitive string comparisons, and clear this option to have the Integration Service ignore case for strings.If the sort order is not case sensitive, the Integration Service ignores this setting. By default, this option is selected.
Tracing Level Amount of detail displayed in the session log for this transformation. The options are Terse, Normal, Verbose Data, and Verbose Initialization.Default value : Normal.
Rank Data Cache Size Data cache size for the transformation. Default is 2,000,000 bytes. If the total configured session cache size is 2 GB (2,147,483,648 bytes) or more, you must run the session on a 64-bit Integration Service. You can configure a numeric value, or you can configure the Integration Service to determine the cache size at runtime.If you configure the Integration Service to determine the cache size, you can also configure a maximum amount of memory for the
Integration Service to allocate to the cache.
Rank Index Cache Size Index cache size for the transformation. Default is 1,000,000 bytes. If the total configured session cache size is 2 GB (2,147,483,648 bytes) or more, you  must run the session on a 64-bit Integration Service. You can configure a numeric value, or you can configure the Integration Service to determine the cache size at run time .If you configure the Integration Service to determine the cache size, you can also configure a maximum amount of memory for the Integration Service to allocate to the cache.
Transformation Scope Specifies how the Integration Service applies the transformation logic to incoming data:
Transaction : Applies the transformation logic to all rows in a transaction. Choose Transaction when a row of data depends on all rows in the same transaction, but does not depend on rows in other transactions.
All Input : Applies the transformation logic on all incoming data. When you choose All Input, the PowerCenter drops incoming transaction boundaries. Choose All Input when a row of data depends on all rows in the source.

Check here for Informatica Normalizer transformation

How to use Rank transformation in Informatica ?

Use the following procedure to create a Rank transformation in Informatica mapping.

Steps to create an 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 use in following transformation.
  • Open the port tab and first check the Group by option for desired column ( for example deptno in our case)

    Rank Transformation in informatica -Part-1

    Rank Transformation in informatica -Part-1

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

Note: 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.
  • Rank Transformation Part 2

    Rank Transformation Part 2

  • Click OK.
  • Connect output ports of Rank Transformation to other transformation or target

Example  of Rank Transformation:

Check here for rank transformation example in informatica

Rank Transformation rules and Guidelines:

  • While using Rank transformation on string :-
    • When the Integration Service runs in the ASCII data movement mode, it sorts session data using a binary sort order.
    • When the Integration Service runs in Unicode data movement mode, the Integration Service uses the sort order configured for the session.You select the session sort order in the session properties. The session properties lists all available sort orders based on the code page used by the Integration Service.
  • Index cache stores the column where we have put rank and group by and rest of all columns will be in data cache
  • The Designer creates a RANKINDEX port for each Rank transformation . The Integration Service uses the Rank Index port to store the ranking position for each row in a group

So , in this article we have gone through details of Rank Transformation in Informatica , component/Properties of Rank Transformation in Informatica , scenarios where to use it , steps to create it and related rules and guidelines.

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