iAnywhere.com - Using CUBE

Using CUBE

The CUBE operation is an OLAP feature. OLAP is a way of organizing data to fit the way you analyze and manage it so that it takes less time and effort to generate the result sets that you need. You precalculate the summary values for the reports, which speeds up report calculation. CUBES are useful when you want to work with a large amount of data in your reports.

Like ROLLUP, the CUBE operator provides subtotals of aggregate values in the result set. When the CUBE operation is performed on variables, the result set includes many subtotal rows based on combinations of the values of the variables. Unlike ROLLUP, CUBE returns all possible combinations of the variables that you specify in the query.

The CUBE operator returns a result set with added information of dimensions to the data. You can further analyze columns of your data, which are referred to as dimensions. CUBE provides a cross tabulation report of all possible combinations of the dimensions and generates a result set that shows aggregates for all combinations of values in selected columns.

Note

CUBE is particularly useful when your dimensions are not a part of the same hierarchy.

This SQL syntax...	Defines the following sets...
GROUP BY CUBE (A, B, C);	(A, B, C) (A, B) (A, C) (A) (B, C) (B) (C) ( )

This SQL syntax...

Defines the following sets...

GROUP BY CUBE (A, B, C);

(A, B, C)

(A, B)

(A, C)

(A)

(B, C)

(B)

(C)

( )

For more information about rows returned by CUBE queries, see About OLAP operations. For more information about using GROUP BY clauses in queries, see GROUP BY clause.

Example

In the example that follows, the query returns a result set that summarizes the total number of orders and then calculates subtotals for the number of orders by year and quarter.

Note

As the number of variables that you want to compare increases, so too does the complexity of the cube that the query returns.

SELECT year (order_date) AS Year, quarter (order_date) AS Quarter, COUNT (*) AS Orders
FROM sales_order
GROUP BY CUBE (Year, Quarter)
ORDER BY Year, Quarter

The table that follows represents the result set from the query. The subtotal rows are highlighted in the result set. Each subtotal row has a NULL in the column or columns over which the subtotal is computed.

A result set from a CUBE query showing the grand total and subtotalled number of sales orders.

The first highlighted row [1] represents the total number of orders across both years and all quarters. The value in the Orders column is the sum of the values in each of the rows marked [3]. It is also the sum of the four values in the rows marked [2].

Note

All CUBE operations return result sets with at least one row where NULL appears in each column except for the aggregate column. This row represents the summary of each column to the aggregate function.

The next set of highlighted rows [2] represents the total number of orders by quarter across both years. The following two rows marked by [3] represent the total number of orders across all quarters first in the year 2000 and then in the year 2001.

Understanding CUBE

The CUBE operation is equivalent to a GROUPING SETS query that includes all possible combinations of variables.

This CUBE query...	Is equivalent to this query without CUBE...
SELECT A, B, C, SUM( D ) FROM t GROUP BY CUBE( A, B, C)	SELECT A, B, C, SUM( D ) FROM t GROUP BY GROUPING SETS( ( A, B, C ), ( A, B ), ( A ), ( B, C ), ( B ), ( A, C ), ( C ), () )

For information about the GROUPING SETS syntax used in the preceding SQL query, see Using GROUPING SETS.