Tutorial corner

Informatica,ETL,oracle,sql/plsql

Aggregator Transformation example in Informatica

Spread the love

Aggregator Transformation example in Informatica : Earlier , we learnt about the Aggregator transformation in informatica in Informatica , steps to create it , configuring the port and properties. We also discussed the business scenario of Aggregator Transformation in Informatica. Lets see a working Example of Aggregator Transformation in Informatica mapping.

Aggregator Transformation image

Aggregator Transformation image

Aggregator Transformation example in Informatica

Create an informatica mapping to populate min , maximum salary , average and sum of salary for each department based on the employee data with the help of  Aggregator Transformation in informatica mapping

Source Data:

EMP Table Data:

EMPNO ENAME JOB SAL DEPT

1

JOHNSON ADMIN

18000

4

2

HARDING MANAGER

52000

3

3

TAFT SALES I

25000

3

4

HOOVER SALES I

27000

3

5

LINCOLN TECH

22500

4

6

GARFIELD MANAGER

54000

4

7

POLK TECH

25000

4

8

GRANT ENGINEER

32000

2

9

JACKSON CEO

75000

4

10

FILLMORE MANAGER

56000

2

11

ADAMS ENGINEER

34000

2

12

WASHINGTON ADMIN

18000

4

13

MONROE ENGINEER

30000

2

14

ROOSEVELT CPA

35000

1

Target Data:

EMP_RES  Data:

EMPNO ENAME DEPT COUNT MIN_SAL MAX_SAL AVG_SAL SUM_SAL

13

MONROE

2

4

30000

56000

38000

152000

4

HOOVER

3

3

25000

52000

34666.67

104000

12

WASHINGTON

4

6

18000

75000

35416.67

212500

14

ROOSEVELT

1

1

35000

35000

35000

35000

Sample Data for above problem can be get from SQL sample data

Solution:

Below are the steps to create full mapping.

  • Create a new mapping m_Aggregator_example by Go to toolbar -> click mapping-> Create
  • Drag Source (EMP) and Target (EMP_RES) to the mapping.
  • Add Aggregator Transformation by “Go to Toolbar->click Transformation -> Create. Select the Aggregator transformation”
  • You can also select Transformation by clicking  function  button on Informatica Designer

    Aggregator Transformation short cut

    Aggregator Transformation short cut

  • Enter the  name “aggr_emp_Salary” and click Done.

    Aggregator Transformation Step 2

    Aggregator Transformation Step 2

  • Drag input port from EMPNO,ENAME, SAL, DEPTNO from SQ_EMP (Source Qualifier ) to aggr_emp_Salary
  • Also add additional below output port (by Clicking on Create port button)
    • COUNT
    • MIN_SAL
    • MAX_SAL
    • AVG_SAL
    • SUM_SAL
  • Check the “Group By” option for the DEPTNO port.
Aggregator Transformation Step 6

Aggregator Transformation Step 4

  • Edit the expression for AVG_SAL ( by clicking on expression editor ) and add below expression

AVG(SAL)

Aggregator Transformation Step 5

Aggregator Transformation Step 5

  • Similarly add below expression for other port as well
    • COUNT   =  COUNT(SAL)
    • MIN_SAL =  MIN(SAL)
    • MAX_SAL =  COUNT(SAL)
    • SUM_SAL =  SUM(SAL)
  • Click the Transformation tab and configure transformation properties. ( change Tracing Level as per your need)

    Aggregator Transformation Step 6

    Aggregator Transformation Step 6

  • To enhance the performance of Aggregator , it is recommended to provide Sorted Data to it (Via SQ query or  by adding Sorter Transformation before it)
  • In case Sorted Input data is coming to Aggregator, check the “Sorted Input” option under the properties Tab.
  • Now link all required port from aggr_emp_Salary to EMP_RES Target Definition,

Please note : Some extra additional fields like  EMPNO , ENAME, (which are not port of aggregation transformation functionally ) are added just to show that during the execution , final output data will contain only the last record per group . Such data is not required although

  • Click on Mapping ( from tool bar) -> then Validate ( to validate the mapping)
  • Now save the mapping ( by clicking on Repository-> Save or you can also press Ctrl+S)
  • Generate the workflow and run it

Over all Mapping:

Full Mapping of Aggregator Transformation (Without Sorter)

Full Mapping of Aggregator Transformation example in Informatica

Full Mapping of Aggregator Transformation example in Informatica

Full Mapping of Aggregator Transformation (With Sorter Transformation)

Full Mapping of Aggregator Transformation example in Informatica With Sorted Data

Full Mapping of Aggregator Transformation example in Informatica With Sorted Data

So, in this article we have gone through step by step example of Aggregator Transformation in Informatica mapping,  learnt the  step to add it , configure the port , properties , creating aggregation ports .

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme