Aggregator Transformation in Informatica

Aggregator is an active transformation used to calculate group values.

Ports:

· Input

· Output

· Expression

· Variable

· Group By

Properties:

· Data Cache

· Index cache

· Sorted Input

Steps:

1. Define Source

2. Define Target

Write the Target requirements and Generate/Execute SQL.

AGG_FACT 

· DEPTNO

· TOT_SAL

· MAX_SAL

· MIN_SAL

· AVG_SAL

· COUNT_EMP

Aggregator Transformation in Informatica

Add the required ports like TOT_SAL, MAX_SAL etc.

Aggregator Transformation in Informatica

3. Define Mapping M_AGG

4. Drag Source and Target to the Mapping Designer.

5. Go to the Transformation Menu and click on Create. Select the Transformation type as AGGREGATOR T_AGG.

Aggregator Transformation in Informatica

6. Project the required ports from Source Qualifier to the Aggregator.

Aggregator Transformation in Informatica

7. Edit Aggregator. Go to Ports Tab

Uncheck inputs for all added ports. (Expressions are highlighted)

In the functions tab Aggregate folder is displayed only when it is used in the mapping.

Aggregator Transformation in Informatica

Aggregator Transformation in Informatica

In the Functions tab expand Aggregate folder. Select the function. From the Ports tab select the port.

Validate -> OK.

Apply the functions to respective ports.

Check the Group By check box for the department.

In the Properties tab check the box Sorted Input if the data is already sorted (suppose sorter tranfn is applied before the aggregator, otherwise the agg will sort the input)

Aggregator Transformation in Informatica

Aggregator Transformation in Informatica

Aggregator Transformation in Informatica

Project the required ports from the aggregator to the taget.

Save the Repository.

Create a Session S_AGG

Define a Workflow WF_AGG.

Start the workflow.

Preview the data.

Aggregator Transformation in Informatica