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?
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.
