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

DROPMEASURE(CubeName, Measure*)
For example, given the result of the add measure above, the cube illustrated
in Fig. 3.4 j is expressed by:
DROPMEASURE(Sales2011-2012, Quantity2011, Quantity2012)
We have seen that the roll-up operation aggregates measures when
displaying the cube at coarser level. On the other hand, we also need to
aggregate measures of a cube at the current granularity, that is, without
performing a roll-up operation. The syntax for this is as follows:
AggFunction(CubeName, Measure) [BY Dimension*]
Usual aggregation operations are SUM , AVG , COUNT , MIN ,and MAX .
In addition to these, we use extended versions of MIN and MAX ,which
have an additional argument that is used to obtain the n minimum or
maximum values. Further, TOPPERCENT and BOTTOMPERCENT select
the members of a dimension that cumulatively account for x percent of a
measure. Analogously, RANK and DENSERANK are used to rank the members
of a dimension according to a measure. We show next examples of these
operations.
For example, the cube in Fig. 3.4 aisatthe Quarter and City levels. The
total sales by quarter and city can be obtained by
SUM(Sales, Quantity) BY Time, Customer
This will yield the two-dimensional cube in Fig. 3.4 k. On the other hand, to
obtain the total sales by quarter, we can write
SUM(Sales, Quantity) BY Time
which returns a one-dimensional cube with values for each quarter. Notice
that in the query above, a roll-up along the Customer dimension up to the
All level is performed before applying the aggregation operation. Finally, to
obtain the overall sales, we can write
SUM(Sales, Quantity)
which will result in a single cell.
Aggregation functions in OLAP can be classified in two types. Cumu-
lative aggregation functions compute the measure value of a cell from
several other cells. Examples of cumulative functions are SUM , COUNT ,
and AVG . On the other hand, filtering aggregation functions filter the
members of a dimension that appears in the result. Examples of these
functions are MIN and MAX . The distinction between these two types
of aggregation functions is important in OLAP since filtering aggregation
functions must compute not only the aggregated value but must also
determine the dimension members that belong to the result. As an example,