Advanced Aggregation

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);

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.



Comments:

hi,
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.
regards,

Posted by guest on December 31, 2011 at 09:27 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today