The Sorter Transformation in Informatica is used to sort the source data in either Ascending or Descending order, similar to SQL command ORDER BY Statement.
In this section, we are going to demonstrate the steps involved in configuring the Informatica Sorter Transformation with an example. For this example, we are going to use the below show data
Configure Sorter Transformation in Informatica
Before we start the Informatica Sorter Transformation demo, First, connect to the Informatica repository service. For this, provide the Admin Console credentials (Username and Password) and click on the Connect button
Step 1: Creating Source Definition for Sorter Transformation in Informatica
Once you connected successfully, Please navigate to Source Analyzer to set our Sources. In this Informatica Sorter Transformation example, we are using the already created SQL table (Fact Internet Sales) as our source definition. Please refer Database Source to know the steps involved in creating a source definition
Step 2: Create Mapping for Sorter Transformation in Informatica
To create a new mapping for Informatica Sorter Transformation, Please navigate to the Mappings menu in Menu Bar and select the Create.. option. Here, you have to write a unique name for this Mapping and click the OK button.
TIP: Please refer Mapping article to understand the steps involved in creating Mapping.
Next, Drag and drop the [Fact Internet Sales] source definition from Sources folder to the mapping designer. Once you drag the source, the PowerCenter designer will automatically create the default transformation called the source qualifier.
Step 2(a): Creating Sorter Transformation in Informatica
To create a sorter transformation in Informatica, Please navigate to the Transformation menu in Menu Bar and select the Create.. option.
It opens a Create Transformation window, as shown below.
Please click on the arrow to select the required transformation from the drop-down list. Let us choose the Informatica Sorter transformation
Next, Please specify the unique name for this Informatica Sorter transformation and click on Create button
Once you click on the Create button, the Informatica Sorter transformation will be added to the mapping designer. In order to perform sorting, transformation requires some data so, we have to connect it with Source definition.
The PowerCenter Designer provides multiple ways to connect the source qualifier with the newly created Sorter Transformation in Informatica. For this example, we are right-clicking on the empty space and selecting the Autolink.. option from that context menu.
NOTE: The Mapping designer allows us to delete the wrong mapping by selecting the link and deleting it.
From the above screenshot, you can observe that we successfully connected the source qualifier to the sorter transformation.
Double click on the Informatica Sorter transformation to configure the sort properties. Below window will show you 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 the sorter transformation to a more meaningful name.
- Make Reusable: If you check mark this option then, this transformation will become reusable transformation.
- Description: Please provide the valid description about this transformation.
The below screenshot will show you the list of available options in the Ports tab of an Informatica Sorter Transformation:
- Port Name: List of available column names. By clicking the New column button you can add new columns and by clicking the scissors button you can delete the unwanted columns.
- I: Columns that are check marked under this section are the Sorter transformation Input columns.
- O: Columns that are check marked under this section are the Sorter transformation Output columns. If you unchecked any column then, that column will not be available to load in the target table.
- Key: Please checkmark the Column(s) that you want to use as Sort column. For example, If you want to sort the data by Unit Price and Order Quantity then you have to checkmark both of them.
- Direction: Here we have to select the Sort direction. This Informatica Sorter Transformation provides two options: Ascending order (A to Z) and Descending Order(Z to A)
To understand the Sorter Transformation in informatica, we are sorting one column with Ascending order and another with Descending order. From the below screenshot you can observe that, we are sorting the [Sales Amount] in Descending Order
Next, we are sorting the [Freight] in Ascending order. It means,
- First data is sorted by the [Sales Amount] in Descending Order and then
- Second, data will be sorted by the [Freight] in Ascending order.
Click OK to finish configuring the Sorter Transformation.
Step 2(b): Target Definition for Sorted Transformation in Informatica
First, Drag and drop the target definition from the Targets folder to the mapping designer. Next, We have to connect it with the target definition. Please use the Autolink.. option to connect them.
TIP: Please refer to Create Target Table article to understand importing the target definition from the SQL Database.
Before we close the Mapping, Let us validate the Informatica Sorter Transformation mapping by going to Mapping Menu bar and selecting the Validate option.
Step 3: Creating a Workflow for Sorter Transformation in Informatica
Once we finish creating the Mapping we have to create the workflow for it. The PowerCenter Workflow manager provides two approaches to creating a workflow.
In this Informatica Sorter Transformation example, we created the Workflow manually. We strictly recommend to refer Workflow article to understand the steps involved in creating Workflow manually. Once we created the workflow, our next step is to create a session.
Step 3(a): Creating an Informatica Sorter Transformation Session
There are two types of sessions:
- Non-reusable Session Task: Please refer Session article to understand the steps involved in it.
- Reusable Session Task: Please refer Reusable Session article to understand the steps involved in it.
For this Sorted Transformation in Informatica example, we are going to create Non-reusable Session. In order 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 for the session.
Once you click on the Create button, a new window called Mappings will open. Here we 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 that we created earlier (in Step 2).
Although we have to configure sources, Target, and some common properties, we are explaining only two properties. We strictly recommend visiting the Session article to understand the remaining properties.
From the below screenshot you can observe that, 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 Our workflow is a valid one. Now, let us start the Informatica Sorter Transformation 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 Management Studio to check whether we successfully transferred the sort data from Adventure Works DW to the target database. Please refer ORDER BY Statement article in SQL Server Tutorial.
