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

TOPPERCENT(Sales, Quantity, 70) BY City, Category ORDER BY Quantity DESC
The rank operation also requires the specification of the order of the cells.
As an example, to rank quarters by category and city order by descending
quantity, as shown in Fig. 3.4 p, we can write
RANK(Sales, Time) BY Category, City ORDER BY Quantity DESC
The rank and the dense rank operations differ in the case of ties. The former
assigns the same rank to ties, with the next ranking(s) skipped. For example,
in Fig. 3.4 p, there is a tie in the quarters for Seafood and Koln ,where Q2 and
Q4 are in the first rank and Q3 and Q1 are in the third and fourth ranks,
respectively. If the dense rank is used, then Q3 and Q1 would be in the second
and third ranks, respectively.
In the examples above, the new measure value in a cell is computed from
the values of other measures in the same cell. However, we often need to
compute measures where the value of a cell is obtained by aggregating the
measures of several nearby cells. Examples of these include moving average
and year-to-date computations. For this, we need to define a subcube that
is associated with each cell and perform the aggregation over this subcube.
These functions correspond to the window functions in SQL that will be
described in Chap. 5 . For example, given the cube in Fig. 3.4 c, the 3-month
moving average in Fig. 3.4 q can be obtained by
ADDMEASURE(Sales, MovAvg = AVG(Quantity) OVER
Time 2 CELLS PRECEDING)
Here, the moving average for January is equal to the measure in January,
since there are no previous cells. Analogously, the measure for February is
the average of the values of January and February. Finally, the average for
the remaining months is computed from the measure value of the current
month and the two preceding ones. Notice that in the window functions,
it is supposed that the members of the dimension over which the window is
constructed are already sorted. For this, a sort operation can be applied prior
to the application of the window aggregate function.
Similarly, to compute the year-to-date sum in Fig. 3.4 r, we can write
ADDMEASURE(Sales, YTDQuantity = SUM(Quantity) OVER
Time ALL CELLS PRECEDING)
Here, the window over which the aggregation function is applied contains
the current cell and all the previous ones, as indicated by ALL CELLS
PRECEDING .
The union operation merges two cubes that have the same schema
but disjoint instances. For example, if CubeSpain is a cube having the
same schema as our original cube but containing only the sales to Spanish
customers, the cube in Fig. 3.4 sisobtainedby
UNION(Sales, SalesSpain)