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

The tables in Fig. 5.17 a, b show, respectively, the result of the GROUP BY
ROLLUP and the GROUP BY CUBE queries above. In the case of roll-up, in
addition to the detailed data, we can see the total amount by product and
the overall total. For example, the total sales for product p1 is 305. If we also
need the totals by customer, we would need the cube computation, performed
by the second query.

a b

ProductKey CustomerKey SalesAmount
p1

ProductKey CustomerKey SalesAmount
p1

c1 100 c1 100

p1 c2 105 p2 c1 70

p1 c3 100 p3 c1 30

p1 NULL 305 NULL c1 200

p2 c1 70 p1 c2 105

p2 c2 60 p2 c2 60

p2 c3 40 p3 c2 40

p2 NULL 170 NULL c2 205

p3 c1 30 p1 c3 100

p3 c2 40 p2 c3 40

p3 c3 50 p3 c3 50

p3 NULL 120 NULL c3 190

NULL NULL 595 NULL NULL 595

p1 NULL 305

p2 NULL 170

p3 NULL 120

Fig. 5.17 Operators. GROUP BY ROLLUP ( a )and GROUP BY CUBE ( b )

Actually, the ROLLUP and CUBE operators are simply shorthands for a
more powerful operator, called GROUPING SETS , which is used to precisely
specify the aggregations to be computed. For example, the ROLLUP query
above can be written using GROUPING SETS as follows:

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

Analogously, the CUBE query would read:

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