Interview Questions On Informatica Aggregator Transformation

1. What is aggregator transformation?

Aggregator transformation is an active transformation and its connected, mainly used to perform calculations on group of rows

2. What is the default behavior of aggregator transformation?

By default it gives last row as output in each group

3. What happens if you don't select group by ports in aggregator?

If you don't select any group by ports, by default it treats all rows as single group and returns last row

4. What happens if you select all group by ports in aggregator?

If you select all ports in aggregator, it applies grouping on all ports and eliminates duplicate records

5. What are different types of caches in aggregator transformation?

a. Index Cache ==> Contains Group by Ports Information
b. Data  Cache ==> Contains Aggregated Calculation Ports Information

Index Cache file has extension .idx
Data   Cache file has extension .dat

6. What are different types of aggregate functions in aggregator?

a. Sum()
b. Max()
c. Min()
d. Avg()
e. First()
f.  Last() etc

7. What are conditional statements in aggregator transformation?

While performing aggregate calculations, we can use conditional statements with the use of IIF

Eg:- SUM(IIF(CITY='HYDERABAD',SAL,0))

The above example calculates only HYDERABAD city employees total

8. What are nested aggregate functions in aggregator transformation?

If you place one aggregate function in another then it is called nesting of aggregation.

Eg:- MAX(SUM(SAL))

9. What happens if group by port contains null values?

 All rows containing null values will treat as separate group

10. Can you create a variable port in aggregator?

Yes we can create variable ports in aggregator

All above information you can see in below video with examples


https://www.youtube.com/watch?v=SS0-XdluF1Q&list=PLmgctEn1nRyXVj-cfFGpext8j2_qJtzCf



11. What are the optimization techniques in aggregator transformaion?

There are two optimization techniques in aggregator transformation

a. Sorted Input

b. Incremental Aggregation

12. How to set sorted input option in aggregator transformation?

Sorted Input option available under properties tab of aggregator transformation. When you select this option you need to send the sorted data to the aggregator based on group by ports. For example if you take DNO is the group by port then you need to sort on DNO.

13. How sorted input helps in increasing performance of aggregator transformation?

When we sort the data and send to aggregator, then immediately performs aggregate calcuations. It won't wait till all rows read from source. Informatica can't read all rows at a time, it will read block by block. If you don't select sorted input, aggregator will wait till all rows read from source

14. What is incremental aggregation?

Incremental aggregation means aggregating values based on existing aggregated values. Below example clarifies.

First time we received below rows
eno   name     sal      dno
100    ravi      2000   10
101    vinay    3000   10

Ouput :-  10  5000

Next time new employee added in same department

102  raju  4000

this time when you use incremental aggregation it peforms 5000+4000 instead of again adding all employee salaries 2000+3000+4000.

15. When cache files deleted for incremental aggregation what happens?

Session won't fail, it will recreate the cache automatically

16. Can we select both sorted input and incremental aggregation at a time?

No, we can't select. Reason is if you select sorted input it performs calculations in memory. When you select incremental aggregation it performs calculations on existing cache values. 

17. Where cache files stored?

The cache files by default stored under server/infa_shared/cache direcotry.

18. When cache files will be deleted?

Automatically cache files will be deleted after session succeeded

19. Cache files will be deleted if session fails?

If session fails, cache files won't delete, we need to manually delete

20. How to get first and last record using aggregator?

By default you can get last record, to get first record use First() function for each port. Source, SQ, two aggregators, by default one aggregator returns last row, another aggregator select first and connect to two target instances.