MAPPING TEMPORAL DATA WAREHOUSE CONCEPTS

TO SAP BW COMPONENTS

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.

1 INTRODUCTION

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

systems.

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

zones.

We addressed Time dimension updates in

(Hezza

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).

2 THE SAP BW INFORMATION

MODEL

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

391

Hezzah A. and Min Tjoa A. (2005).

MAPPING TEMPORAL DATA WAREHOUSE CONCEPTS TO SAP BW COMPONENTS.

In Proceedings of the Seventh International Conference on Enterprise Information Systems, pages 391-397

DOI: 10.5220/0002510903910397

 SciTePress

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,

etc.

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).

3 TIME CHARACTERISTICS IN

SAP BW

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

characteristics.

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

(0FISCYEAR)

Calendar Year

(0CALYEAR)

Calendar Quarter

(0CALQUARTER)

Fiscal Period

(0FISCPER)

Calendar Month

(0CALMONTH)

Calendar Week

(0CALWEEK)

Calendar Day

(0CALDAY)

Figure 1: Hierarchy of SAP BW time characteristics

ICEIS 2005 - DATABASES AND INFORMATION SYSTEMS INTEGRATION

392

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

0CALMONTH2, 0CALQUART1, 0HALFYEAR1,

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 MAPPING TEMPORAL

CHARACTERISTICS TO SAP

BW COMPONENTS

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

logic.

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

runs.

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

MAPPING TEMPORAL DATA WAREHOUSE CONCEPTS

393

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

CET

(

+1h offset from UTC

)

Local date

3 A

r 04

Local time

13:00:00

Conversion

Time stamp (UTC)

3 A

r 04 12:00:00

Time zone of object

CET

(

+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

‘PM’.

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:

ICEIS 2005 - DATABASES AND INFORMATION SYSTEMS INTEGRATION

394

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

fall.

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

MAPPING TEMPORAL DATA WAREHOUSE CONCEPTS

395

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

occurred

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

SAP BW.

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

rules.

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).

ICEIS 2005 - DATABASES AND INFORMATION SYSTEMS INTEGRATION

396

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

query.

5 CONCLUSIONS

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.

REFERENCES

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,

EDBT’00

MAPPING TEMPORAL DATA WAREHOUSE CONCEPTS

397