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

The ROLLUP* operation is used to specify the levels at which each of the
dimensions Customer , OrderDate ,and Product are rolled up. For the other
dimensions in the cube, a roll-up to All is performed. The SUM operation
is applied to aggregate the measure SalesAmount . All other measures of the
cube are removed from the result.

Query 4.2. Yearly sales amount for each pair of customer country and
supplier countries.
ROLLUP*(Sales, OrderDate

Country,
Supplier → Country, SUM(SalesAmount))
As in the previous query, a roll-up to the specified levels is performed while
performing a SUM operation to aggregate the measure SalesAmount .

→ Year, Customer →

Query 4.3. Monthly sales by customer state compared to those of the
previous year.

Sales1 ← ROLLUP*(Sales, OrderDate → Month, Customer → State,
SUM(SalesAmount))
Sales2 ← RENAME(Sales1, SalesAmount ← PrevYearSalesAmount)
Result ← DRILLACROSS(Sales2, Sales1,
Sales2.OrderDate.Month = Sales1.OrderDate.Month AND
Sales2.OrderDate.Year+1 = Sales1.OrderDate.Year AND
Sales2.Customer.State = Sales1.Customer.State)

Here, we first apply a ROLLUP operation to aggregate the measure Sales-
Amount . Then, a copy of the resulting cube, with the measure renamed as
PrevYearSalesAmount , is kept in the cube Sales2 . The two cubes are joined
with the DRILLACROSS operation, where the join condition ensures that cells
corresponding to the same month of two consecutive years and to the same
client state are merged in a single cell in the result. Although we include the
join condition for the Customer dimension, since it is an equijoin, this is not
mandatory—it is assumed by default for all the dimensions not mentioned
in the join condition. In the following, we do not include the equijoins in the
conditions in the DRILLACROSS operations.

Query 4.4. Monthly sales growth per product, that is, total sales per
product compared to those of the previous month.
Sales1

← ROLLUP*(Sales, OrderDate → Month, Product → Product,

SUM(SalesAmount))

Sales2 ← RENAME(Sales1, SalesAmount ← PrevMonthSalesAmount)

Sales3 ← DRILLACROSS(Sales2, Sales1,

( Sales1.OrderDate.Month > 1AND
Sales2.OrderDate.Month+1 = Sales1.OrderDate.Month AND
Sales2.OrderDate.Year = Sales1.OrderDate.Year ) OR
( Sales1.OrderDate.Month = 1 AND Sales2.OrderDate.Month = 12 AND
Sales2.OrderDate.Year+1 = Sales1.OrderDate.Year ) )

Result ← ADDMEASURE(Sales3, SalesGrowth =

SalesAmount - PrevMonthSalesAmount )