Trajectory Data Warehouses - Data Warehouse Systems: Design and Implementation - page 486

SELECT R.RoadKey, COUNT(*)
FROM SegmentS,RoadR,TimeT,TruckU
WHERE S.RoadKey = R.RoadKey AND S.TimeKey = T.TimeKey AND
S.TruckKey = U.TruckKey AND U.TruckBrand = ' Volvo ' AND
T.Date > = ' 2012-02-01 ' AND T.Date < ' 2012-03-01 '
GROUP BY RoadKey
This query addresses the fact table Segment and some of its associated
dimensions. The query does not involve temporal data types, not even
geometric characteristics of spatial dimensions. The query can be used to
identify the segments most frequently used by Northwind trucks.
We give next an example of an OLAP query involving the Delivery
dimension and two possible solutions for it.
Query 12.2. Average duration of deliveries that have one segment which
started in the city of Brussels in the last quarter of 2012.
SELECT AVG(D.TotalDuration)
FROM Delivery D
WHERE EXISTS (
SELECT *
FROM Segment S, StartLocation L, City C, Time T
WHERE S.DeliveryKey = D.DeliveryKey AND
S.StartLocationKey = L.LocationKey AND
L.CityKey = C.CityKey AND C.CityName = ' Brussels ' AND
S.TimeKey = T.TimeKey AND T.Quarter = ' Q4 2012 '
Here, for each instance of the Delivery dimension, the inner query verifies
that at least one segment of the delivery started in the city of Brussels and
occurred on the last quarter of 2012. Notice that the total duration of the
deliveries is precomputed in the Delivery dimension and therefore it is possible
to apply the function average to them. If the duration of the whole deliveries
must be calculated from the measure Duration of the fact table, then the
query would be written as follows:
WITH DeliveryTotal AS (
SELECT D.DeliveryKey, SUM(Duration) AS TotalDuration
FROM Delivery D, Segment S
WHERE D.DeliveryKey = S.DeliveryKey
GROUP BY D.DeliveryKey )
SELECT AVG(TotalDuration)
FROM DeliveryTotal D
WHERE EXISTS (
SELECT *
FROM Segment S, StartLocation L, City C, Time T
WHERE S.DeliveryKey = D.DeliveryKey AND
S.StartLocationKey = L.LocationKey AND
L.CityKey = C.CityKey AND C.CityName = ' Brussels ' AND
S.TimeKey = T.TimeKey AND T.Quarter = ' Q4 2012 '
In the version above, a temporary table DeliveryTotal computes the total
duration of a delivery by adding the duration of all its segments. Then, the