TutorialKart.com- Rank Transformation in Informatica.
- Create Rank Transformation Workflow.
- Run Rank Transformation Workflow.
- Rank Transformation output in SQL Developer.
- Rank Transformation FAQs.
Rank Transformation in Informatica
Rank Transformation in Informatica is an active transformation used to select the top or bottom set of rows from incoming data based on a rank port. It is commonly used for scenarios such as top paid employees, lowest selling products, top customers by revenue, or bottom performing regions.
Unlike a passive transformation, a Rank transformation can reduce the number of rows that pass through the mapping. For example, if the source contains 100 employee rows and the transformation is configured for the top 3 salaries in the Sales department, only the ranked rows that match the rule are passed to the downstream transformation or target.
In this tutorial, we create a PowerCenter mapping that filters Sales department employees, ranks them by salary, calculates tax for the ranked employees, and loads the result into a target table.
Rank Transformation Ports in Informatica PowerCenter
Rank Transformation in Informatica is created with the following type of ports :
- Input Port (I).
- Output port (O).
- Rank Port (R).
The Rank transformation also creates a special output port called Rank Index. Rank Index stores the rank position assigned by the transformation. If you rank the top 3 rows, Rank Index helps identify whether a returned row is in rank position 1, 2, or 3.
| Rank transformation item | What it does in the mapping |
|---|---|
| Input port | Receives values from the previous transformation. |
| Output port | Passes selected values to the next transformation or target. |
| Rank port | Determines the value on which rows are ranked, such as SAL. |
| Rank Index | Returns the ranking position generated by the Rank transformation. |
| Top/Bottom property | Controls whether the highest or lowest values are selected. |
| Number of Ranks | Controls how many ranked rows are returned. |
For official behavior and property details, refer to Informatica documentation on ports in a Rank transformation and creating a Rank transformation.
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 practical terms, this means a Rank transformation is suitable when the requirement is to return the top or bottom N rows based on one ranking column. If the requirement is to produce dense rank numbering similar to a database analytic function, design that logic separately with SQL or additional transformation logic.
Top Rank and Bottom Rank Settings in Informatica
The Top/Bottom property decides the ranking direction. Select Top when the highest values should be returned, such as top 3 salaries. Select Bottom when the lowest values should be returned, such as bottom 5 sales amounts.
Top rank example : highest SAL values are selected
Bottom rank example : lowest SAL values are selected
Number of Ranks = 3 : three ranked positions are returnedBefore setting these properties, confirm the data type and meaning of the rank port. A numeric port such as salary, revenue, score, or quantity is easier to rank consistently than a descriptive text port.
Creating Rank Transformation in Informatica with example
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.
The required mapping logic is:
EMP source
→ Filter Transformation : DEPTNO = 30
→ Rank Transformation : Top 3 rows by SAL
→ Expression Transformation : IIF(SAL > 7000, SAL * 0.25, SAL * 0.15)
→ STG_SALES_EMPLOYEES targetCreating 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.
The source table should contain the employee number, employee name, department number, and salary columns at minimum for this example. The target table should include the columns you want to load after ranking, plus the calculated employee tax column.
Creating Target Definition using Target Designer for Rank Output
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)
)
Review the generated SQL before executing it in a database. In a production project, use consistent column names such as EMPLOYEE_TAX and confirm whether columns should allow null values.
Creating a Mapping for Rank Transformation in Informatica
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.
At this point, the Rank transformation returns only the ranked Sales department rows. If the business requirement changes from top salaries to lowest salaries, change the Top/Bottom property to Bottom. If the requirement changes from top 3 to top 5, update the Number of Ranks property.
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).
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.
Rank Transformation Mapping Validation Points
- Check that
DEPTNO = 30is applied in the Filter transformation before the Rank transformation. - Check that only the
SALport is selected as the Rank port. - Check that Top/Bottom is set to Top for the top salary scenario.
- Check that Number of Ranks is set to 3.
- Check that the tax expression output port is connected to the target tax column.
Creating Rank Transformation Workflow
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.
If the session is invalid, check the source connection, target connection, mapping validation status, and target table availability before running the workflow.
Run Rank Transformation Workflow
To run Rank transformation workflow, Navigate to workflow | Start Workflows. Open Informatica PowerCenter Workflow Monitor to check the Workflow run status as shown below.
A successful run should show that the session completed without errors. If the workflow fails, open the session log and check for database connection errors, target table mismatch, invalid port links, or expression validation errors.
How to check Rank transformation Output?
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.
You can also verify the output with a simple SQL query against the target table.
SELECT empno, ename, sal, deptno, emplyee_tax
FROM stg_sales_employees
ORDER BY sal DESC;The result should contain the employees from department 30 that were selected by the top salary ranking rule. The tax column should contain 25% of salary when salary is greater than 7000, otherwise 15% of salary.
Rank Transformation Troubleshooting in Informatica
| Issue | Likely reason | What to check |
|---|---|---|
| No rows loaded | Filter condition removes all rows. | Check whether DEPTNO = 30 exists in source data. |
| Wrong employees ranked | Wrong rank port selected. | Confirm SAL is marked as Rank port. |
| Too many or too few rows | Number of Ranks property is different from the requirement. | Check the Rank transformation properties tab. |
| Tax value is null or incorrect | Expression port is not configured or linked correctly. | Validate the IIF expression and target mapping. |
| Workflow fails | Connection, target table, or validation problem. | Open the session log in Workflow Monitor. |
Rank Transformation in Informatica QA Checklist
- Does the tutorial explain that Rank transformation is active and can reduce rows?
- Does the mapping clearly show the order: Filter, Rank, Expression, and Target?
- Is the Rank port identified as
SALfor this employee salary example? - Are Top/Bottom and Number of Ranks settings explained for the top 3 salary requirement?
- Is the tax expression
IIF(SAL>7000, SAL * 0.25, SAL * 0.15)included and connected to the target output? - Does the output verification mention checking the target table in SQL Developer?
Rank Transformation in Informatica FAQs
What is Rank Transformation in Informatica?
Rank Transformation in Informatica is an active transformation that selects top or bottom rows based on a rank port. It is used for requirements such as top 3 salaries, lowest 5 sales, or highest revenue customers.
What is Rank Index in Informatica Rank Transformation?
Rank Index is the default output port created by the Rank transformation. It stores the rank position assigned to each row returned by the transformation.
Can Rank Transformation support dense rank in Informatica?
Rank Transformation is normally used for top or bottom ranking and does not provide dense ranking in the same way as a SQL dense rank analytic function. For dense rank requirements, use SQL logic or additional transformation logic.
Can more than one port be selected as Rank port?
No. In this Rank transformation example, only one port is selected as the Rank port. For top 3 employees by salary, the SAL port is selected as the Rank port.
Why use Filter Transformation before Rank Transformation?
The Filter transformation limits the input rows before ranking. In this example, it keeps only Sales department rows with DEPTNO = 30, so the Rank transformation calculates the top 3 salaries only within that department.
Conclusion: Informatica Rank Transformation Example
In this Informatica tutorial, we created a Rank Transformation mapping for top 3 Sales department employees based on salary. The mapping filtered Sales department rows, ranked employees by the SAL port, calculated tax in an Expression transformation, loaded the target table, and verified the Rank transformation output in SQL Developer.
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.