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 are going to explain the steps involved in configuring the Aggregator Transformation in Informatica to perform a few of the above-mentioned operations with an example.
Configure Aggregator Transformation in Informatica
Before we start configuring the Aggregator transformation in Informatica, First connect to Informatica repository service. In order to connect with Repository service, we have to provide the Informatica Admin Console credentials. So, Please provide the appropriate Username and Password and click on Connect button as shown below.
TIP: Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Step 1: Create Aggregator Transformation Source Definition
Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this example, we are using [Dim Geography] and [Fact Reseller Sales] from the SQL Server database as our source definitions.
TIP: Please refer Database Source in Informatica to understand the steps involved in creating source definition
Step 2: Create Aggregator Transformation Target Definition
Please navigate to Target Designer and define the Target. In this example, we are using the already created SQL table (Aggregator Transformation) as our target definition. Please refer Create Informatica Target table using Source Definition to understand the steps involved in creating a target definition
Step 3: Create Informatica Aggregator Transformation Mapping
In order to create a new mapping for Informatica Aggregator Transformation, 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_Aggregator_Transformation) and click OK button.
TIP: Please refer Informatica Mapping article to understand the steps involved in creating Mapping.
Next, Drag and drop the [Dim Geography] and [fact Reseller 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 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 single source qualifier.
Step 3(a): Create Aggregator Transformation in Informatica
In order to create Aggregator Transformation in Informatica, Please navigate to Transformation menu in Menu Bar and select the Create.. option as we shown below.
Once you click on the Create.. option, Create Transformation window will be opened as we shown below. Please select the Aggregator Transformation from drop-down list and specify the unique name (agg_Geography_Sales) for this transformation and click on Create button
Once you click on the Create button, the Aggregator Transformation will be added to the mapping designer. Please connect the Source definition with the transformation 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.
Double click on the Aggregator transformation to perform aggregations on the source. 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 the Aggregator transformation to a more meaningful name.
- Make Reusable: If you check mark this option then, this transformation will become reusable transformation.
- Description: Please provide a valid description of this transformation.
Below screenshot will show you the list of available options in the Ports tab of a Informatica Aggregator 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 Aggregator transformation Input columns.
- O: Columns that are check-marked under this section are the Aggregator transformation Output columns. If you unchecked any column then, that column will 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 check mark this if it is variable
- Expression: Here you can write the custom expression for the particular column.
- Group By: Please checkmark the Column that 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.
From the below screenshot you can observe that We added 5 new columns using the New Port button. We are going to 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
Let us write the custom expression to get the information we required. To do this, Click on the arrow button beside the o_UnitPrice. This 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.
From the below screenshot you can observe that We are calculating the Average of Unit Price, Sum 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 function, we suggest you follow your business logic to get more meaningful results.
Below screenshot will show you the list of available options in the Properties tab. If your data is pre-sorted then, please select the Sorted Input option.
TIP: Selecting the Sorted Input option will improve the performance of the Aggregator transformation in Informatica.
Once you finish configuring the aggregations, Click OK to close the transformation window. Next, Drag and drop the target definition (aggregator transformation) from Targets folder to the mapping designer and connect the aggregator 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: 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 create 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 created the workflow, our next step is to create a session task for our mapping.
Step 4(a): Create a Session for Aggregator Transformation in Informatica
There are two types of sessions in Informatica:
- Non-reusable Session Task: Please refer Session in Informatica article to understand the steps involved in it.
- Reusable Session Task: Please refer Reusable Session in Informatica article to understand the configuration steps.
For this Informatica Aggregator Transformation 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_Aggregator_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_Aggregator_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 not going to explain them here. We strictly recommend visiting Session in Informatica article to understand the properties.
From the below screenshot you can observe that the Aggregator Transformation in Informatica workflow is a valid one. Now, Let us start the Workflow by navigating to Workflows menu and selecting the Start Workflow option.
Once you select the Start Workflow option, Informatica 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 performed aggregations using the Aggregator Transformation in Informatica or not.
Thank You for Visiting Our Blog