Rank Transformation to find TOP or BOTTOM Ranked Data Sets
Rank Transformation can be used to rank record sets and return records with the largest or smallest rank with in a group based on a port value. You can also use a Rank transformation to return the strings at the top or the bottom of an ordered data. In this article lets discuss a practical application of Informatica Power Center Rank Transformation.
For the demonstration, lets consider a business scenario.
The Sales Department wants to build a report showing Top five items per year. This solution should be build on the reusable component created for the quarterly sales report.
This solution will be build on the reusable component created for the quarterly sales report.
- Use the quarterly sales mapplet as source for the mapping
- Use a Rank transformation that will rank the data coming out of the mapplet and compute an annual sales figure for each item. The end result should be a table containing the top five selling items for each year.
I. Create a new mapping
- In the Mapping Designer, create a new mapping by the name M_Top5SalesByYear_x.
- Drag the MPLT_QtrSales_x Mapplet from the Navigator Window into your workspace.
- Create the target table definition Tgt_ItemSalesByYr_x and create the physical table in the database. The structure should be as shown below:
- Switch back to the Mapping Designer and drag the Tgt_ItemSalesByYear_x target definition from the Navigator Window into your workspace.
- Create a Rank Transformation and name it Rnk_Top5SalesByYear_x.
- Connect the ports from MPLT_QtrSales_x (mapplet) to the Rank transformation as shown:
- Double-click the Rnk_Top5SalesByYear_x Rank transformation.
- Click on the Ports tab.
- Make Q1Sales, Q2Sales, Q3Sales and Q4Sales input-only ports and give a default value Zero to each port.
- Group by YEAR.
- Add a new port after Q4Sales.
- Name it Year_Sales.
- Make it output-only.
- Place a check in the Rank column (R), for this port.
- Open the expression window for Year_Sales.
- Enter the expression: Q1Sales + Q2Sales + Q3Sales + Q4Sales
- Click on the Properties tab.
- Connect the ports in Rnk_Top5SalesByYear_x (Rank transformation) to the Tgt_SalesByYear_x (target definition) as shown :
- Validate the Mapping and save it to the repository.
II. Load the Target
- Create a Workflow with the name wf_Top5SalesByYear_x.
- Create a session task with the name s_Top5SalesByYear_x
- Run the Workflow.
- Monitor the Workflow.
- Your results should look like this: