ODI - Aggregate component

• The aggregate component is a projector component which groups and combines attributes using aggregate functions, such as average, count, maximum, sum, and so on.
• ODI will automatically select attributes without aggregation functions to be used as group-by attributes. You can override this by using the Is Group By and Manual Group By Clause properties.

To create an aggregate component:

• Drag and drop the aggregate component from the component palette into the logical diagram.

• Define the attributes of the aggregate if the attributes will be different from the source components. To do this, select the Attributes tab in the property inspector, and click the green plus icon to add attributes. Enter new attribute names in the Target column and assign them appropriate values. If attributes in the aggregate component will be the same as those in a source component, use attribute matching.
• Create a connection from a source component by dragging a line from the connector port of the source to the connector port of the aggregate component.
• The Attribute Matching dialog will be shown. If attributes in the aggregate component will be the same as those in a source component, check the Create Attributes on Target box. If necessary, map all attributes from source to target that were not mapped though attribute matching, and create transformation expressions as necessary .
• In the property inspector, the attributes are listed in a table on the Attributes tab. Specify aggregation functions for each attribute as needed. By default all attributes not mapped using aggregation functions (such as sum, count, avg, max, min, and so on) will be used as Group By.

You can modify an aggregation expression by clicking the attribute. For example, if you want to calculate average salary per department, you might have two attributes: the first attribute called AVG_SAL, which you give the expression AVG(EMP.SAL), while the second attribute called DEPTNO has no expression. If Is Group By is set to Auto, DEPTNO will be automatically included in the GROUP BY clause of the generated code.

You can override this default by changing the property Is Group By on a given attribute from Auto to Yes or No, by double-clicking on the table cell and selecting the desired option from the drop down list.

You can set a different GROUP BY clause other than the default for the entire aggregate component. Select the General tab in the property inspector, and then set a Manual Group by Clause. For example, set the Manual Group by Clause to YEAR(customer.birthdate) to group by birthday year.

Optionally, add a HAVING clause by setting the HAVING property of the aggregate component: for example, SUM(order.amount) > 1000.