Rank Transformation in Informatica is similar to the SQL RANK function, which is used to select the Top or bottom rank of data. In real-time, this transformation will be very helpful.
For example, you can use this Informatica rank transformation to select the top 10 regions with the highest and lowest sales Or bottom (underperforming) 20 products or regions, etc.
For this Informatica rank transformation example, we are going to use the below show data
Configure Rank Transformation in Informatica
Before we start configuring the rank transformation in Informatica, First connect to the repository service.
Create Rank Transformation in Informatica Source Definition
Once you have connected successfully, Please navigate to Source Analyzer and define your Sources. In this Informatica rank transformation example, we are using the [Employ] table from the SQL Server database as our source definitions. Please refer to Database Source to understand the steps involved in creating a source definition
Create a Target Definition
Please navigate to Target Designer and define the Target. We use the already created SQL table as our target definition in this example. Please refer Create Target table using Source Definition to understand creating a target definition
Creating Rank Transformation in Informatica Mapping
In order to create a new mapping for rank transformation, Please navigate to the Mappings menu in Menu Bar and select the Create.. option. This will open the Mapping Name window as shown below. Here, you have to write a unique name for this mapping (m_Rank_Transformation) and click the OK button.
Next, Drag and drop the [Employ] source definition from the Sources folder to the mapping designer. Once you drag the source, the PowerCenter designer will automatically create the default transformation called source qualifier.
Create Rank Transformation in Informatica
In order to create Rank transformation, Please navigate to the Transformation menu and select the Create.. option as we shown below.
Once you click on the Create.. option, Create Transformation window will be opened as shown below. Please select the Informatica Rank Transformation from the drop-down list and specify the unique name (rnk_Income) for this transformation and click on Create button
Once you click on the Create button, the rank transformation will be added to the mapping designer.
To rank the data, it requires some data. So, we have to connect the Source definition with the transformation by dragging the required fields.
From the above screenshot, you can observe the new item called RANKINDEX, and this is the default port created by the rank transformation. This output port will hold the ranking number, so we have to assign this output port to the target table rank column.
Double click on the Informatica Rank transformation to provide the ranking factor (based on which column you want to rank). From the below screenshot you can see the list of available properties in the Transformation tab:
- Select Transformation: By default, it will select the transformation you clicked on.
- Rename: This button will help you to rename it to a more meaningful name.
- Make Reusable: If you check mark this option, then this transformation will become a reusable transformation.
- Description: Please provide a valid description.
Below screenshot will show you the list of available options in the Informatica Rank Transformation Ports tab:
- Port Name: List of available column names. By clicking the New column button, you can add new columns, and the scissors button deletes the unwanted columns.
- I: Columns that are check-marked under this section are the Input columns.
- O: Columns that are check-marked under this section are the Output columns. If you unchecked any column, then that column will not be available to load in a target table.
- R: Please checkmark the Column that you want to use as a ranking factor (based on which column you want to rank). For example, If you want to rank the data by yearly Income, then you have to checkmark it as we have shown below.
The below screenshot show you the list of available options in the Informatica Rank Transformation Properties tab.
- Cache Directory: The integration service will store the Cache files in this location.
- Top/Bottom: This will provide two options for the user i.e., Top and Bottom. If you want to rank the data from top to bottom (Z to A), use the Top option or, for the bottom to top (A to Z), use the Bottom option.
- Number of Ranks: Please enter the number of rows you want to rank for. By default, the value will be 1, meaning the rank transformation will select only 1 record.
- Case Sensitive String Comparison: If you check mark this option, then While sorting the data, the transformation will perform Case Sensitive String Comparison
For this example, we want to rank every record present in the Employ table based on their Yearly Income (in descending order) that’s why we selected Top/Bottom property value as Top as and the Number of Ranks property value as 20 (our maximum records are 14)
Once you finish configuring the properties, Click OK to close the transformation window.
Next, Drag and drop the target definition (Rank Transformation) from the Targets folder to the mapping designer and connect the Transformation with the target definition. Please use the Autolink.. option to connect them.
Before we close the Mapping, Let us validate the mapping by going to the Mapping Menu bar and selecting the Validate option.
Creating a Workflow for Rank Transformation in Informatica
Once we finish creating the Mapping, we have to create the workflow for it. PowerCenter Workflow manager provides two approaches to creating a workflow.
In this Informatica Rank Transformation example, we will create the Workflow manually. To create a new Workflow, Please navigate to Workflows Menu and select the Create option. This will open Create Workflow window. Please provide the unique name (wf_Rank_Transformation) and leave the default settings.
Once we have created the workflow, our next step is to create a session task for our Rank Transformation mapping.
There are two types of sessions:
For this Informatica Rank Transformation example, we will create Non-reusable Session. Please navigate to Tasks Menu and select the Create option to open the Create Task window. Here you have to select the Session as Task type (default) and enter a unique name (S_Rank_Transformation) for the session.
Once you click the Create button, a new Mappings window will be opened. You must select the mapping you want to associate with this session.
From the below screenshot, you can observe that we are selecting the mapping (m_Rank_Transformation) that we created earlier (in Step 3).
Double click on the Session Task to configure it. Although we have to configure Sources, targets, and some common properties, we are explaining only a few properties. We strictly recommend visiting the Session article to understand the remaining properties.
From the below screenshot, you can observe that we assigned the $target variable to Connection Value, we changed the Target Load Type option from Bulk to Normal mode, and check marked the Truncate target table option to truncate the existing data from the destination table.
From the screenshot below, you can observe that the Rank Transformation in Informatica workflow is valid. Let’s start the Workflow by navigating to the Workflows menu and selecting the Start Workflow option.
Once you select the Start Workflow option, the PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot, you can observe that our Rank Transformation workflow is executed without any errors.
Let us open the SQL Server Management Studio to check whether we successfully ranked the records using Yearly Income using the Informatica Rank Transformation or not.
We can achieve the above output using the following Query:
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,RANK() OVER ( ORDER BY [YearlyIncome] DESC ) AS RANK FROM [Customers]
Let us change the Top/Bottom property to Bottom and refresh the mapping. It means Informatica rank transformation workflow will rank the Employ table based on the Yearly Income in Ascending order (0 as 1, 1 as 2 …. )