Rank Transformation to find TOP or BOTTOM Ranked Data Sets

Johnson Cyriac May 14, 2013
|

SCD Type 4, a Solution for Rapidly Changing Dimension
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.

Solution

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.

Mapping Layout

Rank Transformation to find TOP or BOTTOM Ranked Data Sets

I. Create a new mapping

  1. In the Mapping Designer, create a new mapping by the name M_Top5SalesByYear_x.
  2. Drag the MPLT_QtrSales_x Mapplet from the Navigator Window into your workspace.
  3. Create the target table definition Tgt_ItemSalesByYr_x and create the physical table in the database. The structure should be as shown below:
    Rank Transformation to find TOP or BOTTOM Ranked Data Sets
  4. Switch back to the Mapping Designer and drag the Tgt_ItemSalesByYear_x target definition from the Navigator Window into your workspace.
  5. Create a Rank Transformation and name it Rnk_Top5SalesByYear_x.
  6. Connect the ports from MPLT_QtrSales_x (mapplet) to the Rank transformation as shown:Rank Transformation to find TOP or BOTTOM Ranked Data Sets
  7. Double-click the Rnk_Top5SalesByYear_x Rank transformation.
  8. Click on the Ports tab.
  9. Make Q1Sales, Q2Sales, Q3Sales and Q4Sales input-only ports and give a default value Zero to each port.
  10. Group by YEAR.
  11. Add a new port after Q4Sales.
  12. Name it Year_Sales.
  13. Make it output-only.
  14. Place a check in the Rank column (R), for this port.
  15. Open the expression window for Year_Sales.
  16. Enter the expression: Q1Sales + Q2Sales + Q3Sales + Q4Sales
  17. Click on the Properties tab.
    1. Set the Top/Bottom attribute value to Top.
    2. Set the Number of Ranks attribute to 5.
    3. Click OK.
    4. Rank Transformation to find TOP or BOTTOM Ranked Data Sets
  18. Connect the ports in Rnk_Top5SalesByYear_x (Rank transformation) to the Tgt_SalesByYear_x (target definition) as shown :Rank Transformation to find TOP or BOTTOM Ranked Data Sets
  19. Validate the Mapping and save it to the repository.
Given below is the final mapping:
Rank Transformation to find TOP or BOTTOM Ranked Data Sets

II. Load the Target

  1. Create a Workflow with the name wf_Top5SalesByYear_x.
  2. Create a session task with the name s_Top5SalesByYear_x
  3. Run the Workflow.
  4. Monitor the Workflow.
  5. Your results should look like this: 
Rank Transformation to find TOP or BOTTOM Ranked Data SetsHope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out these exercise and subscribe to the mailing list to get the latest tutorials in your mail box.




About US Contact US Advertise Guest Post Terms and Conditions Privacy Policy Disclaimer

© 2012-2013 Data Intelligence Solution, All Rights Reserved
The contents in this site is copyrighted to Data intelligence Solution and may not be reproduced on other websites.