✍ Kashif M
Trends

Sunday, 8 September 2013


Aggregator Transformation in Informatica


By on September 08, 2013




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
Aggregator Transformation in Informatica





Add the required ports like TOT_SAL, MAX_SAL etc.
Aggregator Transformation in Informatica
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
Aggregator Transformation in Informatica


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


Aggregator Transformation in Informatica
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

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
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
Aggregator Transformation in Informatica





Kashif
mkashu: Aggregator Transformation in Informatica
Review : Kashif | Kashif
Update: September 08, 2013 | Rating: 4.5

Comment for "Aggregator Transformation in Informatica"

3 comments:

  1. gud explanation ... can you just brife out first and last function and incremental aggregation with an example

    ReplyDelete
  2. Nice blog thanks for sharing..!

    ReplyDelete
  3. Aggregated is the type an active transformation which allows you to calculate the summary’s for a group of records.Provided information on Aggregator Transformation is good

    ReplyDelete

Blog Archive