Informatica Aggregator Transforamtion

Informatica Aggregator Transformation is an active transformation. 

Informatica Aggregator Transformation is mainly used to perform calculations on group of rows.

Informatica Aggregator Transformation is used to perform different kinds of aggregate functions such as SUM(), MIN(), MAX(), AVG(), COUNT(), FIRST(), LAST() etc

Informatica Aggregator Transformation allows you to apply conditional clauses with in aggregate functions.

The following example calculates sum of salaries of employees under city NEW YORK

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

Informatica Aggregator Transformation allows you to apply conditional clauses with in aggregate functions.

The following example calculates average of salaries of employees under city NEW YORK

Eg:- AVG(IIF(CITY='NEWYORK',SAL,0))

See the below video for complete Informatica Aggregator Transformation 



https://www.youtube.com/watch?v=SS0-XdluF1Q



By default Informatica Aggregator Transformation returns last row from each group.

Eg:- In a mapping if you use Informatica Aggregator Transformation with out selecting any group by ports, then it returns last row.

Input:-

100  ravi    2000   10
101  vinay  3000   10
102  ramu  4000   20

Output:-

102  ramu  4000   20

Eg:- In a mapping if you use Informatica Aggregator Transformation and selected only dno is the group by port, then it returns last row from each dno.

Input:-

100  ravi    2000   10
101  vinay  3000   10
102  ramu  4000   20

Output:-

101  vinay  3000   10
102  ramu  4000   20

Informatica Aggregator Transformation allows you nesting of aggregate functions. Nesting of aggregate functions means one aggregate function with in another aggregate function.

Eg:- Find Out Which department is paying highest salary

Nesting of Aggregate function in Informatica Aggregator Transformation


MAX(SUM(SAL))

Informatica Aggregator Transforamtion handles null values. Group by port contains null value then it creates separate group.

Eg:- Deparment wise salaries

Input:-

100  ravi    2000   10
101  vinay  3000   10
102  ramu  4000   null

Output:-

10    5000
null  4000

Eg:- Cacluating department wise salaries using Informatica Aggregator Transformation

Source:

create table emp_dept(eno number, ename varchar2(20), sal number, dno number);

Target:

create table dept_sal(dno number, dsal number);

Procedure:

Create source table emp_dept in source user and create target table dept_sal in target user.

1. Select power center designer tool. Double click on repository "rep" enter the userid and pwd.

2. Double click on your folder from the navigation bar.

3. Select source analyzer tool and import the source structure.

4. Select target designer tool and import the target structure.

5. From the menu bar select repository and save.

6. Select the mapping designer tool. From the menu bar select mappings and then click on 

create. Enter the mapping name.

7. Drag the source and target from navigation bar to the workspace.

8. From the menu bar select Transformation and click on create. Select aggregator transformation from the drop down. click on create, enter the name and click on done.

9. Drag only required ports from source qualifier (sal, dno) to aggregator transformation.

10. Double click on aggregator transformation, select the ports tab.

11. Place the mouse courser at the end of the port, click on the icon top right corner, "add a new port to this transformation". By default it shows newfield and datatype string. Change this port name to dsal and set data type to double.

12. Disable the input port (check box). Select the expression and apply the formula sum(sal). Select group by check box on "dno" port.

13. Select the ports dno, dsal from aggregator to target.

14. From the menu bar select repository and click on save.

15. Select workflow manager tool to create a session. Select task developer tool to create a session.

16. From the menu bar select tasks and click on create, enter the name and select the corresponding mapping.

17.Double click on the session, select mapping tab. Enter source and target connections.

18.From the menu bar select repository and save.

19.Select workflow manager tool. Drag the session from navigation bar to workspace. From the menu bar select repository and save. Right click on the session and click on start task.

Optimization Techniques for Informatica Aggregator Transformation

1. Sorted Input

2. Incremental Aggregation