Presentation . . . presen tation serv ers: con trol GUI LAN - - PDF document

▶
presentation
SMART_READER_LITE
LIVE PREVIEW

Presentation . . . presen tation serv ers: con trol GUI LAN - - PDF document

Sep 18, 2022 500 likes •827 views

Arc hitecture and Database In terfaces Donald Kossmann Outline 1. SAP's Clien t-serv er Arc hitecture 2. Ov erview of the SAP Sc hema 3. T ransaction Pro cessing 4. Benc hmarks and Results 2 SAP's Three-Tier

slide-1
SLIDE 1 Arc hitecture and Database In terfaces Donald Kossmann Outline 1. SAP's Clien t-serv er Arc hitecture 2. Ov erview
  • f
the SAP Sc hema 3. T ransaction Pro cessing 4. Benc hmarks and Results 2 SAP's Three-Tier Clien t-Serv er Arc hitecture

. . . Presentation LAN or WAN appl. server appl. server LAN RDBMS . . .

presen tation serv ers: con trol GUI application serv ers: carry
  • ut
ABAP programs and DynPros RDBMS: stores all the data (including ABAP programs, DynPros, data dic- tionary , etc.) 3
slide-2
SLIDE 2 Adv an tages
  • f
Three-Tier Arc hitectures 1. Scalabilit y . Add mac hines in middle tier to supp
  • rt
more users 2. P
  • rtabilit
y . It is p
  • ssible
to use dieren t platforms at all lev els. 3. In terop erabilit y and
  • p
enness. Middlew are serv es as platform to in tegrate and in teract with third-part y pro ducts. 4. Nice GUIs. Presen tation serv ers can in teract with Microsoft W
  • rd,
Excel, etc. 4 In tegrating WWW Services

RDBMS . . . local clients appl. server appl. server LAN LAN Web Server Internet Transaction Server Internet Internet clients

5
slide-3
SLIDE 3 SAP R/3 Congurations tin y: 1 user
  • all
three la y ers
  • n
  • ne
mac hine
  • ne
  • f
the SAP founder's golf club is run b y R/3
  • n
a laptop small: ab
  • ut
10 users
  • PCs
for presen tation
  • application
and database serv er
  • n
  • ne
(mid-range) mac hine
  • Ethernet
6 Serious SAP R/3 Congurations medium: ab
  • ut
100 users
  • PCs,
noteb
  • ks,
  • ther
w
  • rkstations
for presen tation
  • a
couple
  • f
mac hines for application serv ers;
  • ne
(fairly big) database serv er mac hine;
  • Ethernet
for lo cal PCs; big: more than 1000 users
  • PCs,
noteb
  • ks,
  • ther
w
  • rkstations
for presen tation;
  • sev
eral mac hines for application serv ers;
  • a
mainframe/m ulti-pro cessor mac hine for the database
  • FDDI
7
slide-4
SLIDE 4 Additional Gimmic ks
  • usually
, installations ha v e separate mac hines for tests
  • standb
y database serv er mac hine is recommended
  • ISDN,
D A TEX-P , and sp ecial links to tec hnical subsystems (e.g., sensors) are also v ery common 8 Supp
  • rted
Platforms Presen tation La y er
  • Windo
ws 3.1, Windo ws 95, Windo ws NT
  • Ja
v a
  • OSF/Motif
  • OS/2
  • Macin
tosh
  • in
the long run,
  • nly
Windo ws and Ja v a are going to b e supp
  • rted
Op erating Systems for Application Serv ers
  • AIX,
Digital Unix, HP-UX, SINIX, SOLARIS
  • Windo
ws NT
  • OS/100
(for IBM AS/400) 9
slide-5
SLIDE 5 Database Systems
  • DB2
Common Serv er, DB2 for AS/400, DB2 for OS/390
  • Informix
Online
  • Oracle
  • MS
SQL Serv er
  • (AD
ABAS D:
  • nly
supp
  • rt
for
  • ld
installations) Hardw are
  • Bull,
IBM, SNI, SUN, Digital, HP for UNIX platforms
  • man
y , man y for Windo ws NT
  • IBM
AS/400
  • IBM
S/390 Comm unication Proto cols
  • TCP/IP
  • LU
6.2 (for IBM mainframes) 10 Ov erview
  • f
the SAP Sc hema
  • R/3
