Ahmed Hezzah, A Min Tjoa
Institute of Software Technology, Vienna University of Technology, Favoritenstr. 9-11/188, 1040 Vienna, Austria
Keywords: Data Warehouse, Time Dimensio
n, Temporal Databases, Business Warehouse, SAP BW
Abstract: SAP Business Information Warehouse (BW) today is a suitable and viable option for enterprise data
warehousing and one of the few data warehouse products that offer an integrated user interface for
administering and monitoring data. In previous works we introduced design and modeling techniques for
representing time and temporal information in enterprise data warehouses and discussed generic problems
linked to the design and implementation of the Time dimension, which have to be considered for global
business processes, such as handling different time zones and representing holidays and daylight saving
time (DST). This paper investigates supporting the global exchange of time-dependent business information
by mapping those temporal data warehouse concepts to SAP BW components, such as InfoCubes and
master data tables.
The amount of data flowing into and through
organizations is growing exponentially. This data
comes from many different sources and locations,
and the ability to put this data to work is critical to
an organization’s success. To exploit the large
amount of data moving through their organizations,
many companies have developed data warehouse
SAP Business Information Warehouse (BW)
day is a suitable and viable option for enterprise
data warehousing. It is equipped with preconfigured
information models and reports as well as automated
data extraction and loading methods to provide a
common view of enterprise data, which facilitates
analysis and interpretation of information. It also
enables Online Analytical Processing (OLAP) to
format the information of large amounts of operative
and historical data.
In (Hezzah, 2004a) we introduced design and
odeling techniques for representing temporal
information in the data warehouse and solved
common problems related to the implementation of
the Time dimension in business data warehouses,
such as representing holidays, seasons and fiscal
periods, considering the observation of daylight
saving time (DST) and handling different time
We addressed Time dimension updates in
h, 2004b) and introduced an approach to
handle structural and instance updates to the Time
dimension, and showed how they differ from
updates to other slowly changing dimensions. We
investigated the information model of SAP BW in
(Hezzah, 2004c) and discussed the role of temporal
characteristics as a time reference to business events.
This paper investigates how the global exchange
of t
ime-dependent information can be supported by
using SAP BW as an enterprise data warehouse. It
provides an overview on the information model of
SAP BW with focus on the storage architectural
layer. It addresses the time characteristics of SAP
BW and introduces a mapping of temporal concepts
introduced in previous works to SAP BW
components. This includes handling different time
zones and local time conversion, as well as modeling
relevant real-world business issues such as holidays,
seasons and daylight saving time (DST).
Before we actually start addressing the options,
methods and tools available in SAP BW to
implement a solution for modeling temporal
information, let’s first focus on the architectural
Hezzah A. and Min Tjoa A. (2005).
In Proceedings of the Seventh International Conference on Enterprise Information Systems, pages 391-397
DOI: 10.5220/0002510903910397
layers of the SAP BW implementation consisting of
the layered architecture of SAP BW accompanied by
two administrative architectural components.
SAP BW is built on a relational OLAP (ROLAP)
model. The main structures used for
multidimensional analysis in SAP BW are called
InfoCubes. The InfoCube Manager generates the
InfoCube infrastructure consisting of a fact table and
a set of dimension tables, as well as the update and
retrieval routines according to the definition stored
in the meta data repository.
Master data is stored in master data attribute
tables, language-dependent text tables, and hierarchy
tables. Master data attributes and texts can be
defined as time dependent, and hierarchies can be
defined as version or time dependent. Generally
speaking, master data is data that remains unchanged
over a long period of time, e.g. customer, product,
The characteristics determine the granularity at
which the key figures are kept in the InfoCube. The
key figures, also known as facts, provide the values
that are reported on in a query, e.g. quantity, amount
or number of items. These values must have units to
give them meaning. Time characteristics are
characteristics such as date, month, fiscal year, etc.
Slowly changing dimensions (e.g. customer or
product) are stored in SAP BW master data tables.
The master data table can have a time-dependent and
a time-independent part.
Attributes are InfoObjects that exist already, and
that are assigned logically to the new characteristic.
It is possible to decide for each attribute
individually, whether it is time-dependent or not. If
only one attribute is time-dependent, a master data
table is created. However, there can still be attributes
for this characteristic that are not time-dependent.
The Time dimension can be customized by
assigning time characteristics. It could be given
using the characteristics ‘day’ (in the form
YYYYMMDD), ‘week’ (in the form YYYY.WW),
‘month’ (in the form YYYY.MM), ‘year’ (in the
form YYYY) and ‘period’ (in the form YYYY.PPP).
Time characteristics are used in the obligatory Time
dimension of InfoCubes to express the time
reference to business events. As time characteristics
in SAP BW are internally treated in a special way, it
is not possible to create client-specific time
The time reference characteristic for an
InfoCube, when there are several time characteristics
in the InfoCube, is always the “most refined”, since
all other times in the InfoCube are derived from this.
An InfoCube might contain warehouse key figures
that should be evaluated for the calendar month and
calendar year. In this case, the calendar month is the
most refined common time reference characteristic.
There is a difference between complete and
incomplete time characteristics: The complete time
characteristics are the SAP BW time characteristics
calendar day (0CALDAY), calendar week
(0CALWEEK), calendar month (0CALMONTH),
calendar quarter (0CALQUARTER), calendar year
(0CALYEAR), fiscal year (0FISCYEAR) and fiscal
period (0FISCPER). They are clearly assigned to a
point in time.
Fiscal Year
Calendar Year
Calendar Quarter
Fiscal Period
Calendar Month
Calendar Week
Calendar Day
Figure 1: Hierarchy of SAP BW time characteristics
Figure 2: Time zones in SAP BW
Only these time characteristics can be used as
time reference characteristics, since it must be
possible to derive time characteristics automatically
from the most detailed time characteristic with the
non-cumulative folder.
Incomplete time characteristics, such as
0WEEKDAY1 or 0FISCPER3 can be used in a non-
cumulative InfoCube but cannot be a time reference
characteristic, since they are not assigned to a
specific point in time. Figure 1 gives an overview of
the hierarchy of SAP BW time characteristics.
4.1 Consideration of Time Zones
Local dates and times can only be compared with
each other and exchanged if they are in the same
time zone. Many global companies, however, work
in different time zones, but still need to exchange
their data across regional boundaries.
Processes which cover more than one time zone
primarily affect logistics functions such as
availability checks, production planning, delivery
scheduling, statistics and service provision, but they
also affect financial accounting in areas such as
inter-company transactions, etc.
In (Hezzah, 2004a) we introduced an approach to
modeling time zones in the data warehouse, which
uses multiple Time dimensions for local and
universal time. We showed that splitting the time-of-
day from the date gives us the capability to navigate
sales facts by date and time of both local and
universal time and saves us implementing the time
calculation based on time zones into the application
We also extended the Time dimension by
additional attributes and flags to solve the issue of
daylight saving time DST in different time zones.
Then in (Hezzah, 2004b) we solved the issue of
Time dimension updates, and showed how changes
to the DST rules can affect the structure as well as
single instances of the Time dimension.
SAP BW uses a similar approach, which
supports the conversion of local dates and times via
the time zone function. This function supports using
dates and times that are comparable and
exchangeable in applications that are implemented
worldwide. The only difference is that it integrates
DST rules into the time zone configuration and not
directly into the Time dimension, and the universal
time is not additionally stored in a separate
dimension, but is calculated based on the time zone
via a conversion function (see 4.1.1).
All available time zones are maintained in a
central table, and are assigned rules for DST
observation as shown in Figure 2. Rules for time
zones, such as the difference from Universal Time
Coordinated (UTC), are maintained in a separate
table and also assigned to the time zones.
4.1.1 Time Conversion in SAP BW
Generally, users think and act in terms of their local
time, and they also expect to use their local time in
business transactions. When the SAP BW system is
used for global transactions that span time zones,
business partners and systems will have different
local times. These differences in local times can lead
to problems such as late postings and missed batch
For example, a company with its headquarters
and database server in Paris requires that all billing
documents be posted by 4:00 p.m. Users in the
company’s office in London might expect that to
mean 4:00 p.m. in London, which is 1 hour behind
Paris time. Thus any users in London posting billing
documents after 3:00 p.m. would be posting their
documents too late.For business processes spanning
time zones, inaccuracies of up to 24 hours could
occur. To compare the local times of users in
different time zones, the SAP BW system represents
time differently externally and internally. The
external representation of the time corresponds to a
context-dependent local time. For example, in
Germany, the time is represented in Central
European Time (CET) and in New York in Eastern
Standard Time (EST).
Internally, the system normalizes the internal
system time to UTC, which serves as a reference
time. By normalizing date and time internally, the
time zone function eliminates problems that can
arise from users working in different local time
zones. For some transactions, the system normalizes
dates and times by storing a time zone and a time
stamp, which consists of the time and date of an
event converted from local time to UTC.
Figure 3 shows how the Conversion function of
SAP BW uses time zone information to transform
the local time into universal time. Here, the
requested delivery date of 3 Apr 2004 13:00:00 CET
for a ship-to address in Germany receives the time
stamp of 3 Apr 2004 12:00:00 UTC.
To determine the time zone of an object in SAP
BW, the system uses a series of decision rules. By
determining an object’s time zone, the system can
display a time stamp of the object in any local time.
To ensure consistent determination of time zones
and efficient performance, this process is performed
by a central function depending on the location of
the object.
The SAP BW system uses a 24-hour clock with
the local date and local time of the object (here the
ship-to address) from the user interface with the
object’s time zone to calculate the time stamp. To
display the object’s local date and time, SAP BW
uses the object’s time zone, which is stored with the
time stamp, and goes through the process
backwards. For application programs, a time stamp
accurate to the second is generally sufficient.
From the SAP BW system’s user time
Time zone of object
+1h offset from UTC
Local date
3 A
r 04
Local time
Time stamp (UTC)
3 A
r 04 12:00:00
Time zone of object
+1h offset from UTC
Figure 3: Time conversion function of SAP BW
The time stamp’s external representation
corresponds to the date and time representation. The
same user options exist for displaying the time
stamp as for the date and time:
DD.MM.YYYY hh:mm:ss
(03.04.2004 14:36:25)
MM/DD/YYYY hh:mm:ss
(04/03/2004 14:36:25)
MM-DD-YYYY hh:mm:ss (04-03-
2004 14:36:25)
YYYY.MM-DD hh:mm:ss
(2004.04-03 14:36:25)
YYYY/MM/DD hh:mm:ss
(2004/04/03 14:36:25)
The total output length is 19 characters. The
system supports displaying times without seconds,
but it does not support displaying times as ‘AM’ or
Internally, the system combines the data types
for date and time to create the 14-character time
stamp (8 characters for the date and 6 for the time).
Combining date and time allows the system to sort
time stamps correctly based on date (year-month-
day) or time (hour-minute-second). The allowed
range of values for the time stamp is
’01.01.0001 00:00:00’ to ’31.12.9999
23:59:59’. To avoid confusion with a.m. and
p.m. time designations, the system always uses a 24-
hour clock, and the system’s initial value for the
time stamp is zero or 00:00:00
, which
corresponds to midnight instead of 24:00:00.
The following example describes dates and times
on inter-company documents between two
companies located in different time zones. The local
date is different for the two companies:
Company code A: 2000, Location: Los Angeles,
Date: 15.04.04, Time: 19:36:03
Company code B: 5200, Location: Melbourne, Date:
16.04.04, Time: 10:36:03
System Date: 15.04.04
The document is associated with a single day,
and therefore, the document date, posting date and
entry date have the same value for both companies,
although they may differ from each other. The
determination of the posting date depends on the
type of transaction. The system records this
transaction for both companies with the date of the
system at the time of issue. Once the document has
been received in the receiving company, the time
zone function will propose a posting date based on
the local date and time zone of the user who entered
the document.
However, considering dates alone is not
sufficient to ensure exact time calculations. For
time-critical processes, dates with times replace
dates without times. A date standing alone, could
easily result in a one day inaccuracy (for example,
depending on the time of day, 3 April in Melbourne
may still be 2 April in Los Angeles). For a date
without a time, an inaccuracy related to time zones
can be as long as 48 hours in some extreme cases.
For time calculations, an accurate duration (for
example, hours and minutes instead of days) must be
used. Otherwise, chain calculations could be
inaccurate by several days.
Time zone
Time zone rule DST rule
Variable DST rule Fixed DST rule
Figure 4: Structure for DST rules
4.1.2 Daylight Saving Time
Some time zones observe daylight saving time
(DST) and use a “DST rule” for calculation
purposes. For these time zones, clocks are normally
set forward one hour to make better use of the longer
daylight hours in the late spring, summer and early
SAP BW uses a structure for DST observation,
which is slightly different from the one we
introduced in (Hezzah, 2004a) and (Hezzah, 2004b).
This structure integrates the rules for DST into the
time zone rules, which makes maintenance and
updating easier, but otherwise doesn’t have any
comparative functional advantage over the approach
we previously presented. However, for global
companies using data in different time zones, the
calculation of DST offsets is this way integrated into
the application logic and doesn’t need to be
considered on database level.
SAP BW introduces rules to maintain DST start
and end dates as well as the time shifts caused by
DST. These rules result in the following structure for
a time zone in the system (Figure 4):
DST rules (Figure 5) define the offset of DST
relative to the time zone’s standard time (for Europe
and USA +1 hour). It does not define the start and
end dates of DST. These rules are assigned to the
different time zones as already shown in Figure 2.
Variable DST rules (Figure 6) define how the
system calculates the start and end dates of DST.
These rules can always be changed, so there is no
Figure 5: DST rules
Figure 6: Variable DST rule
need to maintain DST start and end dates for every
year. For cases in which DST is not defined by
variable rules, fixed DST rules define the start and
end dates for a specific year.
Rather than distinguishing between two separate
time zones (one for winter and one for summer),
only one time zone indicator is used in SAP BW
which includes the DST rule when applicable. The
geographical assignment of DST rules and time zone
rules can be performed at country, region, or even
postal code level.
The switch backwards from DST to “winter
time” can cause problems because the clocks are set
back by one hour, which means that an hour is
repeated. For applications that use time stamps, this
can cause the following problems:
Time stamps from different real times can
have the same value
The time stamps do not necessarily reflect
the sequence in which system events really
In (Hezzah 2004a) we solved this problem by
introducing the 23-hour and 25-hour day, which
simply deletes one hour from all days on which time
is switched to DST, and inserts an additional hour on
all days on which the time is switched backwards.
Applications that use time stamps based on UTC are
not affected by this problem.
Since not all time stamps in the SAP BW system
are based on UTC, SAP has until recently
recommended shutting down the system during this
time to avoid the problem described above. The new
solution to this problem is the DST Safe Kernel,
which makes time during the “repeated hour” run at
half the usual speed. This means that the system can
rely on time stamps in the correct sequence without
duplicates, even if it is not using UTC, which solves
many issues related to the system availability of
SAP applications.
4.2 Holidays in SAP BW
In (Hezzah, 2004a) we introduced among other
things a practical approach, which models relevant
real-world business issues, such as holidays,
seasons, and fiscal periods, by extending the Time
dimension with new attributes and flags. In order to
consider holidays in different countries or in
different time zones we used multiple holiday flags
(holiday_flag_1 …… holiday_flag_n),
one for each country we needed to consider.
Integrating these attributes into the Time dimension
effectively reduces query execution time and
provides more functionality than using conventional
RDBMS tables, for instance, navigating data by
holidays and non-holidays. Here we investigate how
this issue is handled in the current implementation of
While SAP BW integrates seasons and fiscal
periods into the Time dimension, it still stores
holiday data in a separate table called public
holidays. This table is used by two other tables,
public holiday calendar and factory calendar, to
define holiday rules. The public holiday and factory
calendar is a central module in the SAP BW system.
It is used in many areas, such as logistics and human
resources. The calendar system consists of the
following components:
Public holidays: Contains the definition of public
holidays, calculations rules for date, religious
denominations, etc. (Figure 7). It consists of the
following attributes: public holiday type, date or
calculation rule, public holiday text (short or long),
and (if required) sort criterion, religious
denomination or public holiday class. If other public
holidays are needed, it is possible to add them by
maintaining the public holiday definition and
copying them to new or existing public holiday
Public holiday calendar: Contains any
composition of public holiday rules. Here it is
possible to assign any public holiday required to a
public holiday rule, which has the following
attributes: calendar ID, calendar description, period
of validity (From year, To year).
Figure 7: Public holidays
Factory calendar: Contains a definition of
workdays including special regulations, under the
assignment of a particular public holiday calendar.
The following attributes are maintained: factory
calendar ID, factory calendar description, period of
validity (From year, To year), start no. factory date
incremented for each workday, default value is 0).
The main drawback of this structure is that it
doesn’t support navigating data efficiently by
holidays or non-holidays. Also separating the
holiday definition from the Time dimension
increases query execution time and decreases overall
performance. Besides, it will be too complex if we
want to look at data, not just on holidays, but also on
different seasons, fiscal periods or weekdays. These
attributes are stored in different tables and must be
joined with the fact table.
However, using the public holidays and factory
calendar automatically eliminates irrelevant holidays
since only holidays assigned to a holiday rule are
considered in the executed query. This way, not all
entries in the public holidays table need to be
examined by the query. Moreover, for global
organizations, which are the main target group of
SAP BW, it is a big advantage being able to store all
holidays of all countries and regions in a single
database table and assign holiday rules to time zones
to include only a subset in any
This paper has addressed representing temporal
information by using SAP BW as an enterprise data
warehouse. It investigated the information model of
SAP with focus on the storage architectural layer
and gave an overview on the time characteristics
provided by SAP BW. To improve the global
exchange of time-dependent business information,
we introduced a mapping of temporal concepts
presented in previous works to SAP BW
components like InfoCubes and master data tables,
and showed how common business-related temporal
issues, such as handling different time zones,
representing holidays, fiscal periods and daylight
saving time (DST) can be modeled using functions
of SAP BW.
Egger, N., (2004), SAP BW Professional, SAP Press
Hezzah, A., Tjoa, A. M., (2004a), Design and
Representation of the Time Dimension In Enterprise
Data Warehouses - A Business Related Practical
Approach, In Proc. of ICEIS’04
Hezzah, A., Tjoa, A. M., (2004b), Temporal
Multidimensional Modeling with OLAP for Business
Applications, In Proc. of BIS’04
Hezzah, A., (2004c), Modeling Temporal Characteristics
with SAP Business Information Warehouse as an
Enterprise Data Warehouse - A Business Performance
Enhancing Practical Approach, Accepted for
Publication at CISTM’04
Kimball, R., (1996), The Data Warehouse Toolkit, John
Wiley & Sons, Inc.
McDonald, K., Wilmsmeier, A., Dixon, D. C., Inmon, W.
H., (2002), Mastering the SAP Business Information
Warehouse, John Wiley & Sons
Nguyen, T., Tjoa, A.M., Wagner, R., (2000), An Object
Oriented Multidimensional Data Model for OLAP, In
Proc. of 1st Int. Conf. on Web-Age Information
Management (WAIM 2000)
Prosser, A., Ossimitz, M. L., (2001), Data Warehouse
Management Using SAP BW, UTB Stuttgart
Ravat, F., Teste, O., (2000), A Temporal Object-Oriented
Data Warehouse Model, DEXA'00
Wijsen, J., Ng, R.T., (1999), Temporal Dependencies
Generalized for Spatial and Other Dimensions, Proc.
Spatio-Temporal Database Management
Yang, J., Widom, J., (2000), Temporal View Self-
Maintenance in a Warehousing Environment,