Rank Transformation
Rank Transformation in Informatica
Rank transformation is an active and connected transformation. The rank transformation is used to select the top or bottom rank of data. The rank transformation is used to select the smallest or largest numeric/string values. The integration service caches the input data and then performs the rank calculations.
Rank Transformation Properties :
- Cache Directory where cache will be made.
- Top/Bottom Rank as per need
- Number of Ranks Ex: 1, 2 or any number
- Case Sensitive Comparison can be checked if needed
- Rank Data Cache Size can be set
- Rank Index Cache Size can be set
Ports in a Rank Transformation :
| Ports |
Number Required
| Description |
| I | 1 Minimum |
Port to receive data from another transformation.
|
| O | 1 Minimum |
Port we want to pass to other transformation.
|
| V | not needed |
can use to store values or calculations to use in an expression.
|
| R | Only 1 |
Rank port. Rank is calculated according to it. The Rank port is an input/output port. We must link the Rank port to another transformation. Example: Total Salary
|
Rank Index
The Designer automatically creates a RANKINDEX port for each Rank transformation. The Power Center Server uses the Rank Index port to store the ranking position for Each row in a group.
For example, if we create a Rank transformation that ranks the top five salaried employees, the rank index numbers the employees from 1 to 5.
- The RANKINDEX is an output port only.
- We can pass the rank index to another transformation in the mapping or directly to a target.
- We cannot delete or edit it.
Defining Groups
Rank transformation allows us to group information. For example: If we want to select the top 3 salaried employees of each Department, we can define a group for Department.
- By defining groups, we create one set of ranked rows for each group.
- We define a group in Ports tab. Click the Group By for needed port.
- We cannot Group By on port which is also Rank Port.
1) Example: Finding Top 5 Salaried Employees
- EMP will be source table.
- Create a target table EMP_RANK_EXAMPLE in target designer. Structure should be same as EMP table. Just add one more port Rank_Index to store RANK INDEX.
- Create the shortcuts in your folder.
Creating Mapping:
- Open folder where we want to create the mapping.
- Click Tools -> Mapping Designer.
- Click Mapping-> Create-> Give mapping name. Ex: m_rank_example
- Drag EMP from source in mapping.
- Create an EXPRESSION transformation to calculate TOTAL_SAL.
- Click Transformation -> Create -> Select RANK from list. Give name and click Create. Now click done.
- Pass ports from Expression to Rank Transformation.
- Edit Rank Transformation. Go to Ports Tab
- Select TOTAL_SAL as rank port. Check R type in front of TOTAL_SAL.
- Click Properties Tab and Select Properties as needed.
- Top in Top/Bottom and Number of Ranks as 5.
- Click Apply -> Ok.
- Drag target table now.
- Connect the output ports from Rank to target table.
- Click Mapping -> Validate
- Repository -> Save
- Create Session and Workflow as described earlier. Run the Workflow and see the data in target table.
- Make sure to give connection information for all tables.
2) Example: Finding Top 2 Salaried Employees for every DEPARTMENT
- Open the mapping made above. Edit Rank Transformation.
- Go to Ports Tab. Select Group By for DEPTNO.
- Go to Properties tab. Set Number of Ranks as 2.
- Click Apply -> Ok.
- Mapping -> Validate and Repository Save.
Refresh the session by double clicking. Save the changed and run workflow to see the new result.
RANK CACHE
Sample Rank Mapping
When the Power Center Server runs a session with a Rank transformation, it compares an input row with rows in the data cache. If the input row out-ranks a Stored row, the Power Center Server replaces the stored row with the input row.
Example: Power Center caches the first 5 rows if we are finding top 5 salaried Employees. When 6th row is read, it compares it with 5 rows in cache and places it in Cache is needed.
1) RANK INDEX CACHE:
The index cache holds group information from the group by ports. If we are Using Group By on DEPTNO, then this cache stores values 10, 20, 30 etc.
- All Group By Columns are in RANK INDEX CACHE. Ex. DEPTNO
2) RANK DATA CACHE:
It holds row data until the Power Center Server completes the ranking and is Generally larger than the index cache. To reduce the data cache size, connect Only the necessary input/output ports to subsequent transformations.
- All Variable ports if there, Rank Port, All ports going out from RANK Transformations are stored in RANK DATA CACHE.
- Example: All ports except DEPTNO In our mapping example.
Creating Rank Transformation
Follow the below steps to create an expression transformation
- In the mapping designer, create a new mapping or open an existing mapping.
- Go to Toolbar->click Transformation -> Create. Select the Rank transformation.
- Enter a name, click on Create and then click on Done.
- By default, the rank transformation creates a RANKINDEX port. The RankIndex port is used to store the ranking position of each row in the group.
- You can add additional ports to the rank transformation either by selecting and dragging ports from other transformations or by adding the ports manually in the ports tab.
- In the ports tab, check the Rank (R) option for the port which you want to do ranking. You can check the Rank (R) option for only one port. Optionally you can create the groups for ranked rows. select the Group By option for the ports that define the groups.
Configuring the Rank Transformation
Configure the following properties of Rank transformation
- Cache Directory: Directory where the integration service creates the index and data cache files.
- Top/Bottom: Specify whether you want to select the top or bottom rank of data.
- Number of Ranks: specify the number of rows you want to rank.
- Case-Sensitive String Comparison: Used to sort the strings using case sensitive or not.
- Tracing Level: Amount of logging to be tracked in the session log file.
- Rank Data Cache Size: The data cache size default value is 2,000,000 bytes. You can set a numeric value, or Auto for the data cache size. In case of Auto, the Integration Service determines the cache size at runtime.
- Rank Index Cache Size: The index cache size default value is 1,000,000 bytes. You can set a numeric value, or Auto for the index cache size. In case of Auto, the Integration Service determines the cache size at runtime.
Rank Transformation Examples:
Q) Create a mapping to load the target table with top 2 earners (employees) in each department using the rank transformation.
Solution:
- Create a new mapping, Drag the source definition into the mapping.
- Create a rank transformation and drag the ports of source qualifier transformation into the rank transformation.
- Now go to the ports tab of the rank transformation. Check the rank (R) option for the salary port and Group By option for the Dept_Id port.
- Go to the properties tab, select the Top/Bottom value as Top and the Number of Ranks property as 2.
- Now connect the ports of rank transformation to the target definition.
Share This:
Tharun Katanguru
I'm Tharun Katanguru. A full time web designer and Data Engineer. I enjoy to make modern template. I love create blogger template and write about Web Design, Data Warehousing and Data Management. Now I'm working as a Jr Etl Developer.
No Comment to " Rank Transformation "