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

Here, we first compute a new measure TotalDisc from three other measures.
Then, we roll up the cube to the Product and Month levels.

Query 4.10. Monthly year-to-date sales for each product category.
Sales1 ← ROLLUP*(Sales, Product → Category, OrderDate → Month,

SUM(SalesAmount))

Result ← ADDMEASURE(Sales1, YTD = SUM(SalesAmount) OVER

OrderDate BY Year ALL CELLS PRECEDING)

Here, we start by performing a roll-up to the category and month levels.
Then, a new measure is created by applying the SUM aggregation function
to a window composed of all preceding cells of the same year. Notice that it
is supposed that the members of the Time dimension are ordered according
to the calendar time.

Query 4.11. Moving average over the last 3 months of the sales amount by
product category.
Sales1 ← ROLLUP*(Sales, Product → Category, OrderDate → Month,

SUM(SalesAmount))

Result ADDMEASURE(Sales1, MovAvg = AVG(SalesAmount) OVER

OrderDate 2 CELLS PRECEDING)
In the first roll-up, we aggregate the SalesAmount measure by category and
month. Then, we compute the moving average over a window containing the
cells corresponding to the current month and the two preceding months.

←

Query 4.12. Personal sales amount made by an employee compared with
the total sales amount made by herself and her subordinates during 1997.
Sales1 ← SLICE(Sales, OrderDate.Year = 1997)

Sales2 ← ROLLUP*(Sales1, Employee → Employee, SUM(SalesAmount))

Sales3 SalesAmount)

Sales4 ← RECROLLUP(Sales2, Employee → Employee, Supervision,
SUM(SalesAmount))
Result ← DRILLACROSS(Sales4, Sales3)
In the first step, we restrict the data in the cube to the year 1997. Then, in
the second step, we perform the aggregation of the sales amount measure by
employee, thus obtaining the sales figures independently of the supervision
hierarchy. Then, in the third step, the obtained measure is renamed. In the
fourth step, we apply the recursive roll-up, which performs an iteration over
the supervision hierarchy by aggregating children to parent until the top level
is reached. Finally, the last step obtains the cube with both measures.

← RENAME(Sales2, PersonalSales ←

Query 4.13. Total sales amount, number of products, and sum of the
quantities sold for each order.
ROLLUP*(Sales, Order → Order, SUM(SalesAmount),
COUNT(Product) AS ProductCount, SUM(Quantity))