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

when asking for the best-selling employee, we must compute the maximum
sales amount but also identify who is the employee that performed best.
Therefore, when applying an aggregation operation, the resulting cube will
have different dimension members depending on the type of the aggregation
function. For example, given the cube in Fig. 3.4 a, the total overall quantity
can be obtained by the expression
SUM(Sales, Quantity)
This will yield a single cell, whose coordinates for the three dimensions will
be all equal to all . On the other hand, when computing the overall maximum
quantity as follows
MAX(Sales, Quantity)
we will obtain the cell with value 47 and coordinates Q4 , Condiments ,and
Paris (we suppose that cells that are hidden in Fig. 3.4 a contain a smaller
value for this measure). Similarly, the following expression
SUM(Sales, Quantity) BY Time, Customer
returns the total sales by quarter and customer, resulting in the cube given
in Fig. 3.4 k. This cube has three dimensions, where the Product dimension
only contains the member all . On the other hand,
MAX(Sales, Quantity) BY Time, Customer
will yield the cube in Fig. 3.4 l, where only the cells containing the maximum
by time and customer will have values, while the other ones will be filled with
null values. Similarly, the two maximum quantities by product and customer
asshowninFig. 3.4 m can be obtained as follows:
MAX(Sales, Quantity, 2) BY Time, Customer
Notice that in the example above, we requested the two maximum quantities
by time and customer. If in the cube there are two or more cells that tie for
the last place in the limited result set, then the number of cells in the result
could be greater than two. For example, this is the case in Fig. 3.4 mfor Berlin
and Q1 , where there are three values in the result, that is, 33, 25, and 25.
To compute top or bottom percentages, the order of the cells must be
specified. For example, to compute the top 70% of the measure quantity by
city and category ordered by quarter, as shown in Fig. 3.4 n, we can write
TOPPERCENT(Sales, Quantity, 70) BY City, Category ORDER BY Quarter ASC
The operation computes the running sum of the sales by city and category
starting with the first quarter and continues until the target percentage is
reached. In the example above, the sales by city and category for the first
three quarters covers 70% of the sales. Similarly, the top 70% of the measure
quantity by city and category ordered by quantity, as shown in Fig. 3.4 o, can
be obtained by