The OWB 10.2.0.3 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);
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:
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.
