The Informatica Rank Transformation is similar to SQL RANK function, which is used to select the Top or bottom rank of data. In this article, we are going to explain the steps involved in configuring the Informatica Rank Transformation with Group By along with an example.
For this example, we are going to use the below show data.
Configure Informatica Rank Transformation with Group By
Before we start configuring the rank transformation, First connect to the Informatica repository service. In order to connect with the Repository service, we have to provide the Admin Console credentials. So, Please provide the appropriate Username and Password that you specified while installing the Server and click on Connect button as shown below.
TIP: Please refer Rank Transformation in Informatica article to understand the transformation without Grouping
Step 1: Creating Source Definition
Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this example, we are using [Employ] table from SQL Server database as our source definitions. Please refer to Database Source to understand the steps involved in creating a source definition
Step 2: Creating a Target Definition
Please navigate to Target Designer and define the Target. In this example, we are using the already created SQL table as our target definition. Please refer Create Target table using Source Definition to understand the steps involved in creating a target definition
Step 3: Creating Mapping
In order to create new mapping, Please navigate to 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 OK button.
TIP: Please refer Mapping article to understand the steps involved in creating Mapping.
Next, Drag and drop the [Employ] source definition from Sources folder to the mapping designer. Once you drag the source, the PowerCenter designer will automatically create the default transformation called source qualifier.
Step 3(a): Creating Rank Transformation in Informatica
In order to create Rank transformation in Informatica with the group by, Please navigate to the Transformation menu in Menu Bar and select the Create.. option as shown below.
Once you click on the Create.. option, Create Transformation window will be opened as shown below. Please select the Rank Transformation from 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. In order to rank the data, rank Transformation requires some data. So, we have to connect the Source definition with the transformation by dragging the required fields.
From the above screenshot you an observe the new item called RANKINDEX and this is default port created by the Rank Transformation. This output port will hold the ranking number so, we have to assign this output port to target table rank column.
Double click on the Rank transformation to provide the ranking factor (based on which column you want to rank). Below screenshot will show you the list of available options in the Ports tab:
- R: Please checkmark the Column that you want to use as 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 shown below.
- Group By: Please checkmark the Column that you want to use for Group by. In this example, we want to group the Employ table using the Occupation and then we want to rank employees inside the individual group.
Below screenshot will show you the list of available options in the Properties tab.
- 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) then use the Top option or, if rank the data from bottom to top (A to Z) then use Bottom option.
- Number of Ranks: Please enter the number of rows you want to rank for. By default, the value will be 1 and it means, the rank transformation will select only 1 record.
For this example, we want to rank every record present in 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 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 Mapping Menu bar and selecting the Validate option.
Step 4: Creating a Workflow
Once we finish creating the Mapping we have to create the workflow for it. PowerCenter Workflow manager provides two approaches to create a workflow.
In this example, we will create the Workflow manually. In order to create a new Workflow, Please navigate to Workflows Menu and select the Create option. This will open Create Workflow window as shown below. Please provide the unique name (wf_Rank_Transformation) and leave the default settings.
Once we created the workflow, our next step is to create a session task for our mapping.
NOTE: We strictly recommend to refer Workflow article to understand the steps involved in creating Workflow manually.
Step 4(a): Creating Session
There are two types of sessions:
- Non-reusable Session Task: Please refer Session article to understand the steps involved init.
- Reusable Session Task: Please refer Reusable Session article to understand the steps involved init.
For this example, we are going to 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 on the Create button, a new window called Mappings will be opened. Here you have to 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 below screenshot you can observe that the Informatica Rank Transformation with the group by workflow is a valid one. Now, let us 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 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 with a group by or not.
We can achieve the above output using the following SQL RANK Function Query:
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,RANK() OVER (
PARTITION BY Occupation
ORDER BY [YearlyIncome] DESC
) AS RANK
FROM [Customers]
