Informatica Interview Questions on Aggregator Transformation

1. What is aggregator transformation?

Aggregator transformation performs aggregate calculations like sum, average, count etc. It is an active transformation, changes the number of rows in the pipeline. Unlike expression transformation (performs calculations on a row-by-row basis), an aggregator transformation performs calculations on group of rows.

 

2. What is aggregate cache?

The integration service creates index and data cache in memory to process the aggregator transformation and stores the data group in index cache, row data in data cache. If the integration service requires more space, it stores the overflow values in cache files.

 

3. How can we improve performance of aggregate transformation?

Use sorted input: Sort the data before passing into aggregator. The integration service uses memory to process the aggregator transformation and it does not use cache memory.

Filter the unwanted data before aggregating.

Limit the number of input/output or output ports to reduce the amount of data the aggregator transformation stores in the data cache.

 

4. What are the different types of aggregate functions?

 

The different types of aggregate functions are listed below:

·         AVG

·         COUNT

·         FIRST

·         LAST

·         MAX

·         MEDIAN

·         MIN

·         PERCENTILE

·         STDDEV

·         SUM

·         VARIANCE

 

5. Why cannot you use both single level and nested aggregate functions in a single aggregate transformation?

 

The nested aggregate function returns only one output row, whereas the single level aggregate function returns more than one row. Since the number of rows returned are not same, you cannot use both single level and nested aggregate functions in the same transformation. If you include both the single level and nested functions in the same aggregator, the designer marks the mapping or mapplet as invalid. So, you need to create separate aggregator transformations.

 

6. Up to how many levels, you can nest the aggregate functions?

 

We can nest up to two levels only.

Example: MAX( SUM( ITEM ) )

 

7. What is incremental aggregation?

 

The integration service performs aggregate calculations and then stores the data in historical cache. Next time when you run the session, the integration service reads only new data and uses the historical cache to perform new aggregation calculations incrementally.

 

8. Why cannot we use sorted input option for incremental aggregation?

 

In incremental aggregation, the aggregate calculations are stored in historical cache on the server. In this historical cache the data need not be in sorted order.  If you give sorted input, the records come as presorted for that particular run but in the historical cache the data may not be in the sorted order. That is why this option is not allowed.

 

9. How the NULL values are handled in Aggregator?

 

You can configure the integration service to treat null values in aggregator functions as NULL or zero. By default the integration service treats null values as NULL in aggregate functions.