![](bg2.png)
order to provide a consistent view of the data over
time, a view that can be used by decision support
systems. One of the major dimensions in every
multidimensional data warehouse is the time
dimension. The time dimension contains descriptive
temporal information, and its attributes are used as
the source of most of the temporal constraints in data
warehouse queries (Kimball, 1996). However, the
design of the time dimension is not always
straightforward as it strongly depends on the type of
business and the requirements of the enterprise.
The aim of this paper is to introduce a
specification of the time dimension in enterprise data
warehouse systems, which is consistently applicable
for handling the analysis of global enterprise data.
The problems arising in multinational corporate
groups when combining data with a temporal
dimension are enormously cost-intensive. Even the
minor problem of DST for one of the world-wide
leading energy companies could cause data
warehouse costs of millions of dollars, as it has been
investigated by one of the authors.
This paper deals with the subject matter related
to representing time in the data warehouse. It
discusses the design of the time dimension and
introduces design techniques for its implementation.
It presents a practical approach, which also models
relevant real world business issues such as holidays,
seasons, daylight saving time and fiscal periods by
extending the time dimension with new attributes
and flags. It uses the time dimension together with
timestamps to resolve major granularity issues.
Finally, it addresses issues related to having
different time zones and demonstrates how the use
of local and universal time can resolve these issues.
2 RELATED WORKS
The functions needed to implement a data
warehouse architecture including different types of
data are described in (Devlin, 1997) and (Inmon,
1996). It addresses the use of timestamps to store
periodic and historical business data, but doesn’t
consider the multidimensional approach widely used
today. This is more discussed in (Kimball, 1996)
with case studies of data warehouses for different
types of businesses, almost all using a daily-based
time dimension, unfortunately with no focus on the
issues related to its implementation.
Adding history to the temporal database
application is investigated in (Snodgrass, 2000) with
focus on issues related to valid and transaction time,
intervals and periods and state tables for valid and
transaction time. It also presents some
implementation considerations for the temporal
database logical and physical design and
demonstrates application development issues using
SQL.
A conceptual multidimensional data model,
which facilitates even sophisticated constructs based
on multidimensional data units or dimension
members, is introduced in (Nguyen, 2000). This
model is able to represent and capture natural
hierarchical relationships among dimension
members within a dimension as well as the
relationships between dimension members and
measure values and is modeled using UML.
Dimension updates are formally discussed in
(Vaisman, 2001).
(Bruckner, 2001) presents an approach for
modeling conceptual time consistency problems and
introduces a model that deals with timely delays.
However, this model doesn’t address issues related
to the time dimension as much as data consistency
and updating issues. Changes of dimension data are
discussed in (Eder, 2002), which presents an
approach to represent temporal behavior of master
data within existing, non-temporal data warehouses.
In (Ravat, 2000) a data warehouse class concept
is introduced, which is based on the concepts of
temporal filter and archive filter. It defines mapping
functions to specify derived, calculated, and specific
properties, and organize the inheritance hierarchy of
the warehouse classes, allowing extracting only
relevant data. In (Yang, 2000) Yang and Widom
study incremental maintenance of temporal views
using a temporal query language equivalent to
TSQL2. Although (Ravat, 2000) does not organize
data multidimensionally, it provides a more flexible
temporal model than (Yang, 2000) because the
purging values are not deleted, but they are archived.
The use of multiple time dimensions is
mentioned in (Kimball, 1999), which introduces the
concept of a data webhouse. It uses a clickstream
data mart to store all web activities for later analysis
of user behavior. This is also discussed briefly in
(Pedersen, 2001) with focus on the influence of the
web on data warehousing, but also the design of
clickstream fact tables and dimension tables.
Other temporal issues like fiscal periods and
granularity are briefly discussed in (Kimball, 2002)
and (Kimball, 1997) with more focus on using the
time dimension to resolve this issues, but design
issues are not investigated in detail.
The aim of this paper is to give a framework for
modeling the time dimension in data warehouses for
enterprise wide (global) information systems with
focus on its applicability for practical issues, such as
daylight saving time (DST) or problems related to
time zones, holidays, and fiscal periods. This paper
DESIGN AND REPRESENTATION OF THE TIME DIMENSION IN ENTERPRISE DATA WAREHOUSES - A
BUSINESS RELATED PRACTICAL APPROACH
417