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

location-specific
7
W HEN AND W HERE
Dimensional Design Patterns for Time and Location
The past is a foreign country: they do things differently there.
— L.P. Hartley, The Go-Between
Every business event happens at a point in time or represents an interval of time.
Time is the primary way that BI queries group (“show me monthly totals”), filter
(“show me sales for Financial Q1”), and compare business events (“How are we
doing year to date, versus last year?”). That is why every fact table has at least one
time (when) dimension. Time is the most
Most business events occur at a specific geographical or online location. Many
interesting events represent changes of location. Hence, a large number of fact
tables have distinct where dimensions in addition to the location attributes that can
be found in who and what dimensions, such as customer and product. Location dimensions
Although when and where are separate dimensions, they can influence one an-
other: Time zones, holidays and seasons, are all examples of location-specific time
attributes that are affected by event geography. Similarly, analytically significant
locations such as the first and last locations in a sequence of events are timing-
specific location dimensions, affected by event chronology. Time and location
In this chapter, we describe dimensional design patterns for efficiently handling
time and location, in particular, patterns for correctly analyzing year-to-date facts,
and journeys—facts that represent changes in space and time, that are all about
where and when. This chapter
Efficient date and time reporting
frequently used
dimension for BI
analysis
and attributes are
frequently used too
are separate
dimensions but can
affect one another
describes when and
where patterns
Chapter 7 Design
Correct year-to-date analysis Challenges
Time zones, international holidays and seasons At a Glance
National language support
Trip and journey analysis
203