How to perform Group By in ODI11g interfaces

Aggregation is a must in Data Warehousing and Data Integration, as of ODI 12c there exists a designated tool for Group by operations, but in ODI 11g you have to perform a workaround.

Aggregate functions in SQL include:

MIN

MAX

SUM

COUNT

AVG

To perform a query with the above functions, a group by statement has to be used in order to aggregate data.

I will be using a random source file created randomly with ID and different numerical attributes:

Source file set

And a target table created manually in Oracle with identical attributes such as source file:

Target table

Now I will create an interface (GROUP_BY_EXP) which uses the data store's created from above file and table:

Initial Interface

Assuming that I want to find the SUM of Height grouped by ID only, typically this will be achieved with this query:

Mapping only ID and Height in Mappings expressions this is pretty easy:

Mapping Expressions

Executing this interface will generate this code, which indeed is exactly what we expect it to generate:

Code generated in first run

What if we map all of the columns on the target table? And we need the same task Sum(Height) and Group by ID? 

Let's firstly map all of the columns:

Mapping Expressions

Let's execute it:

Code generated in second run

Now this brings an issue because in our case we need only ID used in Group By expression.

In order to avoid this, each of unneeded columns in group by statement: NAME, LENGTH, WIDTH should have aggregate function used in expression, if that does not pose a problem in whatever you are using, then encapsulate each column with MIN/MAX:

Mapping Expressions

Let's execute it:

Code generated in third run

As you can see NAME, HEIGHT and WIDTH are removed from Group By statement whenever there's an aggregate function used on them.

Note: This is a workaround, data should be analyzed after the execution due to the fact that MIN/MAX functions might generate data which can cause trouble on your business logic.