Aggregator Transformation Interview Questions And Answers

In this Informatica interview questions list, you will come to know the top questions asked in the Informatica job interview. You will learn the difference between a database and a data warehouse, Informatica Workflow Manager, mapping parameter vs mapping variable, and more. Learn Informatica from Intellipaat Informatica Certification Course and fast-track your career!

1 How do you load more than 1 Max Sal in each Department through Informatica or write sql query in oracle?

SQL query:

You can use this kind of query to fetch more than 1 Max salary for each department.

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY, RANK () OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) SAL_RANK FROM EMPLOYEES)

Informatica Approach:

We can use the Rank transformation to achieve this.

Use Department_ID as the group key.

In the properties tab, select Top, 3.

The entire mapping should look like this.

This will give us the top 3 employees earning maximum salary in their respective departments.

How do you remove Duplicate records in Informatica? And how many ways are there to do it?

There are several ways to remove duplicates.

If the source is DBMS, you can use the property in Source Qualifier to select the distinct records.Or you can also use the SQL Override to perform the same.

You can use, Aggregator and select all the ports as key to get the distinct values. After you pass all the required ports to the Aggregator, select all those ports , those you need to select for de-duplication. If you want to find the duplicates based on the entire columns, select all the ports as group by key. The Mapping will look like this.

You can use Sorter and use the Sort Distinct Property to get the distinct values. Configure the sorter in the following way to enable this.

Let’s assume the data is not sorted. We are using a sorter to sort the data. The Key for sorting would be Employee_ID. Configure the Sorter as mentioned below.

Use one expression transformation to flag the duplicates. We will use the variable ports to identify the duplicate entries, based on Employee_ID.

Use a filter transformation, only to pass IS_DUP = 0. As from the previous expression transformation, we will have IS_DUP =0 attached to only records, which are unique. If IS_DUP > 0, that means, those are duplicate entries.

Add the ports to the target. The entire mapping should look like this.

v. When you change the property of the Lookup transformation to use the Dynamic Cache, a new port is added to the transformation. NewLookupRow.

The Dynamic Cache can update the cache, as and when it is reading the data.

If the source has duplicate records, you can also use Dynamic Lookup cache and then router to select only the distinct one.

3 What is a Joiner Transformation and why it is an Active one?

A Joiner is an Active and Connected transformation used to join source data from the same source system or from two related heterogeneous sources residing in different locations or file systems.

The Joiner transformation joins sources with at least one matching column. The Joiner transformation uses a condition that matches one or more pairs of columns between the two sources.

The two input pipelines include a master pipeline and a detail pipeline or a master and a detail branch. The master pipeline ends at the Joiner transformation, while the detail pipeline continues to the target.

In the Joiner transformation, we must configure the transformation properties namely Join Condition, Join Type and Sorted Input option to improve Integration Service performance.

The join condition contains ports from both input sources that must match for the Integration Service to join two rows. Depending on the type of join selected, the Integration Service either adds the row to the result set or discards the row.

The Joiner transformation produces result sets based on the join type, condition, and input data sources. Hence it is an Active transformation.

Session # 12 – Aggregator Transformation in Informatica | Informatica Training for Biginners