Logical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 143

The result is given in Fig. 5.16 . Note that each tuple in the table represents
a cell in the data cube. For example, the fourth tuple represents the total
sales amount to customer c1 . The penultimate tuple represents the total sales
amount of product p3 . Finally, the last tuple represents the total sales amount
of all products to all customers. In this example, for clarity, we did not include
hierarchies. However, cubes with hierarchies can be analyzed analogously.

ProductKey CustomerKey SalesAmount
p1 c1 100

p2 c1 70

p3 c1 30

NULL c1 200

p1 c2 105

p2 c2 60

p3 c2 40

NULL c2 205

p1 c3 100

p2 c3 40

p3 c3 50

NULL c3 190

p1 NULL 305

p2 NULL 170

p3 NULL 120

NULL NULL 595

Fig. 5.16 Data cube corresponding to the fact table in Fig. 5.15 b

5.8.2 ROLLUP, CUBE, and GROUPING SETS

Computing a cube with n dimensions in the way described above would
require 2 n GROUP BY statements, which is not very ecient. For this reason,
SQL/OLAP extends the GROUP BY clause with the ROLLUP and CUBE
operators. The former computes group subtotals in the order given by a list
of attributes. The latter computes all totals of such a list. Over the grouped
tuples, the HAVING clause can be applied, as in the case of a typical GROUP
BY . The syntax of both statements applied to our example above are

SELECT ProductKey, CustomerKey, SUM(SalesAmount)
FROM Sales
GROUP BY ROLLUP(ProductKey, CustomerKey)

SELECT ProductKey, CustomerKey, SUM(SalesAmount)
FROM Sales
GROUP BY CUBE(ProductKey, CustomerKey)