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

4.4 Design a MultiDim schema for the university application given in Ex. 3.3
taking into account the different granularities of the time dimension.
4.5 Design a MultiDim schema for the French horse race application given
in Ex. 2.1 . With respect to the races, the application must be able to
display different statistics about the prizes won by owners, by trainers,
by jockeys, by breeders, by horses, by sires (i.e., fathers), and by
damsires (i.e., maternal grandfathers). With respect to the bettings,
the application must be able to display different statistics about the
payoffs by type, by race, by racetrack, and by horses.
4.6 In each of the dimensions of the multidimensional schema of Ex. 4.5 ,
identify the hierarchies (if any) and determine its type.
4.7 Design a MultiDim schema for the Formula One application given in
Ex. 2.2 . With respect to the races, the application must be able to
display different statistics about the prizes won by drivers, by teams, by
circuit, by Grand Prix, and by season.
4.8 Consider a time dimension composed of two alternative hierarchies: (a)
day, month, quarter, and year and (b) day, month, bimonth, and year.
Design the conceptual schema of this dimension and show examples of
instances.
4.9 Consider the well-known Foodmart cube whose schema is given in
Fig. 4.23 . Write using the OLAP operations the following queries 2 :
(a) All measures for stores.
(b) All measures for stores in the states of California and Washington
summarized at the state level.
(c) All measures for stores in the states of California and Washington
summarized at the city level.
(d) All measures, including the derived ones, for stores in the state of
California summarized at the state and the city levels.
(e) Sales average in 1997 by store state and store type.
(f) Sales profit by store and semester in 1997.
(g) Sales profit percentage by quarter and semester in 1997.
(h) Sales profit by store for the first quarter of each year.
(i) Unit sales by city and percentage of the unit sales of the city with
respect to its state.
(j) Unit sales by city and percentage of the unit sales of the city with
respect to its country.
(k) For promotions other than “No Promotion,” unit sales and percent-
age of the unit sales of the promotion with respect to all promotions.
(l) Unit sales by promotion, year, and quarter.
2 The queries of this exercise are based on a document written by Carl Nolan entitled
“Introduction to Multidimensional Expressions (MDX).”