Data Warehouse Concepts - Data Warehouse Systems: Design and Implementation - page 63

Now, the user wants to apply window operations to the cube in Fig. 3.4 c
in order to see how monthly sales behave. She starts by requesting a 3-month
moving average to obtain the result in Fig. 3.4 q. Then, she asks the year-to-
date computation whose result is given in Fig. 3.4 r.
Finally, the user wants to add to the original cube data from Spain, which
are contained in another cube. She obtains this by performing a union of the
two cubes, whose result is given in Fig. 3.4 s. As another operation, she also
wants to remove from the original cube all sales measures except the top two
sales by quarter and city. For this, she performs the difference of the original
cube in Fig. 3.4 a and the cube in Fig. 3.4 m, yielding the result in Fig. 3.4 t.
The OLAP operations illustrated in Fig. 3.4 canbedefinedinaway
analogous to the relational algebra operations introduced in Chap. 2 .
The roll-up operation aggregates measures along a dimension hierarchy
(using an aggregate function) to obtain measures at a coarser granularity.
The syntax for the roll-up operation is:

ROLLUP(CubeName, (Dimension → Level)*, AggFunction(Measure)*)

where Dimension Level indicates to which level in a dimension the roll-up

is to be performed and function AggFunction is applied to summarize the
measure. When there is more than one measure in a cube, we must specify
an aggregate function for each measure that will be kept in the cube. All the
measures for which the aggregation is not specified will be removed from the
cube. In the example given in Fig. 3.4 b, we applied the operation:

→

ROLLUP(Sales, Customer → Country, SUM(Quantity))

When querying a cube, a usual operation is to roll up a few dimensions
to particular levels and to remove the other dimensions through a roll-up
to the All level. In a cube with n dimensions, this can be obtained by
applying n successive ROLLUP operations. The ROLLUP* operation provides
a shorthand notation for this sequence of operations. The syntax is as follows:

ROLLUP*(CubeName, [(Dimension → Level)*], AggFunction(Measure)*)

For example, the total quantity by quarter can be obtained as follows:

ROLLUP*(Sales, Time → Quarter, SUM(Quantity))

which performs a roll-up along the Time dimension to the Quarter level and
the other dimensions (in this case Customer and Product )tothe All level. On
the other hand, if the dimensions are not specified as in

ROLLUP*(Sales, SUM(Quantity))

all the dimensions of the cube will be rolled up to the All level, yielding a
single cell containing the overall sum of the Quantity measure.