Rank Transformation in Informatica

• Active Transformation.

• Used to seek the position (at which position the data is).

• We can see top or bottom positions.

• By default, rank index port name is created along with the transformation object (To carry the rankings – rank index is prepared).


Ports:

Input
Output
Expression
Variable
Rank
Group-By

Steps:
1. Define the Source Definition (EMP).

2. Define the Target Definition.

How to Define the Target Table?

1. Go to Ware House Designer create a target table same as the source (EMP) by dragging and dropping the source definition.

2. Edit EMP source definition. Go to the Table tab of Edit and rename it to DIM_RANK.

Now go to the Columns tab and add a new column RANKINDEX and click Apply, then OK.

Till now the new table Dim_RANK is logically present in the WareHouse. 

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

DIM_EMP                        TABLE

DIM_EMP1                      TABLE

To make it available physically go to the Targets Menu and click on Generate/Execute SQL.

Click on Connect button.

Give the DSN, Username and Password of the Target.

Click the radio button Generate from Selected Tables , check the box Create Table.

Click Generate SQL file and the Close.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

DIM_EMP                        TABLE

DIM_EMP1                       TABLE

DIM_RANK                     TABLE

Note that DIM_RANK now physically belongs to the Schema DWH_MEEN.

Define the mapping by dragging the source and target. Create a RANK Transformation T_RANK.

By default a RANKINDEX column is there in the blank transformation object.

Project the required ports from the Source Qualifier to the transformation object.
Edit Rank Transformation.

Go to the Ports Tab and apply RANK on Salary Check the R box. Apply Group By on the department no. 

Click on Apply and then OK.

Now in the Properties Tab 

Set Top/Botton to Top and Number of Ranks to 2. 

(It will fetch us the top 2 salaries)

RANK also uses CACHE

Now we have selected department wise top two salaries, but our objective is to fetch the second highest salary so we will apply a fliter with the condition RANKINDEX = 2.

Create a Filter Transformation. Project the required ports from RANK to FILTER.

Edit Filter – In the Properties Tab apply the condition RANKINDEX = 2.

Apply -> OK

Project the required ports from the Filter to the Target.

SQ -> T_RANK -> T_FIL -> DIM_RANK.

(To increase the performance, use SORTER Transformation before T_RANK)

Create a Session

Create a Workflow

Repository _> Save

Start Workflow

 

We encounter an error.

Edit the Session.

In the Mappings Tab, select the target DIM_RANK. In the Properties, Set Target Load Type as Normal.

Repository -> Save

Start the Workflow Again.

Now the load is successful.