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

Here, we roll up all the dimensions, except Order ,tothe All level, while
adding the SalesAmount and Quantity measures and counting the number of
products.

Query 4.14. For each month, total number of orders, total sales amount,
and average sales amount by order.
Sales1

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

SUM(SalesAmount))

Result ← ROLLUP*(Sales1, OrderDate Month, SUM(SalesAmount),

AVG(SalesAmount) AS AvgSales, COUNT(Order) AS OrderCount)

→

In the query above, we first roll up to the Month and Order levels. Then,
we perform another roll-up to remove the Order dimension and obtain the
requested measures.

Query 4.15. For each employee, total sales amount, number of cities, and
number of states to which she is assigned.
ROLLUP*(Sales, Employee State, SUM(SalesAmount), COUNT(DISTINCT City)

AS NoCities, COUNT(DISTINCT State) AS NoStates)

→

Recall that Territories is a nonstrict hierarchy in the Employee dimension.
In this query, we roll up to the State level while adding the SalesAmount
measure and counting the number of distinct cities and states. Notice that
the ROLLUP* operation takes into account the fact that the hierarchy is
nonstrict and avoids the double-counting problem to which we referred in
Sect. 4.2.6 .

4.5 Summary

This chapter focused on conceptual modeling for data warehouses. As is
the case for databases, conceptual modeling allows user requirements to be
represented while hiding actual implementation details, that is, regardless
of the actual underlying data representation. To explain conceptual mul-
tidimensional modeling, we used the MultiDim model, which is based on
the entity-relationship model and provides an intuitive graphical notation. It
is well known that graphical representations facilitate the understanding of
application requirements by users and designers.
We have presented a comprehensive classification of hierarchies, taking
into account their differences at the schema and at the instance level. We
started by describing balanced, unbalanced, and generalized hierarchies,
all of which account for a single analysis criterion. Recursive (or parent-
child) and ragged hierarchies are special cases of unbalanced and generalized
hierarchies, respectively. Then, we introduced alternative hierarchies, which
are composed of several hierarchies defining various aggregation paths for
the same analysis criterion. We continued with parallel hierarchies, which