ODI 12c - Aggregating Data

This posting will look at the aggregation component that was introduced in ODI 12c. For many ETL tool users this shouldn't be a big surprise, its a little different than ODI 11g but for good reason. You can use this component for composing data with relational like operations such as sum, average and so forth. Also, Oracle SQL supports special functions called Analytic SQL functions, you can use a specially configured aggregation component or the expression component for these now in ODI 12c. In database systems an aggregate transformation is a transformation where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning - that's exactly the purpose of the aggregate component.

In the image below you can see the aggregate component in action within a mapping, for how this and a few other examples are built look at the ODI 12c Aggregation Viewlet here - the viewlet illustrates a simple aggregation being built and then some Oracle analytic SQL such as AVG(EMP.SAL) OVER (PARTITION BY EMP.DEPTNO) built using both the aggregate component and the expression component.

In 11g you used to just write the aggregate expression directly on the target, this made life easy for some cases, but it wan't a very obvious gesture plus had other drawbacks with ordering of transformations (agg before join/lookup. after set and so forth) and supporting analytic SQL for example - there are a lot of postings from creative folks working around this in 11g - anything from customizing KMs, to bypassing aggregation analysis in the ODI code generator.

The aggregate component has a few interesting aspects.

1. Firstly and foremost it defines the attributes projected from it - ODI automatically will perform the grouping all you do is define the aggregation expressions for those columns aggregated. In 12c you can control this automatic grouping behavior so that you get the code you desire, so you can indicate that an attribute should not be included in the group by, that's what I did in the analytic SQL example using the aggregate component.

2. The component has a few other properties of interest; it has a HAVING clause and a manual group by clause. The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause. Because it acts on the results of the GROUP BY clause, aggregation functions can be used in the HAVING clause predicate, in 11g the filter was overloaded and used for both having clause and filter clause, this is no longer the case. If a filter is after an aggregate, it is after the aggregate (not sometimes after, sometimes having). 

3. The manual group by clause let's you use special database grouping grammar if you need to. For example Oracle has a wealth of highly specialized grouping capabilities for data warehousing such as the CUBE function. If you want to use specialized functions like that you can manually define the code here. The example below shows the use of a manual group from an example in the Oracle database data warehousing guide where the SUM aggregate function is used along with the CUBE function in the group by clause.

The SQL I am trying to generate looks like the following from the data warehousing guide;

  1. SELECT channel_desc, calendar_month_desc, countries.country_iso_code,
  2.       TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
  3. FROM sales, customers, times, channels, countries
  4. WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND
  5.   sales.channel_id= channels.channel_id
  6.  AND customers.country_id = countries.country_id
  7.  AND channels.channel_desc IN
  8.   ('Direct Sales', 'Internet') AND times.calendar_month_desc IN
  9.   ('2000-09', '2000-10') AND countries.country_iso_code IN ('GB', 'US')
  10. GROUP BY CUBE(channel_desc, calendar_month_desc, countries.country_iso_code);

I can capture the source datastores, the filters and joins using ODI's dataset (or as a traditional flow) which enables us to incrementally design the mapping and the aggregate component for the sum and group by as follows;

In the above mapping you can see the joins and filters declared in ODI's dataset, allowing you to capture the relationships of the datastores required in an entity-relationship style just like ODI 11g. The mix of ODI's declarative design and the common flow design provides for a familiar design experience. The example below illustrates flow design (basic arbitrary ordering) - a table load where only the employees who have maximum commission are loaded into a target. The maximum commission is retrieved from the bonus datastore and there is a look using employees as the driving table and only those with maximum commission projected.

Hopefully this has given you a taster for some of the new capabilities provided by the aggregate component in ODI 12c. In summary, the actions should be much more consistent in behavior and more easily discoverable for users, the use of the components in a flow graph also supports arbitrary designs and the tool (rather than the interface designer) takes care of the realization using ODI's knowledge modules.

Interested to know if a deep dive into each component is interesting for folks. Any thoughts? 


can you please share on how did you make a default of all the data sources together. i mean you have employee and dept under one roof. can you please tell me how you did it. i am trying the above mapping, but couldnt succeed. thanks

Posted by guest on May 09, 2014 at 01:22 AM PDT #


You can either use a dataset and add EMP and DEPT, then drag from EMP.DEPTNO to DEPT.DEPTNO to define the join condition. Or you can build in a flow manner - add EMP, add DEPT, add JOIN component, drag EMP.DEPTNO to JOIN, drag DEPT.DEPTNO to JOIN. Now add AGGREGATE component and connect JOIN to aggregate. When you drag the attributes above the join condition (EMP.DEPTNO=DEPT.DEPTNO) will be constructed. Within the join you can specify more details - do you want inner/outer etc.


Posted by David on May 09, 2014 at 07:01 AM PDT #

How 2 write mapping code in odi 12c using odi sdk 12c api pls help me

Posted by guest on November 24, 2014 at 02:02 AM PST #


This blog link below has a zip with example groovy to create many mapping cases defined in the blog including aggregation;

There is also a useful builder which shows generating mappings from a file, it doesn't include aggregation I don't think but it might give you ideas;


Posted by David on November 24, 2014 at 08:33 AM PST #

Deep dives into the other components are a good idea in my opinion. Also is their any documentation on how to build your own custom Component KMs?

Posted by SH on February 23, 2015 at 06:47 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality


« February 2015