The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling

Chapter 11. Transportation

Voyages occur whenever a person or thing travels from one point to another, perhaps with stops in the middle. Obviously, this applies directly to organizations involved in the travel industry. Shippers, as well as internal logistical functions, also will relate to the discussion, as will package delivery services and car rental agencies. Somewhat unexpectedly, many of the characteristics in this chapter's schema are also applicable to telecommunications network route analysis. A phone network can be thought of as a map of possible voyages that a call makes between origin and destination phone numbers.

In this chapter we'll draw on an airline frequent flyer case study to explore voyages and routes because many readers are familiar (perhaps too familiar) with the subject matter. The case study lends itself to a discussion of multiple fact tables at different granularities. We'll also expand on several concepts, such as dimension role-playing and additional date and time dimension considerations. As usual, the intended audience for this chapter should not be limited to the industries just listed.

Note

Chapter 11 discusses the following concepts:

• Fact tables at different levels of granularity

• Combining role-playing dimensions into a superdimension in certain situations

• Country-specific date dimensions

• Time of day as a fact versus dimension

• Dates and times in multiple time zones

Airline Frequent Flyer Case Study

In this case the airline's marketing department wants to ...