Aggregator Transformation in Informatica

The Aggregator Transformation in Informatica is one of the most used transformations in real-time. This transformation performs a function similar to the SQL Server Aggregate functions.

The Informatica Aggregator Transformation operations include the following:

  • COUNT: It will count the number of values in this column. Null values are included in the count if you select (*) as the input column. Otherwise, null values are ignored.
  • SUM: Calculate the Sum of the Column values.
  • AVG: Calculate the Average of the Column values.
  • MIN: Finding the Minimum Column value.
  • MAX: Finding the Maximum Column value.
  • Median: Calculate the Median of the column values.
  • Stddev: Calculate the Standard Deviation of the column values.
  • Variance: Calculate the Variance of the column values.

In this article, we explain the steps involved in configuring the Informatica Aggregator Transformation to perform a few of the above-mentioned operations.

Configure Aggregator Transformation in Informatica

Before we start configuring the Informatica Aggregator transformation, First connect to the repository service.

To connect with the Informatica Repository service, we have to provide the Admin Console Username and Password you specified while installing the Server.

Connect to Repository Service Admin page

Create Aggregator Source Definition

Once you have connected successfully, Please navigate to Source Analyzer and define your Sources. In this Informatica Aggregator Transformation example, we use [Dim Geography] and [Fact Reseller Sales] from the SQL Server database as our source definitions. Please visit Database Source to understand creating source definitions.

Source Analyzer Tables 1

Create Aggregator Target Definition

Please navigate to Target Designer and define the Informatica Aggregator Transformation Target. In this example, we use the already created (Aggregator) SQL table as our target definition. Please refer Create Target table using the Source Definition article.

Target Definition Table 2

Create Informatica Aggregator Transformation Mapping

To create a new mapping for Informatica Aggregator Transformation, Please navigate to the Mappings menu and select the Create.. option. It opens the Mapping Name window.

You must write a unique name for this mapping (m_Aggregator_Transformation) and click the OK button.

Create a New mapping 3

Next, Drag and drop the [Dim Geography] and [fact Reseller Sales] source definitions from the Sources folder to the mapping designer. Once you drag the source, the PowerCenter designer automatically creates the default one called the source qualifier.

As we all know that the [Dim Geography] and [fact Reseller Sales] tables have the Primary and Foreign key relationship. So we removed one source qualifier and added the required fields from both tables to a single source qualifier.

Create Aggregator Transformation in Informatica

In order to create Aggregator Transformation in Informatica, Please navigate to the Transformation menu in Menu Bar and select the Create.. option as shown below.

Aggregator Transformation in Informatica 4

Once you click on the Create.. option, Create Transformation window will open, as shown below. Please select the Informatica Aggregator Transformation from the drop-down list, specify the unique name (agg_Geography_Sales) for this, and click the Create button.

Aggregator Transformation in Informatica 5

Once you click the Create button, the Informatica Aggregator Transformation will add to the mapping designer. Please connect the Source definition with this one by dragging the required fields.

From the below screenshot, you can observe that we are excluding the unwanted columns like Spanish Country name, Keys, IP address locator, etc.

Aggregator Transformation in Informatica 7

Double click on the Informatica Aggregator transformation to perform aggregations on the source. For example, below screenshot, you can see the list of available properties in the Transformation tab:

  • Select Transformation: By default, it will select the one. You clicked on it.
  • Rename: This button will help you to rename the Aggregator transformation to a more meaningful name.
  • Make Reusable: If you check mark this option, it becomes reusable.
  • Description: Please provide a valid description of this aggregator transformation.
Aggregator Transformation in Informatica 8

The below screenshot will show you the list of available options in the Ports tab of an Informatica Aggregator Transformation:

  • Port Name: List of available column names. You can add new columns by clicking the New Column button, and by clicking the scissors button, you can delete the unwanted columns.
  • I: The columns check marked under this section are the Informatica Aggregator transformation Input columns.
  • O: Columns that are check marked under this section are the Output columns. If you unchecked any column, that column would not be available to load in a target table. From the below screenshot, you can observe that we unchecked this port for five columns (Unit Price, Product Standard Cost, Total Product Cost, Sales Amount, and Tax Amount) because we want to restore these columns with new fields (holding the aggregated data)
  • V: Please checkmark this if it is variable
  • Expression: Here, you can write the custom expression for a particular column.
  • Group By: Please checkmark the Column you want to use for Group by. It is just like the GROUP BY Clause in SQL SELECT queries. In this example, we want to group the data by English Country Region Name, State Province Name, and City.

We added 5 new columns using the New Port button. We will use these new columns to store the Aggregated information such as Sum, Mean, Median, Count etc..

TIP: All these new columns are output ports only, so please check mark O

Add new Columns inside a Port Tab 9

Let us write the custom expression to get the information we require. To do this, Click on the arrow button beside the o_UnitPrice. It will open the Expression Editor to write a custom expression. Here, we want to find the average of the Unite Price, so select the AVG() function from the list of aggregate functions.

List Of Aggregate Functions inside the Expression editor 10

In this Informatica Aggregator Transformation example, we are calculating the Average of Unit Price, Sum of Product Standard Cost, Median of Total Product Cost, Standard Deviation of Sales Amount, and Variance of Tax Amount and Group by English Country Region Then by State Province Name and Then by City.

TIP: We select some random functions, and we suggest you follow your business logic to get more meaningful results.

Aggregator Transformation in Informatica 11

The list of available options is in the Informatica Aggregator Transformation Properties tab. If your data is pre-sorted, please select the Sorted Input option.

TIP: Selecting the Sorted Input option will improve the performance of the Informatica Aggregator transformation.

View the Properties tab to select sorted data 12

Once you finish configuring the aggregations, Click OK to close the window. Next, Drag and drop the target definition (aggregator) from the Targets folder to the mapping designer and connect the Informatica aggregator transformation with the target definition.

Please use the Autolink.. option to connect them.

Aggregator Transformation in Informatica 13

Before we close the Mapping, Let us validate the Aggregator transformation mapping by going to the Mapping Menu bar and selecting the Validate option.

Create a Workflow for Aggregator 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 Aggregator Transformation example, we are going to use the manually created Workflow that we created in our earlier posts. Once we have created the workflow, our next step is to create a session task for our mapping.

Create a Session

There are two types of sessions:

For this Informatica Aggregator Transformation example, we are going to create Non-reusable Session. Please navigate to the 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_Aggregator_Transformation) for the session.

Once you click the Create button, a new window called Mappings will open. Here you have to select the mapping you want to associate with this session. We are selecting the mapping (m_Aggregator_Transformation) that we created earlier (in Step 3).

Aggregator Transformation in Informatica 14

Double click on the Session Task to configure it. Although we have to configure Sources, targets, and some common properties, we already explain them in Session’s article.

The Informatica Aggregator Transformation workflow is a valid one. Let us start the Workflow by navigating to the workflow menu and selecting the Start Workflow option.

Start the Workflow 15

Once you select the Start Informatica Aggregator Transformation 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.

Aggregator Transformation in Informatica 16

Let us open the SQL Server Management Studio to check whether we successfully performed aggregations using the Aggregator Transformation.

Destination Table Data 17

Comments are closed.