My first Publication Agile-Data-Warehouse-Design-eBook | Page 241

Dimensional Design Patterns for Time and Location
221
International Travel
To enable BI carbon footprint analysis, Pomegranate stakeholders have modeled
the national and international flights taken by their global sales and consulting
force. The resulting EMPLOYEE FLIGHTS event table, Figure 7-10 contains 6
event stories—6 flights taken by employee Bond during July 2011. These are typical
movement stories containing pairs of when and where details that give rise to
interesting when and where related measures, such as distance, duration and speed,
in addition to other explicit facts such as their associated costs; e.g., CO 2 emissions.
Events with pairs of
when and where
details are typically
movements with
interesting distance,
duration and speed
measures
Figure 7-10
Flight events for
employee James
Bond
Movement doesn’t have to be from one geographic location to another; it can be
between virtual locations, such as the URLs of a website, or between members of
a social network. Many of the same questions apply: How long does it take to
navigate from page A to B or pass intelligence from James Bond to Jason Bourne
and how far apart are they (measured in page links or people rather than miles)?
Figure 7-11 shows the flight events modeled as a star schema using the
CALENDAR, CLOCK, and AIRPORT dimensions to play multiple roles of depar-
ture and arrival times and locations. This design can easily be used to answer many
of the stakeholders’ questions:
Which Employees travel the most frequently and furthest?
Which Airlines are used most often?
Which Airlines have the lowest CO2 figures on the routes we use?
!
But it makes one rather important question surprisingly difficult:
Where do our employees need to travel to on business?
!