Data Warehouse Concepts - Data Warehouse Systems: Design and Implementation - page 84

3.9 Describe the various components of a typical data warehouse architec-
ture. Identify variants of this architecture and specify in what situations
they are used.
3.10 Briefly describe the multidimensional model implemented in Analysis
Services.
3.10 Exercises
3.1 A data warehouse of a telephone provider consists of five dimensions:
caller customer, callee customer, time, call type, and call program and
three measures: number of calls, duration, and amount.
Define the OLAP operations to be performed in order to answer the
following queries. Propose the dimension hierarchies when needed.
(a) Total amount collected by each call program in 2012.
(b) Total duration of calls made by customers from Brussels in 2012.
(c) Total number of weekend calls made by customers from Brussels to
customers in Antwerp in 2012.
(d) Total duration of international calls started by customers in Belgium
in 2012.
(e) Total amount collected from customers in Brussels who are enrolled
in the corporate program in 2012.
3.2 A data warehouse of a train company contains information about train
segments. It consists of six dimensions, namely, departure station, arrival
station, trip, train, arrival time, and departure time, and three measures,
namely, number of passengers, duration, and number of kilometers.
Define the OLAP operations to be performed in order to answer the
following queries. Propose the dimension hierarchies when needed.
(a) Total number of kilometers made by Alstom trains during 2012
departing from French or Belgian stations.
(b) Total duration of international trips during 2012, that is, trips
departing from a station located in a country and arriving at a station
located in another country.
(c) Total number of trips that departed from or arrived at Paris during
July 2012.
(d) Average duration of train segments in Belgium in 2012.
(e) For each trip, average number of passengers per segment, which
means take all the segments of each trip and average the number
of passengers.