Join the Community

Sponsor

Free BI Digest

Need more help on this subject?

Aggregation with out Informatica Aggregator

Since Informatica process data on row by row basis, it is generally possible to handle data aggregation operation even without an Aggregator Transformation. On certain cases, you may get huge performance gain using this technique!

General Idea of Aggregation without Aggregator Transformation

Let us take an example: Suppose we want to find the SUM of SALARY for Each Department of the Employee Table. The SQL query for this would be:

SELECT DEPTNO, SUM(SALARY) 
FROM EMP_SRC 
GROUP BY DEPTNO; 

If we need to implement this in Informatica, it would be very easy as we would obviously go for an Aggregator Transformation. By taking the DEPTNO port as GROUP BY and one output port as SUM(SALARY) the problem can be solved easily.

But we want to achieve this without aggregator transformation!

We will use only Expression transformation to achieve the functionality of Aggregator expression. The trick is to use the very funda of the expression transformation of holding the value of an attribute of the previous tuple over here.

But wait... why would we do this? Aren't we complicating the things here?

Yes, we are! But as it appears, in many cases, it might have an performance benefit (especially if the input is already sorted or when you know input data will not violate the order, like you are loading daily data and want to sort it by day). Please see this article to know more about how to improve the performance of Aggregator transformation

Remember Informatica holds all the rows in Aggregator cache for aggregation operation. This needs time and cache space and this also voids the normal row by row processing in Informatica. By removing the Aggregator with an Expression, we reduce cache space requirement and ease out row by row processing. The mapping below will show how to do this.

Mapping for Aggregation with Expression and Sorter only:

Mapping for Aggregation with Expression and Sorter only

  • Sorter (SRT_SAL) Ports Tab

    Sorter1

    Now I am showing a sorter here just illustrate the concept. If you already have sorted data from the source, you need not use this thereby increasing the performance benefit.

  • Expression (EXP_SAL) Ports Tab

    Expression1

  • Sorter (SRT_SAL1) Ports Tab

    Sorter2

  • Expression (EXP_SAL2) Ports Tab

    Expression2

  • Filter (FIL_SAL) Properties Tab

    Filter

This is how we can implement aggregation without using Informatica aggregator transformation.

About the Author

Saurav Mitra

Saurav Mitra is a business intelligence professional.
Connect with the author via Google+
 



If you have any doubt or question on the above article, please Ask your question here. We will surely help you out!

Before you leave, Kindly provide your comments / suggestions / feedback below. Thank You!
Hope this tutorial was helpful. If you have any doubt or question on above, please Ask your question here. We will surely help you out!

If you want to learn more about Informatica, visit our Informatica tutorial page.

And before you leave , please provide your comments / suggestions / appreciations etc. below. Thank You!

 

READ OUR RECOMMENDED ARTICLES