- Rank Transformation in Informatica.
- Create Rank Transformation Workflow.
- Run Rank Transformation Workflow.
- Rank Transformation output in SQL Developer.
Rank Transformation in Informatica is a type of an Active transformation that allows to identify top performers or bottom performers. The rank transformation is created with a default output (o/p) port called Rank Index.
Rank Transformation in Informatica is created with the following type of ports :
- Input Port (I).
- Output port (O).
- Rank Port (R).
What is Rank port?
A port which is participated to determine the ranks in Rank Transformation is known as Rank port. Only one port can be selected as rank port.
Example :- When we want to calculate top 3 employees based on their Salary. Here to identify the top or bottom employees, we must define Top/Bottom, Number of Ranks in the properties tabs as shown below.
Rank Transformation in Informatica does not support dense ranking only it supports a Normal ranks. If we want to do dense ranking, we have to use SQL or Expression Transformation in Informatica.
In this Informatica tutorial, we will learn how to create Create Rank Transformation in Informatica with an example. In the process of creating Create Rank Transformation in Informatica, we will explain step by step. Let us deep dive into Rank Transformation in Informatica with a scenario.
Scenario 1 : Calculate the tax for Top 3 employees of Sales dept based on Salary, If the Salary is >7000, then calculate the tax as Sal*0.25 Else calculate the tax as Sal*0.15.
Creating Rank Transformation in Informatica
As already we learnt that to create any transformation in Informatica, we must select Source table and target table. In this process we have selected Source table as Emp and created Target table Stg_Sales_emplyees.
Target definition using target designer can be created in Informatica PowerCenter Designer tool. Open Designer and connect with your Username and Password.
- From the left window, expand source and drop the source definition (Emp) to the target designer workspace and double click on the target definition, click Rename.
- Enter the name for your target designer as Stg_Sales_Employees and click on Ok button.
- Now navigate to Columns tab to add a new column called Employee_tax and the data type will be Number(P,S).
- Enter Prec as 7 and Scale as 2 as shown above and from the toolbar click on cut to delete unwanted columns.
- Click apply to save the settings and finally click on Ok button.
- Navigate to Target tab menu and click on Generate / Execute SQL as shown below.
- Click on Connect and enter filename for your Target fil
- Click on Generate / Execute button.
- Our Target definition table has been created successfully as shown below.
**** Running ODBC SQL script: file Stg_Sales_emplyees.sql ... CREATE TABLE Stg_Sales_employees ( EMPNO number(4) NOT NULL, ENAME varchar2(10), JOB varchar2(9), MGR number(4), HIREDATE date, SAL number(7,2), COMM number(7,2), DEPTNO number(2), Emplyee_Tax number(7,2) )
In this steps we have to create a mapping for rank transformation in Informatica, to create mapping navigate to Tools | Mapping Designer | Mapping and click on create button.
- Enter the name for your mapping and click on Ok button.
- Now Drag and drop the source (Emp) and target definition (Stg_Sales_Employees) to the Mapping designer workspace.
Now we have to create three transformation, Filter Transformation, Rank transformation and Expression expression transformation. Select Transformation | Create.
- Select filter transformation and enter a new name for filter transformation and click on create button. Similarly create Rank and Expression transformation.
From Source Qualifier, copy the required ports to the Filter transformation and connect them. Double click on the header of the filter transformation and select properties tab to add filter condition as shown below.
Under Transformation attribute, Filter condition as Deptno = 30 and click on Apply on Ok button. Now copy the ports from Filter transformation to Rank Transformation and double click on the Rank transformation header to select port tab.
- Select Port tab and for a port name Sal select Rank port (R) .
- Now go to properties tab and in Transformation attribute select Top/ Bottom as ‘Top” and No of ranks as ‘3’ as shown below.
Click on Apply button and now copy all the ports from Rank Transformation to Expression Transformation, except Rank Index. Double click on Expression Transformation add new port called “Tax”.
- Create new port called “Tax” ad select datatype as Decimal and select output port.
As per our scenario Calculate the tax for Top 3 employees of Sales dept based on Salary, If the Salary is >7000, then calculate the tax as Sal*0.25 Else calculate the tax as Sal*0.15. In the expression, enter condition as IIF(SAL>7000, SAL * 0.25, SAL * 0.15).
- Click on Apply, Click OK.
Connect the ports from Expression Transformation to Target definition as shown below.
- From the Repository, click on Save button.
After successfully creating the mapping, we have to create Workflow for the mapping. In Informatica PowerCenter Workflow manager, we can create workflow in two ways.
- Creating Informatica Workflow manually and.
- Creating Informatica Workflow using wizard.
In this Informatica Rank Transformation, we will create workflow using wizard. To create Workflow using wizard, Open Workflow manager and navigate to Workflow | Wizard. Enter a name for you Workflow and click on Next button.
In this step, we have to select the mapping that we created earlier to create session as s shown below.
- Click on next button and click on Finish button.
A session has been created for the workflow and double click on the session task to configure sources, targets and some other properties.
Like wise select your target concoction and finally click on OK button. From the below screenshot you can observe that rank Transformation workflow is valid one.
To run Rank transformation workflow, Navigate to workflow | Start Workflows. Open Informatica PowerCenter Workflow Monitor to check the Workflow run status as shown below.
After successful Rank transformation workflow run, top 3 emplyees of Sales dept based on salary, their tax amount will be calculated. Open the target table (Stg_Sales_emplyees) in SQL developer. Let us open SQL developer whether we successfully ranked the records based on Sal using Rank transformation.
In this Informatica tutorial, we have successfully created Rank Transformation in Informatica but he we haven’t learned how to handle nulls. In our next Informatica tutorial we will learn how to handle nulls in Rank transformation.