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

222
Chapter 7
The default from
and to details may
not answer the most
important where
questions
When and Where
ARRIVAL AIRPORT will tell you where airlines are flying employees to—but
that’s not quite the same thing. Figure 7-10 shows that Bond took three flights on
July 18th, each with the REASON of attending a conference. He did not, of course,
attend three conferences in one day, nor did he actually have to go to Amsterdam
or Minneapolis. He simply chose that route from London to the one conference he
needed to attend in Phoenix. Apparently that routing had lower CO2 emissions per
passenger than a direct alternative because it used a larger, newer aircraft.
Figure 7-11
Flight star schema
The most interesting
where details are
typically the first and
last points in a
journey
Bond’s first multi-flight journey can be worked out manually by browsing all his
flights and spotting the short gaps between the connecting flights and the longer
gap that precedes his flight on July 21—which represents a different journey from
Phoenix to New York for a consulting engagement. But getting a journey-level
perspective on all of the flights in a large fact table via BI queries is difficult, be-
cause it involves comparing pairs of flights in the correct order on a per-employee
basis. DW/BI designers don’t want to hear that a query is difficult.
If stakeholders use the prepositions “from” and “to” to connect where details to
the main clause of an event, it is an obvious clue that the event represents move-
ment. Ask stakeholders for related stories such as those in Figure 7-10 to dis-
cover if individual movement events are part of a sequence that describes a
greater journey from an origin to a final destination.