Conceptual Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 109

As in the previous query, we first apply a ROLLUP operation, make a copy of
the resulting cube, and join the two cubes with the DRILLACROSS operation.
However, here the join condition is more involved than in the previous query,
since two cases must be considered. In the first one, for the months starting
from February ( Month > 1 ), the cells to be merged must be consecutive and
belong to the same year. In the second case, the cell corresponding to January
must be merged with the one of December from the previous year. Finally,
in the last step, we compute a new measure SalesGrowth as the difference
between the sales amount of the two corresponding months.

Query 4.5. Three best-selling employees.
Sales1 ← ROLLUP*(Sales, Employee → Employee, SUM(SalesAmount))

Result ← MAX(Sales1, SalesAmount, 3)

Here, we roll up all the dimensions of the cube, except Employee ,tothe All
level, while aggregating the measure SalesAmount . Then, the MAX operation
is applied while specifying that cells with the top three values of the measure
are kept in the result.

Query 4.6. Best-selling employee per product and year.
Sales1 ← ROLLUP*(Sales, Employee → Employee,

Product → Product, OrderDate → Year, SUM(SalesAmount))

Result ← MAX(Sales1, SalesAmount) BY Product, OrderDate

In this query, we roll up the dimensions of the cube as specified. Then, the
MAX operation is applied after grouping by Product and OrderDate .

Query 4.7. Countries that account for top 50% of the sales amount.
Sales1 ← ROLLUP*(Sales, Customer → Country, SUM(SalesAmount))

Result TOPPERCENT(Sales1, Customer, 50) ORDER BY SalesAmount DESC

Here,werollupthe Customer dimension to Country level and the other
dimensions to the All level. Then, the TOPPERCENT operation selects the
countries that cumulatively account for top 50% of the sales amount.

←

Query 4.8. Total sales and average monthly sales by employee and year.
Sales1 ← ROLLUP*(Sales, Employee → Employee, OrderDate → Month,

SUM(SalesAmount))
Result ← ROLLUP*(Sales1, Employee → Employee, OrderDate → Year,
SUM(SalesAmount), AVG(SalesAmount))
Here, we first roll up the cube to the Employee and Month levels by summing
the SalesAmount measure. Then, we perform a second roll-up to the Year level
to obtain to overall sales and the average of monthly sales.

Query 4.9. Total sales amount and total discount amount per product and
month.
Sales1 ← ADDMEASURE(Sales, TotalDisc = Discount * Quantity * UnitPrice)
Result ← ROLLUP*(Sales1, Product → Product, OrderDate → Month,
SUM(SalesAmount), SUM(TotalDisc))