Pointer Corporation

The Information Technology Architects

Project Management and Database Solutions ...
From the Desktop to the World

Oracle Rollup and Cube Queries

Making the Business Intelligence Process a Little Easier.

In the heart of any business intelligence process today, you'll find a data warehouse surrounded by managers and executives who need to retrieve some mission-critical data -- as fast and elaborate as possible.

Your challenge as the technology expert at the organization, however, is to achieve the above goal without resorting to any unnecessary steps.  Rollup and Cube queries are among the little-known tools that help you do just that.

Let's look at a sample table in your data warehouse, as shown by the Oracle Enterprise Manager:

CUSTOMER SALESREP TRANDATE ITEM QTY UNITPRICE EXTAMOUNT
C1 R1 12-Aug-2002 12:00:00 AM A 2 10 20
C1 R1 12-Aug-2002 12:00:00 AM B 3 20 60
C1 R1 13-Aug-2002 12:00:00 AM A 4 10 40
C1 R1 13-Aug-2002 12:00:00 AM B 1 20 20
C1 R2 12-Aug-2002 12:00:00 AM A 3 10 30
C1 R2 12-Aug-2002 12:00:00 AM B 2 20 40
C1 R2 13-Aug-2002 12:00:00 AM A 6 10 60
C1 R2 13-Aug-2002 12:00:00 AM B 5 20 100
C2 R1 12-Aug-2002 12:00:00 AM A 5 10 50
C2 R1 12-Aug-2002 12:00:00 AM B 9 20 180
C2 R1 13-Aug-2002 12:00:00 AM A 10 10 100
C2 R1 13-Aug-2002 12:00:00 AM B 4 20 80
C2 R2 12-Aug-2002 12:00:00 AM A 1 10 10
C2 R2 12-Aug-2002 12:00:00 AM B 7 20 140
C2 R2 13-Aug-2002 12:00:00 AM A 8 10 80
C2 R2 13-Aug-2002 12:00:00 AM B 6 20 120

What you're trying to do with this table is to summarize it by CUSTOMER and SALESREP.  Your typical GROUP BY query will produce the following results:

SQL> select customer, salesrep, sum(extamount) from sales group by customer,salesrep;

CUSTOMER SALESREP SUM(EXTAMOUNT)

---------- ---------- --------------

C1 R1 140

C1 R2 230

C2 R1 410

C2 R2 350

With a ROLLUP query, not only you get the same output, but also a summary of total sales amount for each customer:

SQL> select customer, salesrep, sum(extamount) from sales group by rollup (customer,salesrep);

CUSTOMER SALESREP SUM(EXTAMOUNT)

---------- ---------- --------------

C1 R1 140

C1 R2 230

C1 370

C2 R1 410

C2 R2 350

C2 760

1130

7 rows selected.

And finally, with a CUBE query, you get additional summaries in all dimensions of your data:

SQL> select customer, salesrep, sum(extamount) from sales group by cube (customer,salesrep);

CUSTOMER SALESREP SUM(EXTAMOUNT)

---------- ---------- --------------

C1 R1 140

C1 R2 230

C1 370

C2 R1 410

C2 R2 350

C2 760

R1 550

R2 580

1130

9 rows selected.

The beauty of running ROLLUP and CUBE queries is that the result set is ready to be transferred to other destinations, including HTML, XML, or report distribution tools of your choice.

Ben Aminnia, president of Pointer Corporation,  is a database architect with over 20 years of experience.  He's also the president of Los Angeles SQL Server Professionals Group www.sql.la and a board member of Los Angeles .NET Developers Group www.ladotnet.org.     

 

Copyright © 2008 Pointer Corporation