Welcome to All Things Warehouse Builder

  • ETL
    January 4, 2008

Advanced Aggregation

David Allan

The OWB and 11g releases of OWB extended the aggregation capabilities to support Oracle's advanced aggregation capabilities. So for example the CUBE and ROLLUP clauses can now be used in the aggregation operator which is great for building summary maps with these simple yet powerful clauses.

To illustrate I have used the example from the Oracle Data Warehousing guide (Example 20-8 GROUPING combined with HAVING);

SELECT channel_desc, calendar_month_desc, country_iso_code,
       TO_CHAR( SUM(amount_sold), '9,999,999,999') SALES$,
       GROUPING(channel_desc) CH,
       GROUPING (calendar_month_desc) MO,
       GROUPING(country_iso_code) CO
FROM sales, customers, times, channels, countries
WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND customers.country_id = countries.country_id AND sales.channel_id= channels.channel_id AND channels.channel_desc IN ('Direct Sales', 'Internet') AND times.calendar_month_desc IN ('2000-09', '2000-10') AND country_iso_code IN ('GB', 'US')
GROUP BY CUBE(channel_desc, calendar_month_desc, country_iso_code)
HAVING (GROUPING(channel_desc)=1 AND GROUPING(calendar_month_desc)= 1 AND GROUPING(country_iso_code)=1) OR (GROUPING(channel_desc)=1 AND GROUPING (calendar_month_desc)= 1) OR (GROUPING(country_iso_code)=1 AND GROUPING(calendar_month_desc)= 1);

In OWB use the AGGREGATOR operator and construct the group by and having clause as:

Map Builder 5:

This and the ability to define a materialized view as a target in a map, then create/bind the materialized view gives a great way for building summaries and importantly capturing the metadata for lineage/impact analysis (see post on federation). Also check out the post on constructing maps from SQL (part 1 and part 2) which uses the example above.

Join the discussion

Comments ( 1 )
  • guest Sunday, January 1, 2012


    is it possible to send me a sample for create a cube have aggregate sum and count for example count(amount) and sum(amount) as through their dimensions.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.