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

Dimensional Design Patterns for Time and Location
217
International Time
Problem/Requirement
To analyze global business events, a data warehouse needs to handle international
time correctly. For customer (or employee) behavioral analysis, local time of day,
weekday status, holiday status, and season are important. While an organization-
wide standard time perspective—irrespective of event location—is equally impor-
tant, for measuring simultaneous operational activity and accounting for financial
transactions in the correct fiscal period.
Regardless of how events are originally recorded—using local time or the standard
time of a central application server set to Greenwich Mean Time—converting
between the two requires an understanding of event geography, time zones, and
“daylight saving” that is beyond individual queries. Just how many time zones are
there? It’s not 24!
International events
must be analyzed
by local and
standard time
Converting between
time zones is not
trivial
Solution
If standard organization and local customer time are important, the data ware-
house should provide both as readily available dimension roles to avoid inconsis-
tent and inefficient time zone calculations within reports. For consistency, a shared
ETL process should perform all time zone conversions, and the results should be
used to overload international facts with additional time dimension keys. Figure 7-
8 shows how local time is modeled in a star schema—by overloading a global sales
fact table with extra date and time of day keys (LOCAL DATE KEY and LOCAL
TIME KEY) so that the CALENDAR and CLOCK dimensions can play the dual
roles of Standard Sale Time and Local Sale Time.
Overload the facts
with additional time
dimensions to
provide dual time
perspectives
Figure 7-8
Sales fact table
overloaded with
local and standard
time dimensions
Consequences
All dimensional overloading patterns require additional ETL processing and make
fact tables larger but the trade off is faster, simpler, more consistent BI queries.