has more than 10,000 pre-dened tables (V ersion 3.x) { tables for data suc h as customers,
  • rders,
etc. { tables for statistics (monitoring the system) { tables for authorization { ... { comprehensiv e, generic sc hema for an y kind
  • f
conceiv able business rather than greatest common denominator
  • fully
normalized, (almost) no redundancy { go
  • d
for OL TP { bad for OLAP (as w e will see)
  • users
can also dene their
  • wn
tables 11
slide-6
SLIDE 6 ( )
  • three
dieren t kinds
  • f
SAP tables transparen t: mapp ed 1:1 to RDBMS tables p
  • l:
mapp ed n:1 to RDBMS tables motiv ation: in the 80s, some RDBMS pro ducts limited the total n um b er
  • f
tables cluster: mapp ed n:1 to RDBMS tables so that related tuples
  • f
sev eral cluster tables are stored in
  • ne
ro w
  • f
the RDBMS table motiv ation: sometimes go
  • d
during transaction pro cessing T rend: mak e all tables transparen t 12 Examples 1. All c
  • mments
and descriptions need to b e stored in separate tables in
  • rder
to k eep information in dieren t languages: part(id , . . . , commen t) ) part(id , . . . ) commen t(partid, language , commen t) N.B.: Actually , SAP tables come with names suc h as K ONV, STXL, VBAP, VBEP, etc. Keys span sev eral attributes (including business unit, etc.) 13
slide-7
SLIDE 7 2. Generic w a y
  • f
dealing with pricing terms (customizable): lineitem(id , . . . , tax, discoun t) ) lineitem(id , . . . , pricing term id ) pricing terms(id, condition , amoun t)
  • tax
and discoun t are stored in t w
  • dieren
t tuples (additional pricing terms stored in additional tuples)
  • to
allo w quic k access, pricing term tuples that b elong to the same lineitem are clustered together (i.e., pricing terms is a cluster table) 14 Sc hema: Observ ations
  • SAP
databases tend to b e v ery large (due to genericit y)
  • Sc
hema is the heart
  • f
SAP , but still under constan t revision { a couple
  • f
thousand new tables with ev ery new ma jor release { a great deal
  • f
reorganization w
  • rk
with ev ery upgrade 15
slide-8
SLIDE 8 g SAP's T ransaction Concept
  • SAP
uses the term L
  • gic
al Unit
  • f
Work (LUW) for transaction.
  • Basically
, an SAP LUW has the same A CID prop erties as SQL and an y (SQL) database system: { an SAP LUW can span sev eral dialog steps { an SAP LUW is either executed completely
  • r
not at all (i.e., atomicit y) { ... { nested transactions are also p
  • ssible
16 Ov erview
  • f
Implemen tation
  • SAP
LUWs are NOT mapp ed 1:1 to database transactions
  • SAP
implemen ted its
  • wn
lo c king (cen tralized enqueue servic e )
  • basically
, SAP also implemen ted its
  • wn
TP monitor (message hand ler and queues in ev ery application serv er)
  • nline
transactions and batc h (o v ernigh t) queries p
  • ssible
  • (for
comparison: P eopleSoft uses third-part y TP monitors suc h as T uxedo) 17
slide-9
SLIDE 9 Pro cessing Dialog Steps 1. when a user logs in, a message handler nds the application serv er with the smallest load (load balancing) 2. this application serv er handles all
  • f
the requests
  • f
that user session 3. a user session consists
  • f
sev eral transactions, and ev ery transaction consists
  • f
sev eral dialog steps 4. ev ery application serv er has
  • ne
dispatc her pro cess and sev eral w
  • rk
pro cesses 5. the dispatc her queues requests un til a w
  • rk
pro cess is a v ailable 6. a w
  • rk
pro cess carries
  • ut
a dialog step; rolls in relev an t data and in terprets DynPro and ABAP programs 7. so, ev ery user session is handled b y a single application serv er, but ev ery dialog step is handled b y dieren t w
  • rk
pro cesses 8. exception: transactions that in v
  • lv
e large
  • b
jects ha v e exclusiv e w
  • rk
pro cesses to a v
  • id
cost
  • f
rolling data in and
  • ut
18 Application Serv er

Work Process Work Process . . . private buffers private buffers Dispatcher Shared Memory Buffers Queue

19
slide-10
SLIDE 10 g

D1 D2 D3 dialog steps P1 P2 P3 P4 posting steps

  • nline phase

posting phase lock requests lock release

  • log
records for up dates are generated as part
  • f
ev ery dialog step
  • log
records are propagated to RDBMS in p
  • sting
phase
  • lo
c ks are requested during
  • nline
phase and released after the p
  • sting
phase is complete (2-phase lo c king)
  • lik
e dialog steps, p
  • sting
steps are handled b y dieren t w
  • rk
pro cesses (p
  • ten
tially in parallel) 20 Wh y do esn't SAP directly use the RDBMS?
  • T
ypical RDBMSs do not allo w transactions that cross pro cess b
  • undaries.
(This is necessary in SAP b ecause the dialog steps
  • f
an LUW can b e handled b y dieren t w
  • rk
pro cesses)
  • ab
  • rts
are quite frequen t (e.g.,
  • ut
  • f
sto c k) and
  • nly
carried
  • ut
b efore p
  • sting;
as a result, no roll-bac k at the RDBMS, the b
  • ttlenec
k, is required for ab
  • rts
  • SAP
carries
  • ut
lo c king in the gran ularit y
  • f
\business
  • b
jects" whic h are dened in the ABAP dictionary 21
slide-11
SLIDE 11 g Ov erview
  • SAP's
application serv ers cac he ABAP programs, constrain ts, and
  • p
erational data in
  • rder
to reduce the load
  • f
the RDBMS.
  • more
than 90% cac he hits are not un usual for SAP applications
  • ABAP
programs and constrain ts are alw a ys cac hed.
  • Administrator
decides whic h data to cac he and in whic h w a y to cac he it: { data that is frequen tly up dated should not b e cac hed at all { h uge data that is lik ely to
  • d
the cac he should not b e cac hed either { default settings for pre-dened tables help 22 Cac hing Examples
  • REGION
table is a classic candidate for cac hing
  • LINEITEM
table is a classic candidate for NOT cac hing 23
slide-12
SLIDE 12 Application serv ers can cac he data three dieren t gran ularities (set b y default
  • r
system administrator for ev ery table): 1. c
  • mplete
c aching
  • f
a table
  • cac
he can b e used for an y query
  • n
that table
  • need
m uc h cac he space, high cost to propagate up dates 2. tuple-wise c aching
  • cac
he
  • nly
used for select single statemen ts
  • ne-grained:
i.e., go
  • d
cac he utilization, lo w cost in the presence
  • f
up dates 3. generic c aching (a c
  • mpr
  • mise)
  • cac
he all tuples
  • f
a table with the same v alue
  • f
a prex
  • f
the primary k ey
  • e.g.,
cac he all tuples that b elong to the same business unit 24 Cac he consistency in congurations with sev eral application serv ers
  • p
erio dic propagation
  • no
guaran tee for cac he coherency
  • ho
w ev er, in pr actic e not a problem b ecause
  • nly
data that is almost nev er up dated is cac hed
  • r
data for whic h inconsistencies don't matter that m uc h 25
slide-13
SLIDE 13
  • again,
SAP implemen ts its
  • wn
authorization mo del and do es not use the standard (SQL) mo del supp
  • rted
b y the RDBMS
  • users
m ust log in with their user-id and passw
  • rd
(iden tication)
  • ne-grained
and exible authorization concept { individual elds
  • f
tables { sp ecic transactions and/or rep
  • rts
{ views
  • bundling
  • f
authorizations { authorization
  • b
ject (set
  • f
related authorizations) { authorization prole (set
  • f
authorization
  • b
jects; roles) { group prole (set
  • f
authorization proles)
  • users
b elong to groups and inherit group authorizations 26 Securit y
  • activit
y logs
  • encryption
  • f
all messages exc hanged b et w een RDBMS and application serv ers
  • Kerb
eros and SecuDE for secure clien ts at the presen tation la y er
  • secure
transp
  • rt
system for batc h input and migration
  • f
databases
  • nly
the administrator has direct access to the database and le system; ev eryb
  • dy
else m ust use the in terfaces
  • f
the presen tation la y er
  • r
  • ther
external services in
  • rder
to w
  • rk
with the system 27
slide-14
SLIDE 14 y g
  • SAP
monitors the follo wing parameters { queue lengths in dispatc her { cac he hit ratio { database
  • p
erations (scans, sorts, joins) { ABAP
  • p
erations (sorts, etc.) { n um b er
  • f
commits and rollbac ks { CPU, disk, memory , net w
  • rk
utilization { resp
  • nse
time
  • f
dialog steps, w
  • rk
pro cesses, database calls
  • alerters
inform administrators if problems
  • ccur
  • p
erformance statistics are stored in the database
  • EarlyW
atc h service | (Big Brother is W atc hing Y
  • u)
28 SAP T ransaction Pro cessing Benc hmarks
  • dened
in 1993 (Release 1.1H)
  • purp
  • se
  • f
these b enc hmarks: { sizing
  • f
an SAP system { measure real SAP application
  • p
erations { k ey for database systems certication
  • sev
en dialog b enc hmarks (FI, MM, SD, PP , PS, WM, ?)
  • t
w
  • batc
h b enc hmarks (AA, HR)
  • most
p
  • pular
b enc hmark is SD b enc hmark
  • usually
run b y hardw are v endors (SAP b enets from their comp etition) 29
slide-15
SLIDE 15 Th e SD B enc h mar k (Sales and Distribution) Set Up (common to all b enc hmarks)
  • ne
presen tation serv er sim ulates users
  • f
sev eral business units
  • to
a v
  • id
lo c k con ten tion, maxim um 100 users p er business unit
  • cen
tral conguration:
  • ne
mac hine for application serv er and RDBMS
  • distributed
conguration: n mac hines for application serv ers, another mac hine for RDBMS 30 SD Benc hmark Script 1. create an
  • rder
with v e line items 2. create a deliv ery for this
  • rder
3. displa y the customer
  • rder
4. c hange the deliv ery and p
  • st
go
  • ds
issue 5. list fort y
  • rders
6. create an in v
  • ice
15 dialog steps; 4 p
  • sting
steps; 150 secs think time 31
slide-16
SLIDE 16 p ( ) SUN Bea v erton, CA, USA
  • n
Marc h 11, 1997: Num b er
  • f
b enc hmark users: 1,410 SD Av erage dialog resp
  • nse
time: 1.85 seconds Throughput: 7,133 SAPS (428,000 dialog steps/h) Equiv alen t to: 143,000 pro cessed
  • rder
line items p er hour Av erage DB request time: 0.397 secs (dialog), 0.397 secs (up date) CPU utilization: 94% Op erating System: Solaris 2.5.1 RDBMS: Informix Online Serv er 7.21 UC1 R/3 Release: 3.0E (with 3.0F k ernel) T
  • tal
disk space: 350 GB 1 Cen tral Serv er: 64-w a y UltraSparc I I CPU's 250 MHz, 14 GB main memory , 1 MB lev el 2 cac he; 15 dialog/up date instances, 1 message/enqueue instance Certication Num b er: 1997007 32 SD Benc hmark Results and T rends

100000 200000 300000 400000 500000 600000 1993 1994 1995 1996 1997 1998 Transactions per hour Year

har dwar e vendors have solve d many
  • f
SAP's p erformanc e pr
  • blems
c
  • nc
erning tr ansaction pr
  • c
essing. 33
slide-17
SLIDE 17
  • R.
Buc k-Emden and J. Galimo w: SAP R/3 System { A Clien t/Serv er T ec hnology . Addison-W esley , 1996.
  • J.
Dopp elhammer, T. H
  • ppler,
A. Kemp er, and D. Kossmann: Database P erformance in the Real W
  • rld
{ TPC-D and SAP R/3. A CM SIGMOD, 1997.
  • B.
Lob er and U. Marquard: R/3 System Standard Application Benc hmarks. http://www.sap-ag .de /p rod uct s/ tec hn
  • /i
nd ex. ht m
  • B.
Lob er: R/3 System Standard Application Benc hmarks { Published Results, V ersion 2.4. http://www.sap-ag .de /p rod uct s/ tec hn
  • /i
nd ex. ht m
  • SAP
A G: Online Do cumen tation. 1996-1998.
  • SAP
A G: SAP R/3 Soft w are Arc hitektur. 1994. 34 Queries and OLAP Applications
  • n
SAP R/3 Alfons Kemp er Outline
  • Query
in terfaces
  • f
SAP R/3
  • Decision
Supp
  • rt
Queries
  • n
SAP R/3
  • SAP
Data W arehouse Pro ducts 35
slide-18
SLIDE 18 / /
  • all
business data is en tered via OL TP-applications in to SAP R/3
  • SAP
has its
  • wn
predened sc hema
  • SAP
R/3 is used to ask queries, e.g.: { for nding particular information as part
  • f
OL TP pro cessing: what happ ene d to a p articular
  • r
der issue d by customer 'Smith' in Mar ch 1998 { for generating business rep
  • rts
for decision supp
  • rt:
how did
  • ur
new line
  • f
pr
  • ducts
sel l in dier ent r e gions
  • ver
the last six months
  • SAP
R/3 has man y predened business rep
  • rts
that can b e in v
  • k
ed \b y the clic k
  • f
the mouse"
  • SAP
R/3 also allo ws to \program" customized queries/rep
  • rts
36 Query In terfaces
  • f
SAP R/3 relational database system (bac k-end serv er) 6 ? lo cal buers database in terface
  • data
dictionary ABAP/4 in terpreter Nativ e SQL Op en SQL 6 ? application- data SAP-SQL 6 ? DB-data SQL queries SAP R/3 37
slide-19
SLIDE 19 Op en SQL
  • SQL-92
st yle syn tax (some limitations!)
  • built-in
  • p
erators, suc h as SOR T
  • materialize
temp
  • rary
results
  • cac
hing
  • f
query results
  • cursor
cac hing to
  • ptimize
rep eated database access
  • in
recen t v ersions SAP has signican tly impro v ed the database in terface
  • b
efore V ersion 3.0 it w as not p
  • ssible
to \push" a join to the database serv er; it had to b e implemen ted as nested SFW's within the ABAP-pro cessor (i.e., within the application serv er). 38 Op en SQL v ersus Nativ e SQL{con t'd Nativ e SQL
  • b
ypass ABAP/4 pro cessor (and cac hing)
  • b
ypass SAP data dictionary
  • therefore
not p
  • ssible
to access non-transparen t tables
  • adv
an tage:
  • ften
b etter p erformance { exploit (non-standard) features
  • f
the RDBMS { a v
  • id
certain problems
  • f
the ABAP pro cessor
  • disadv
an tage: not p
  • rtable
and p
  • ten
tially unsafe { cannot use dieren t RDBMSs { programmer needs exp ert kno wledge ) SAP recommends use
  • f
Op en SQL { all built-in business applications use Op en SQL 39
slide-20
SLIDE 20 /
  • use
  • f
the TPC-D Benc hmark
  • results
w ere rep
  • rted
in: Jo c hen Dopp elhammer, Thomas H
  • ppler,
Alfons Kemp er, Donald Kossmann: Database P erformance in the Real W
  • rld
  • TPC-D
and SAP R/3 (Exp erience P ap er). SIGMOD Conference 1997: 123-134
  • load
data in to SAP's predened tables
  • wrote
17 TPC-D queries in a) Nativ e SQL and b) Op en SQL
  • implemen
ted 2 up date functions using SAP R/3's batc h input facilit y
  • Sun
Sparc Station with 260MHz pro cessors, 256 MB main memory , 54GB disks, commercial RDBMS as bac k-end
  • for
comparison: w e implemen ted the \original" TPC-D b enc hmark directly
  • n
commercial RDBMS using the same hardw are and the same conguration 40 The TPC-D Database Sc hema

integer NAME char 10

CUSTOMER (C_)

CUSTKEY char 25 ADDRESS varchar 40 NATIONKEY integer PHONE char 15 ACCTBAL decimal MKTSEGMENT COMMENT varchar 117 integer integer NAME char 55 MFGR char 25 BRAND char 10 TYPE varchar 25 SIZE CONTAINER char 10 RETAILPRICE decimal COMMENT varchar 23 PARTKEY

PART (P_)

integer PARTKEY

PARTSUPP (PS_)

integer AVAILQTY integer SUPPLYCOST decimal COMMENT varchar 199 SUPPKEY SF*200k integer decimal

LINEITEM (L_)

ORDERKEY PARTKEY integer SUPPKEY integer LINENUMBER integer QUANTITY decimal EXTENDEDPRICE decimal DISCOUNT decimal TAX COMMENT RETURNFLAG char 1 LINESTATUS char 1 SHIPDATE date COMMITDATE date RECEIPTDATE date SHIPINSTRUCT char 25 SHIPMODE char 10 varchar 44 integer COMMENT

ORDER (O_)

ORDERKEY CUSTKEY integer ORDERSTATUS char 1 TOTALPRICE decimal ORDERDATE date ORDERPRIORITY char 15 CLERK char 15 SHIPPRIORITY integer varchar 79 integer NAME char 55 COMMENT char 152 REGIONKEY

REGION (R_)

5 integer NAME

NATION (N_)

25 NATIONKEY char 25 REGIONKEY integer COMMENT varchar 152 integer NAME

SUPPLIER (S_)

SUPPKEY char 25 ADDRESS varchar 40 NATIONKEY integer PHONE char 15 ACCTBAL decimal COMMENT varchar 101 SF*6000k SF*1500k SF*150k SF*10k SF*800k

41
slide-21
SLIDE 21
  • 8
tables in the TPC-D sc hema v ersus 17 tables in SAP R/3 SAP T able Description Orig. TPC-D T ab. T005 Coun try: general info NA TION T005T Coun try: Names NA TION T005U Regions REGION MARA P arts: general info P AR T MAKT P arts: description P AR T A004 P arts: terms P AR T K ONP T erms: p
  • sitions
P AR T LF A1 Supplier: general info SUPPLIER EINA P art-Supplier: general info P AR TSUPP EINE P art-Supplier: terms P AR TSUPP A USP prop erties P AR T, SUPP , P .-S. KNA1 Customer: general info CUSTOMER VBAK Order: general info ORDER VBAP Lineitem: p
  • sition
LINEITEM VBEP Lineitem: terms LINEITEM K ONV Pricing T erms LINEITEM STXL T ext
  • f
commen ts all 42 Size
  • f
Database in KB (SF = 0.2) Original SAP DB TPC-D DB (V ersion 2.2) Data 271,139 2,813,216 Indexes 102,822 841,008 Wh y is SAP database so big?
  • SAP
k eeps info whic h is not mo delled in the TPC-D b enc hmark { giv en default v alues in
  • ur
exp erimen ts
  • (strong)
partitioning
  • f
the database tables { space to store foreign k eys
  • in
SAP , k eys (e.g. Customer-No) are 16 b yte strings.
  • in
  • riginal
TPC-D b enc hmark, k eys are 4 b yte in tegers. 43
slide-22
SLIDE 22 Loading Time for the Benc hmark Data (SF = 0.2) Loading Time REGION | NA TION | SUPPLIER 18m P AR T 15h 56m P AR TSUPP 30h 24m CUSTOMER 7h 33m ORDER+LINEITEM 25d 19h 55m
  • in
all, it to
  • k
ab
  • ut
1 mon th!
  • Reasons:
{ SAP carries
  • ut
extensiv e consistency c hec ks for ev ery record individually { SAP R/3 inserts records in to the database
  • ne-at-a-time
rather than making use
  • f
the RDBMS bulk load facilities
  • it
app ears to b e v ery dicult (imp
  • ssible?)
to b ypass SAP R/3 and insert R/3-c
  • nsistent
data in to the RDBMS tables directly 44 TPC-D P
  • w
er T est Query RDBMS Nativ e SQL Op en SQL Up date (TPC-D-DB) (SAP DB) (SAP DB) Q1 6m 09s 58m 59s 56m 18s Q2 53s 3m 09s 34s Q3 4m 03s 9m 02s 11m 51s Q4 1m 45s 6m 18s 6m 38s Q5 6m 39s 14m 42s 37m 27s Q6 1m 20s 7m 28s 14m 06s Q7 9m 03s 23m 05s 29m 24s Q8 1m 54s 19m 04s 16m 37s Q9 8m 42s 31m 33s 1h 7m 14s Q10 5m 18s 33m 06s 57m 49s Q11 5s 4m 37s 2m 23s Q12 3m 15s 9m 48s 9m 36s Q13 8s 19s 25s Q14 6m 23s 10m 25s 21m 54s Q15 3m 25s 13m 51s 28m 31s Q16 13m 24s 3m 16s 3m 22s Q17 11s 1m 50s 2m 13s UF1 1m 40s 1h 46m 54s 1h 46m 54s UF2 1m 48s 11m 35s 11m 35s T
  • tal
(quer.) 1h 12m 37s 4h 10m 32s 6h 06m 22s T
  • tal
(all) 1h 16m 05s 6h 09m 01s 8h 04m 51s
  • more
details (all queries in Op en and Nativ e SQL): http://www.db.fmi .u ni- pa ssa u. de/ pr
  • je
ct s/S AP / 45
slide-23
SLIDE 23
  • three
(nativ e SQL) to v e (Op en SQL) times more exp ensiv e queries { ten times larger database (due to partitioning, large k eys, default v alues)
  • partitioning
  • f
the data leads to N
  • M
  • w
a y joins v ersus M
  • w
a y joins in
  • riginal
TPC-D b enc hmark { Example: Query 1 { TPC-D: single table scan { SAP: Lineitem
  • Lineitem
  • Lineitem
  • up
dates are v ery exp ensiv e b ecause
  • f
the consistency c hec ks; just as for initial loading 46 Wh y is Op en SQL ev en more exp ensiv e?
  • Limitations
  • f
Op en SQL prohibit \pushing do wn" certain exp ensiv e
  • p
erations, e.g., complex aggregation { increased comm unication costs b et w een database serv er and application serv er
  • p
  • r
query ev aluation plans { partly due to RDBMS { partly due to SAP R/3
  • cannot
exploit non-standard features
  • f
the RDBMS { for example proprietary string manipulation routines
  • SAP
generates parameterized queries rather than passing constan ts
  • f
selection predicates to the RDBMS
  • ptimizer
{ ma y result in p
  • r
  • ptimization
c hoices b y the
  • ptimizer
{ e.g., c ho
  • sing
an index scan instead
  • f
a table scan b ecause the selectivit y
  • f
a predicate is not kno wn due to param. 47
slide-24
SLIDE 24 Conclusions
  • Complex
queries for decision supp
  • rt
are to
  • exp
ensiv e in SAP R/3
  • The
highly partitioned sc hema is
  • ptimized
for OL TP applications, not for OLAP applications
  • (Extensiv
e) query pro cessing p enalizes the
  • p
erational OL TP system ) Data W arehouse is the w a y to go 48 SAP EIS (Executiv e Information System)
  • SAP's
rst attempt in data w arehousing
  • part
  • f
the regular R/3 system
  • uses
the same system (database serv er and application serv er) as the OL TP applications
  • therefore,
incresed load
  • n
the OL TP system
  • based
  • n
a rudimen tary star sc hema { a fact table { sev eral dimension tables
  • ho
w ev er, SAP EIS has limitations: e.g.,
  • nly
three attributes p er dimension table 49
slide-25
SLIDE 25
  • limitations
in query pro cessing {
  • nly
8
  • f
the 17 TPC-D queries could b e expressed in SAP EIS
  • the
p erformance
  • f
these eigh t queries impro v ed b y a factor
  • f
t w
  • as
compared to the Op en SQL rep
  • rts
  • high
loading time: ab
  • ut
50 hrs to load the TPC-D database with SF=0.2 from R/3 in to EIS { ev en though, EIS w as part
  • f
the same R/3 system
  • merely
extracting the TPC-D database from SAP R/3 in to ASCI I les tak es 6 hours 50 The Business Information W arehouse (BIW)
  • SAP's
new data w arehouse pro duct
  • rst
b eta tests; general a v ailabilit y starting in July
  • R
OLAP arc hitecture (Relational OLAP)
  • RDBMS
indep enden t, i.e., sev eral RDBMS pro ducts are supp
  • rted
as bac k-end serv er (dev elop ed
  • n
Oracle and MS SQL Serv er)
  • bundled
with the SAP R/3 system, V ersion 4.0
  • but
can also b e acquired separately as a stand-alone system
  • rst
SAP stand-alone comp
  • nen
t (outside
  • f
R/3)
  • part
  • f
SAP's Business F ramew
  • rk
; comp
  • netization
  • f
R/3
  • BIW
can handle data extraction from v arious
  • ther
systems, not
  • nly
from SAP R/3 and R/2 51
slide-26
SLIDE 26 The Business Information W arehouse's Role

Business Information Warehouse Server Admministrator Workbench Business Explorer Business Explorer Business Explorer Business Explorer R/3 OLTP (diff. versions) R/2 OLTP Applications Other OLTP Applications External Info. Sources Web

52 The BIW's Arc hitecture

BIW Server Repository Data Store Meta Data Business Explorer Report Catalog Browser Reporting and Analysis for Microsoft Excel Reporting and Analysis OLTP Reporting Production Data Extractor Non R/3 Production Data Extractor Meta Data Manager Data Manager InfoCubes Staging Engine BAPI R/3 OLTP Applications Non R/3 OLTP Applications OLAP Processor BAPI

53
slide-27
SLIDE 27
  • p
en Business Application Programming In terfaces (BAPIs) for { data loading: to accommo date the extraction
  • f
data from non-SAP systems { OLAP pro cessing: e.g., for third part y visualization to
  • ls
  • pre-congured
meta data rep
  • sitory:
InfoCub es catalog, rep
  • rt
catalog, information source catalog
  • man
y pre-dened InfoCub es for common business applications: e.g., mark et segmen t analyses, protabilit y analyses, sto c k in v en tory analyses, corp
  • rate
indicator systems
  • predened
extraction routines to
  • btain
this data from R/3 OL TP systems (with incremen tal up dates!) 54 The Staging
  • f
Information

(Application Link Enabling-Interface) Transfer Structure Transfer Structure Mapping and Transformation Rules Communication Structure Update Rules InfoCubes Business Information Warehouse ALE ALE Extract Source Structure Transfer Structure Extract Source Structure Transfer Structure

55
slide-28
SLIDE 28

Administrator’s Workbench Meta Data Scheduler Data Load Monitor Data Access Monitor Meta Data Maintenance Manager Staging Engine Statistics Data Manager

  • GUI-based
  • sophisticated
sc heduler 56 InfoCub es: Star Sc hema Sales Outlet Time Customer Agen t Pro duct
  • ne
normalized fact table
  • sev
eral dimension tables { p
  • ssibly
non-normalized to mo del hierarc hical c haracteristics { e.g., customers' cit y! coun t y! coun try! region 57
slide-29
SLIDE 29
  • (Handy)
Sales b y Brand/Y ear/Coun try

600 400 500 1400 1200 2300 3 3 5 8 5 2 7 3 4 3400 2 4 4 5 1 9 5 Aus CH all all Nokia Bosch Motorola Siemens 800 D Year Brand Country 1995 1996 1997 all

58 Relational Represen tation
  • f
an \InfoCub e"

C P T quantity sold revenue discount sales overhead stock value T Period Fiscal Year P Product-No Product-Group Brand Category Product-No Indicator ... Product Master Data C Customer-No Chain Office Head Office InfoCube: Profitability by Customer/Product Customer-No Name Name Location Industry-Key Customer Master Data Product-No Language Product-Description

59
slide-30
SLIDE 30
  • slice
and dice
  • drill
do wn/roll up

Product Group Product Group Customer Region Customer Region Product Group Customer Region

60 Business Analysis Library
  • aggregate
functions: sum, min, max, a v erage, . . .
  • comparison
functions: dierence, ratio, p ercen t,, share, correlation, . . .
  • sequence
functions: top/last n, cum ulativ e sum, tertiles, quartiles, ABC analysis, . . .
  • exception
conditions: absolute v alues, top/last n, top/last n%, trends, . . . { used to highligh t exceptions
  • nancial
functions: currency con v ersion, business p erio d con v ersion, . . . { v ery imp
  • rtan
t for in ternational en terprises 61
slide-31
SLIDE 31 P er f
  • rmance
  • no
b enc hmark results y et
  • dedicated
serv er { separate from OL TP serv er { can b e congured as a three-tier clien t serv er conguration (lik e SAP R/3)
  • InfoCub
e summary lev els: precomputed aggregations
  • p
ersisten t rep
  • rt
cac he
  • batc
h rep
  • rts
62 Literature
  • Jo
c hen Dopp elhammer, Thomas H
  • ppler,
Alfons Kemp er, Donald Kossmann: Database P erformance in the Real W
  • rld
  • TPC-D
and SAP R/3 (Exp erience P ap er). SIGMOD Conference 1997: 123-134 http://www.db.fmi .un i- pas sau .d e/p ro jec ts /SA P/
  • SAP-A
G: SAP Business Information W arehouse { Information Cen ter. http://www.sap-ag .de /p rod uc ts/ bi w/i nd ex. ht m
  • R
  • udiger
Kretsc hmer and W
  • lfgang
W eiss, SAP's R/3 Applications with ABAP/4. Syb ex, 1996, Alameda, CA, USA.
  • T
ransaction Pro cessing P erformance Council TPC, TPC Benc hmark D (Decision Supp
  • rt),
Standard Sp ecication 1.0, T ransaction Pro cessing P erformance Council (TPC), 1995, http://www.tpc.or g/ 63