- ITtestpapers.com
Download
Report
Transcript - ITtestpapers.com
BW Know-How
Call :
Performance
Tuning
dial-in phone numbers!
U.S. Toll-free: (877) 260-8900
International: (612) 338-1917
Passcode: “BW”
Performance Tuning in BW
OLTP
Performance Tuning
OLTP Systems
Application
Development and
performance tuning separated
Performance
Database
tuning by basis
experts
Application
Performance Tuning
BW
BW
Performance
Tuning as
holistic process over
application design and
database configuration
Database
Application
Performance Tuning
SAP AG 2002, BW Performance Tuning, Alex Peter, 2
Contents
Checklists
OLAP Performance Layers
Further Information
SAP AG 2002, BW Performance Tuning, Alex Peter, 3
Performance Guidelines
Checklists
OLAP Performance Layers
Further Information
SAP AG 2002, BW Performance Tuning, Alex Peter, 4
Checklist – Query and Web Performance – Overview
ST03 / Technical Content
Which component
contributes most?
OLAP
Database
Check
these
points
Tools
Frontend
1.
Data Model
1.
Data Model
1.
Network
2.
Query Definition
2.
2.
WAN and BEx
3.
Aggregates
Query Definition
(including OLAP
features)
3.
Client Hardware
4.
OLAP Cache
4.
VBA / Java
5.
Pre-Calculated Web
Templates
5.
Documents
6.
Formatting
7.
ODBO / 3rd party
6.
Compressing
7.
Indices
8.
DB Statistics
9.
DB and basis
(Buffer) Parameter
3.
Aggregates
4.
OLAP Cache
5.
Virtual Key Figures /
Characteristics
6.
Authorizations
SQL Trace (ST05)
RSRT, RSRTRACE
RSRV
SQL Trace (ST05)
IEMON
RSRT, RSRTRACE
ABAP Trace (SE30)
RSRT, RSRTRACE
SAP AG 2002, BW Performance Tuning, Alex Peter, 5
Checklist – Data Load Performance – Overview 1 –
Technical Content, Data Load Monitor
Which component
contributes most?
Extraction
Check
these
points
Tools
Transfer
Load Into PSA
1.
Customer Exits
1.
Resource Contraint
1.
I/O Contention
2.
Resource Utilization
2.
2.
PSA Partition Size
3.
Load Balancing
CPU / Memory
Bottleneck
4.
Data Package Size
3.
Network
5.
Indices on tables
4.
Application Buffer
Synchronization
6.
Flat File format
7.
Content vs. generic
extractor
Extractor Checker
(RSA3),
SM50
ABAP Trace (SE30),
SQL Trace (ST05)
OS Monitor (ST06)
SQL Trace (ST05)
OS Monitor (ST06)
DB Monitor (ST04)
SAP AG 2002, BW Performance Tuning, Alex Peter, 6
Checklist – Data Load Performance – Overview 2 –
Technical Content, Data Load Monitor
Which component
contributes most?
Transfer Rules
Load Into Data Targets
Update Rules
Master Data
Check
these
points
1.
2.
Transformation
Rules / ABAP
Coding
1.
Transformation
Library
Formulas
2.
Debugger within
Monitor
Tools
ABAP Trace
(SE30),
SQL Trace (ST05)
SAP AG 2002, BW Performance Tuning, Alex Peter, 7
ODS Objects
InfoCubes
Buffering
Number
Ranges
1.
Roll-up
2.
Change Run
Change
Run
3.
Compression
4.
Indices
5.
Load Master
Data before
Transaction
Data
6.
Buffering
Number
Ranges
SQL Trace (ST05)
1.
Parallel ODS
activation
2.
Unique Data
Records
3.
Flag BEx
Reporting
4.
Indices
Performance Guidelines
Checklists
OLAP Performance Layers
Further Information
SAP AG 2002, BW Performance Tuning, Alex Peter, 8
OLAP Performance Layers
Performance Layers
For specific Scenarios
Offline Analysis
Pre-Calculation
Performance
OLAP Cache
Aggregates
Reuse
InfoCubes
SAP AG 2002, BW Performance Tuning, Alex Peter, 9
Aggregates – 1 –
Aggregate Definition
Materialization
of aggregated subsets of InfoCube fact table data
Independent
structures where summary data is stored within separate,
transparent InfoCubes
Transparency:
Improved
Users do not notice if aggregate is hit or not
query performance by reducing the amount of data to be read from
DB
Aggregates can be created
Only
on top of basic InfoCubes
For
dimension characteristics
For
navigational attributes
On
hierarchy levels
Using
time-dependent navigational attributes (as of BW 3.x)
Using
hierarchy levels where the structure is time-dependent (as of BW 3.x)
Aggregates can improve query performance considerably, but keep in
mind that they also impact the load performance.
SAP AG 2002, BW Performance Tuning, Alex Peter, 10
Aggregates – 2 – Example
Example for flow with and without aggregates
Flow without aggregate
Selection
Database
Month
July
July
August
August
Material Revenue
Hammer
10
Nail
20
Hammer
10
Nail
20
InfoCube
Month
July
July
August
August
Material Revenue
Hammer
10
Nail
20
Hammer
10
Nail
20
Number of records read
on the database
Month Revenue
July
30
August
30
Month Revenue
July
30
August
30
Aggregate
Flow with aggregate
SAP AG 2002, BW Performance Tuning, Alex Peter, 11
OLAP Engine
Month Revenue
July
30
August
30
Records
transferred to BW
instance after
being summarized
on the database
OLAP Cache – 1 –
Caching in BW 2.x
One
Local Cache for each session
No
cache access across sessions
No
possibility to set size of cache
Caching in BW 3.x OLAP Cache
Additional
global cache which is accessible from all
sessions
OLAP
Cache is part of application buffer (Imp/Exp SHM) and
can also be stored in a DB table or a file
The
Cache stores query results and navigation statuses as
highly compressed cluster data
Query
Cache is used for equal queries or subsets of cached
queries
Benefits:
OLAP Cache reduces workload on database and
application server
SAP AG 2002, BW Performance Tuning, Alex Peter, 12
OLAP Cache – 2 –
OLAP Cache
OLAP
(In
Cache size can be adjusted
3.x there is also an adjustable size for the local cache)
Cache
can be switched off entirely, for InfoCubes and for
individual queries
Local
Cache is used, when OLAP Cache is inactive.
Specific
instance profile parameters:
Buffer small by
default!
rsdb/esm/buffersize_kb
4096 (kB) Size of exp/imp SHM buffer
rsdb/esm/max_objects
2000
rsdb/esm/large_object_size
8192 (byte)Estimation for the size of the largest object
rsdb/esm/mutex_n
0
SAP AG 2002, BW Performance Tuning, Alex Peter, 13
Max. number of objects in the buffer
Number of mutexes in Exp/Imp SHM buffer
Pre-Calculated Web Templates – 1 –
Pre-Calculated Web Templates
Pre-calculation is a set of techniques where you can distribute the
workload of running the report to off-peak hours, and have the report
result set ready for very fast access to the data
Data Pre-Calculation or HTML Pre-Calculation
HTML
Pre-Calculation can be used for Offline Analysis
Benefits
Fast
response time
System
workload shifted to off-peak hours
Re-use
data that goes to many queries
Reports
also available offline
SAP AG 2002, BW Performance Tuning, Alex Peter, 14
Pre-Calculated Web Templates – 2 –
Scenario
Supports “Newspaper” scenario
Quick
access to many reports
Reports
are requested by many users
Static
reporting, usually little navigation
requirements
Active
Reporting Authorisations
Restrictions
No
invalidation when new data is loaded
Restricted
SAP AG 2002, BW Performance Tuning, Alex Peter, 15
navigation: only filtering possibles
Pre-Calculated Web Templates – 3 – Access Modes
DATA MODE SAP GUI
Web Browser
http://myserver:myport/sap/bw/BEx?cmd=ldoc&template_id=myTemplate&data_mode=new
NEW
STORED
BEx Web Service
HYBRID
BW Server
STATIC
STATIC_HYBRID
Web Framework
OLAP
Engine
Doc Web Service
WebDAV
Handler
Reporting Agent
Log
Content
Management
Framework
Web Application Server
InfoProvider
SAP AG 2002, BW Performance Tuning, Alex Peter, 16
RA Data Storage
CMF Repository
Pre-Calculated Web Templates – 4 – Access Modes
DATA MODESAP GUI
Web Browser
http://myserver:myport/sap/bw/BEx?cmd=ldoc&template_id=myTemplate&data_mode=stored
NEW
STORED
BEx Web Service
HYBRID
BW Server
STATIC
STATIC_HYBRID
Doc Web Service
Reporting Agent
WebDAV
Handler
Web Framework
OLAP
Engine
Content
Management
Framework
Log
Web Application Server
InfoProvider
SAP AG 2002, BW Performance Tuning, Alex Peter, 17
RA Data Storage
CMF Repository
Pre-Calculated Web Templates – 5 – Access Modes
DATA MODESAP GUI
Web Browser
http://myserver:myport/sap/bw/BEx?cmd=ldoc&template_id=myTemplate&data_mode=static
http://myserver:myport/sap/bw/doc/tmpl/fldtmpl/“cmfpath/report.htm“
NEW
STORED
BEx Web Service
HYBRID
BW Server
STATIC
STATIC_HYBRID
Doc Web Service
Reporting Agent
WebDAV
Handler
Web Framework
OLAP
Engine
Log
Content
Management
Framework
RA Data Storage
CMF Repository
Web Application Server
InfoProvider
SAP AG 2002, BW Performance Tuning, Alex Peter, 18
Performance Guidelines
Checklists
OLAP Performance Layers
Further Information
SAP AG 2002, BW Performance Tuning, Alex Peter, 19
Further Information
Find Additional Information
BW Performance Page in SAP Service Marketplace
Alias
BW Performance
SEM-BPS Performance Page in SAP Service Marketplace
Alias
SEM Media Library Help To Use SEM SEM-BPS Performance
KHNC
on SEM Performance in 2002
SAP AG 2002, BW Performance Tuning, Alex Peter, 20