1

1
The top 20 tricks for
obtaining faster SAP
NetWeaver BI response time
Dr. Bjarne Berg
MyITgroup Ltd.
A VIP consulting company
© 2008 Wellesley Information Services. All rights reserved.
What We’ll Cover …
• Introduction
• Performance Issues & Tips




MultiProviders and Partitioning
Aggregates
Query Design & Caching
Hardware & Servers
• Designing for Performance

InfoCubes and DSOs
• BI- Accelerator
 Sizing and Implementation

Management and Costs
• EarlyWatch Reports
• Wrap-up
3
Introduction…
 In this session we will cover the top 20 must-do technical
performance tricks to help you optimize SAP NetWeaver BI
reporting for your end users.
 We will look at performance modeling of InfoCubes, how
to improve memory utilization by caching and how to use
diagnostics to analyze performance issues.
 We will also explore best practices on how to develop and
manage aggregates and MultiProviders, and see what the
BI- Accelerator (BIA) can do for your organization.
 Finally, we will look at how to analyze EarlyWatch reports
from Solution Manager 4.0 so they become actionable.
4
Capacity and Scalability Is the Top Concern for Your CxO
• Don’t under size your global BI system
 Spend adequate funding on hardware, memory, processing power and disk space
A survey of 353 top C-level officers in large companies, reported
that the top BI concern was the scalability of their solutions.
Overall System Performance and capacity
7.9
Server & Desktop processing capacity
7.88
Determining ROI
7.65
Security
7.62
Data Integration
7.51
Too many Business Intelligence tools in use
7.31
Danger of distributing outdated data
7.21
Difficult for end users to learn or use
Source: Intel, SAP & Business Week
"Seizing the BI Opportunity" 2006.
7.03
7
7.25
7.5
7.75
8
5
What We’ll Cover …
• Introduction
• Performance Issues & Tips




MultiProviders and Partitioning
Aggregates
Query Design & Caching
Hardware & Servers
• Designing for Performance

InfoCubes and DSOs
BI- Accelerator
 Sizing and Implementation

Management and Costs
• EarlyWatch Reports
• Wrap-up
6
Tip 1: MultiProviders and Hints
Problem: To reduce data volume in each InfoCube,
data is partitioned by Time period.
2002
2003
2004
2005 2006
2007
2008
A query now have to search in all InfoProviders to find
the data (i.e. billing docs from 2007). This is very slow.
Solution: We can add “hints” to guide the query execution. In the
RRKMULTIPROVHINT table, you can specify one or several
characteristics for each MultiProvider which are then used to
partition the MultiProvider into BasicCubes.
If a query has restrictions on this characteristic, the OLAP processor is already
checked to see which part cubes can return data for the query. The data
manager can then completely ignore the remaining cubes.
An entry in RRKMULTIPROVHINT only makes sense if a few attributes of this
characteristic (that is, only a few data slices) are affected in the majority of, or
the most important, queries (SAP Notes: 911939. See also: 954889 and 1156681).
7
Tip 2: The Secret about MultiProviders & Parallel Processing
• To avoid an overflow of the memory, parallel processing is
cancelled as soon as the collected result contains 30,000 rows
or more and there is at least one incomplete sub process


The MultiProvider query is then restarted automatically and processed
sequentially
What appears to be parallel processing corresponds to sequential
processing plus the preceding phase of parallel processing up to the
termination
• Generally, it’s recommended that you keep the number of
InfoProviders of a MultiProvider to no more than 10. However,
even at 4-5 large InfoProviders you may experience performance
degradation.
8
MultiProviders and Parallel Processing (cont.)
• Consider deactivating parallel processing for those queries that
are MultiProvider queries and have large result sets (and ‘hints’
cannot be used).

With SAP BW 3.0B SP14 (SAP BW 3.1 SP8 and later versions, you can change
the default value of 30,000 rows — refer to SAP Notes 629541, 622841, 607164,
and 630500.
• A larger number of base InfoProviders is likely to result in a
scenario where there are many more base InfoProviders than
available dialog processes, resulting in limited parallel
processing and many pipelined sub-queries
You can also change the number of dialogs (increase the use of parallel processing)
in RSADMIN by changing the settings for QUERY_MAX_WP_DIAG.
9
What We’ll Cover …
• Introduction
• Performance Issues & Tips




MultiProviders and Partitioning
Aggregates
Query Design & Caching
Hardware & Servers
• Designing for Performance

InfoCubes and DSOs
• BI- Accelerator
 Sizing and Implementation

Management and Costs
• EarlyWatch Reports
• Wrap-up
10
Aggregates
• Aggregates are much less used by the SAP installation base than
training and common sense should dictate.
• The interface to build the summary tables (aggregates) are intuitive
and easy to master, but few are taking real advantage of them.
• Even among those that are using aggregates, many have poorly
defined solutions & seldom monitor the usage, thereby limiting the
benefits of this simple technology.
To avoid poor definition and usage, aggregates should
be developed after the system has been in production
for a while and real user statistics are captured.
11
Tip 3: Building aggregates is easy – Propose from statistics
This example shows how to
build aggregates by using
system statistics to generate
proposals
Note: To make this work, the BW
statistics must be captured.
•
•
Select the run time of queries
to be analyzed (e.g., 20 sec)
Select time period to
be analyzed

Only those queries executed in
this time period will be reviewed
to create the proposal
12
Correct Aggregates Are Easy to Build – Propose from Query
We can also create proposals
from the Query user statistics.
To make this work, a
representative number of
queries must be executed to
gather the statistics to optimize
from.
We can also create
proposals for aggregates
based on individual queries
that are performing poorly.
13
Tip 4: Reduce the number of overlapping Proposals
We reduce the overlapping proposals
by optimizing them.
This may reduce the proposals from 99
to less than a dozen
High valuation and high usage is what we are looking for. This indicates high reduction
of records in aggregate and high benefits to users.
.
When using 3rd party query tools and ODBC to query directly into the
DSO, you are bypassing the OLAP Processor. Therefore, you cannot
accurately performance tune the system using aggregates (statistics),
nor will the 3rd party tool benefit from aggregates.
14
Activate the aggregate
The process of turning 'on' the
aggregates is simple
1. Click on Jobs to
see how the
program is
progressing
Fill aggregate with summary data
What We’ll Cover …
• Introduction
• Performance Issues & Tips




MultiProviders and Partitioning
Aggregates
Query Design & Caching
Hardware & Servers
• Designing for Performance

InfoCubes and DSOs
BI- Accelerator
 Sizing and Implementation

Management and Costs
• EarlyWatch Reports
• Wrap-up
17
Tip 5: Use the Right Read Mode for Queries
Select the right read mode. Three query read modes in
BW determine the amount of data to be fetched from a
database:
1.
Read all data (all data is read from a database and stored in user
memory space)
2.
Read data during navigation (data is read from a database only
on demand during navigation)
3.
Read data during navigation and when expanding the hierarchy
Reading data during navigation minimizes the impact on
the application server resources because only data that
the user requires will be retrieved.
Source: Catherine Roze, MyITgroup
18
Tip 6: Query read mode for large hierarchies
NEW
For queries involving large hierarchies with many nodes, it would
be wise to select Read data during navigation and when
expanding the hierarchy option to avoid reading data for the
hierarchy nodes that are not expanded.
Reserve the Read all data mode for special queries—for instance,
when a majority of the users need a given query to slice and dice
against all dimensions, or when the data is needed for data
mining. This mode places heavy demand on database & memory
resources and might impact other SAP BW processes and tasks.
Source: Catherine Roze, MyITgroup
A query read mode can be defined either on an individual query
basis or as a default for new queries using the query monitor
(transaction RSRT).
Run Program ANALYZE_RSZ_TABLES to check and fix
inconsistencies within query definition, please see note:
https://service.sap.com/sap/support/notes/792779
19
Tip 7: Condition & Exceptions
Minimize conditions-and-exceptions reporting. Conditions &
exceptions are usually processed by the SAP application
server. This generates additional data transfer between
database and application servers.
If conditions and exceptions have to be used, the amount of data to be
processed should be minimized with filters. When multiple drill-downs
are required, separate the drill-down steps by using free characteristics
rather than rows and columns.
This strategy results in a smaller initial result set, and therefore faster
query processing and data transport as compared to a query where all
characteristics are in rows.
This strategy does not reduce the query result set. It just separates
the drill-down steps. In addition to accelerating query processing, it
provides the user more manageable portions of data.
Source: Catherine Roze, MyITgroup
20
Some Performance settings for Query Execution
This decides how many records are read
during navigation.
Examine the
request status
when reading
the InfoProvider
New in 7.0 BI:
OLAP Engine can
read deltas into the
cache. Does not
invalidate existing
query cache.
Turn off/on parallel
processing
When will the
query program be
regenerated based
on database
statistics
Displays the level of
statistics collected.
21
Tip 8: Filters
Leverage filters as much as possible. Using filters contributes to
reducing the number of database reads and the size of the result set,
thereby significantly improving query runtimes.
Filters are especially valuable when associated with “big
dimensions” where there is a large number of characteristics such as
customers and document numbers.
If large reports have to be produced, leverage the BEx
Broadcaster to generate batch reports and pre-deliver
them each morning to their email, PDF or printer.
22
Tip 8: Use RSRT Transaction to examine slow queries
P1 of 3
23
Look for patterns and see the performance details
P2 of 3
In this real case, aggregates
was needed for those cubes
flagged…
24
Real Example: This system has issues with the Oracle DB
P3 of 3
Work with the basis team
to research the settings
and the Oracle issues.
Focus on SAP notes and
the index issue.
The RSRT and RSRV
codes are a gold mine for
debugging and analyzing
slow queries.
25
Look at the query details for each slow query
Notice the yellow flag for the 6 base
cubes in the MultiProvider and the
yellow flag for the 14 free chars.
(Note: no hints were used in this MultiProvider,
which led to very poor performance).
You can also trace the front-end data
transfers and OLAP performance by using
RSTT in SAP 7.0 BI (RSRTRACE in BW 3.5)
26
Tip 9: Use the BEx Broadcaster to Pre-Fill the Cache
Distribution Types
You can increase query speed by broadcasting
the query result of commonly used queries to
the cache.
Users do not need to execute the query from
the database. Instead the result is already in
the system memory (much faster).
27
Tip 10: Debugging Queries - RSRT
Here you can execute the query
and see each breakpoint, thereby
debugging the query and see
where the execution is slow.
Worth a try: Try running slow
queries in debug mode with
parallel processing deactivated
to see if they run faster..
28
Tip 11: Upgrade to AS-Java service pack 14 asap.
NEW
1. In service pack 14 we find several performance improvements
including:

Better Java execution and performance

Increased OLAP cache abilities (Enhanced Cluster table -BLOB)
2. In 7.0 BI at all service packs upto number 14, it is also impossible
to populate the OLAP cache by broadcasting query views. If you
use earlier service packs, you may be forced to create many
different queries to provide this performance.
3. Use quicksizer and OSS note 927530 for BI JAVA sizing.
The implementation of service pack 14 is highly
recommended by SAP for these performance reasons.
When implemented the Java execution will also improve.
29
Cockpit
Expense Query for Detailed program
Financial dashboard expense (actual vs. target)
Financial dashboard [expense]
Financial dashboard [non-earnings]
Expense Query for Detailed org objective
Financial dashboard [workforce costs]
Expense Query for Detailed
Capital Query for Detailed work type
Financial performance
Workforce financials
Expense query for detailed
Balance Query for Detailed program
Balance Query for Detailed cost element
Balance Query for Detailed MWC
Non-earnings Query for Detailed org objective
Financial dashboard (other balance)
Balance query for organization detailed
Balance Query for Detailed work type
Non-earnings Query for Detailed cost element
Capital Query for Detailed cost element
Labor query for detailed org
Standard costs variance detailed report
Financial dashboard (non-earnings)
Non-earnings Query for Detailed program
Balance Query for Detailed org objective
Capital Query for Detailed MWC
Non-earnings query for organization detailed
Non-earnings Query for Detailed work type
Financial dashboard [workforce cost]
Capital Query for Detailed org objective
Financial dashboard [other balance sheet]
Labor query for detailed cost element
Expense Query for Detailed cost element
PCC Expense Query for detailed
Headcount detail fin-DB
Capital - Actual Vs. Target
Financial dashboard capital trend
Non-earnings Query for Detailed MWC
Average
#of
Queries
1
7
2
2
1
6
1
1
9
9
1
1
1
1
1
7
1
1
1
1
1
1
7
1
1
1
1
1
2
1
2
1
1
1
1
7
2
1
Baseline SP 14 Improve
4/18/08 (4/21/08)
ment
145
9
94%
150
18
88%
70
12
83%
42
13
69%
31
10
68%
50
17
66%
36
14
61%
22
9
59%
43
21
51%
29
16
45%
16
9
44%
14
8
43%
14
8
43%
14
8
43%
14
8
43%
29
17
41%
13
8
38%
13
8
38%
13
8
38%
14
9
36%
14
9
36%
20
13
35%
30
20
33%
12
8
33%
13
9
31%
14
10
29%
12
9
25%
12
9
25%
23
18
22%
15
12
20%
21
17
19%
12
10
17%
13
11
15%
14
12
14%
15
13
13%
24
22
8%
13
12
8%
13
13
0%
27.95
12.03
39%
A Real Example
This company saw a
39% decrease in Query
execution time after
implementing SP-14.
They had 38 cockpits
and 82 queries that
improved substantially
without any further
changes..
30
NEW
Tip 12: Restrictive Key Figures & Line Item Dimensions
1. When Restrictive Key Figures (RKF) are included in a query, conditioning is done for
each of them during query execution. This is very time consuming and a high number
of RKFs can seriously hurt query performance
Recommendation: Reduce RKFs in the query to as few as possible. Also, define
calculated & RKFs on the Infoprovider level instead of locally within the query. Why?:
 Good: Formulas within an Infoprovider are returned at runtime and held in cache.
 Bad: Local formulas and selections are calculated with each navigation step.
2. Line item dimensions are basically fields that are transaction oriented and therefore,
once flagged as a ‘line item dimension’, is actually stored in the fact table. This
results in faster query access (no table join).
Explore the use line item dimensions for fields
that are frequently conditioned in queries.
31
Tip 13: Reducing the Query processing time
NEW
Problem: Calculated Key Figures (CKF) are computed
during run-time, and a many CKFs can slow down the
query performance.
Solution: Many of the CKF can be done during data loads & physically
stored in the InfoProvider. This reduces the number of computations and
the query can use simple table reads instead. Do not use total rows when
not required (this require additional processing on the OLAP side).
Problem: Sorting the data in reports with large result sets can be time
consuming.
Solution: Reducing the number of sorts in the default view can improve
the report execution & provide the users with data faster.
PS! Reducing the text in query will
also speed up the processing some.
32
Tip 14: Make your web templates Smaller & Faster
NEW
Web templates in SAP BI can become
really large. Since they contain both
scripts and Cascading Stylesheets (CSS),
the code can become large..
To reduce the CSS, you can try several
compression tools that may help you limit
the overall size of your web templates.
There are no lack of free tools available,
and the quality varies. Therefore you must
remember to test, test and test….
CSSTidy
PS! Web Applications run significantly faster
compared to BEx Analyzer reports in WAN
environments
Compression tools for CSS and Java scripts can reduce the overall web
template size. If you have thousands of users, this can be a ‘life saver’’
33
What We’ll Cover …
• Introduction
• Performance Issues & Tips




MultiProviders and Partitioning
Aggregates
Query Design & Caching
Hardware & Servers
• Designing for Performance

InfoCubes and DSOs
BI- Accelerator
 Sizing and Implementation

Management and Costs EarlyWatch Reports
Wrap-up
34
Tip 15: Is the Memory Cache Is Set Too Low?
Cache has a system default of 100 MB for local and 200 MB for global
cache. This may be too low for a system that can be optimized via
broadcaster.
Review the settings with the
Basis team and look at the
available hardware.
Use the transaction code
RSCUSTV14 in SAP NetWeaver
BI to increase the cache. Focus
particularly on the global cache.
The Cache is not used when a query contains a virtual key
figure or virtual characteristics, or when the query is
accessing a transactional DSO, or a virtual InfoProvider
35
Tip 15: Monitor and adjust Cache Size
To monitor the usage of the cache, use transaction code RSRCACHE
and also periodically review the analysis of load distribution using
ST03N – Expert Mode
The size of OLAP Cache is physically limited by the amount
of memory set in system parameter rsdb/esm/buffersize_kb.
The settings are available in RSPFPAR and RZ11.
Source: V. Rudnytskiy, 2008
36
Tip 16: The Right OLAP Cache Persistence Settings
CACHE OLAP Persistence settings
Note
Default
Optional
When
What
Flatfile
Change the logical file
BW_OLAP_CACHE when
installing the system (not
valid name)
Cluster table
RSR_CACHE_DBS_IX
Medium and small result sets RSR_CACHE_DB_IX
Binary Large Objects
Optional (blob)
Best for large result sets
SP 14
t-code
Blob/Cluster
Enhanced (new in
SAP 7.0 BI)
FILE
RSR_CACHE_DBS_BL
RSR_CACHE_DB_BL
No central cache directory or
lock concept (enqueue). The Set
mode is not available by
RSR_CACHE_ACTIVATE_NEW
default.
RSADMIN VALUE=x
Source: SAP AG 2008.
37
Monitor Memory Usage – Do you need more?
Roll memory was never maxed out in
the period 12/23/07 through 1/27/08
Paging memory was never maxed out
in the period 12/23/07 through 1/27/08
Extended memory was never maxed out in
the period 12/23/07 through 1/27/08
Only 3GB of 9 GB of Heap memory was ever
used in the period 12/23/07 through 1/27/08
38
What We’ll Cover …
• Introduction
• Performance Issues & Tips




MultiProviders and Partitioning
Aggregates
Query Design & Caching
Hardware & Servers
• Designing for Performance

InfoCubes and DSOs
BI- Accelerator
 Sizing and Implementation

Management and Costs
• EarlyWatch Reports
• Wrap-up
39
Tip 17: Avoid Outdated Indexes and Database statistics
Database statistics are used by the optimizer to route queries. Outdated
statistics leads to performance degradation. Outdated indexes can lead
to very poor search performance in all queries where conditioning is
used (i.e. mandatory prompts).
Name
Vendor history closed
AR customer
FIAR line items
FIAR Payment history
FIAR: Transaction data
Multicube AR&billing
Billing cube custom for AR trade
Sales contract cube - anticipated billing
Service orders - ZSLM
Performance cube
Headcount and personnel actionas
Cycle count
MM LIO interface infocube
Material aging
Lead time cube
Tech-Nm
XFIAP_C10
XFIAR_C10
0FIAR_C03
0FIAR_C05
0FIAR_C02
XSDARBIL
XSDBILITM
XSDCN_C10
ZCSCBZSLM
ZCSCBPER
ZHRPA_C02
XMMWM_C10
XLIO_C01
ZMMCBMAAG
ZMMLTCUBE
Object
Infocube
Infocube
Infocube
Infocube
Infocube
Infocube
Infocube
Infocube
Infocube
Infocube
Infocube
Infocube
Infocube
Infocube
Infocube
Indexes
DB stats
n/a
n/a
% used to
create stats
10%
10%
10%
10%
10%
n/a
10%
10%
10%
10%
10%
10%
10%
10%
10%
For high volume Infocubes, or cubes that have a high number of users, the
percentage used to build the DB stats can be increased from the default 10%
to 20%. This may yield more accurate query routing and better query
performance (consider this especially for cubes with ‘old data’ partitioned)
Tip 18: Avoid replicating the transaction system in SAP BI
It is tempting to load cross-reference tables and do lookups inside SAP BI instead of
extending extractors. This creates DSOs that cannot be queried efficiently without
many table joins. In this example, ¼ of all DSOs contains less than 9 fields, & six
have less than 4.
Programs that can help you monitor
the system design:
1. SAP_ANALYZE_ALL_INFOCUBES
2. ANALYZE_RSZ_TABLES
3. SAP_INFOCUBE_DESIGNS
As much logic as possible should be moved to the extraction,
and needed data fields should be denormalized and stored in
logically organized ODSs and Infocubes.
InfoCube Design & Indexes
When you flag a dimension as “high cardinality” SAP BI
will use a b-tree index instead of a bit-map index.
This can be substantially slower if the high cardinality
does not exist in the data in general (star-joins cannot be
used with b-trees).
Info Cube
CBBL_CB02
CBPD_CB06
CBPR_CB11
CBPR_CB18
CBSV_CB01
CBSV_CB02
Line Item
dims
0
0
0
0
0
0
DIM 1 DIM 3 DIM 6 DIM 8
H
H
H
H
H
H
Validate the high-cardinality of the data and reset the flag if
needed – this will give a better index type and performance
42
What We’ll Cover …
• Introduction
• Performance Issues & Tips




MultiProviders and Partitioning
Aggregates
Query Design & Caching
Hardware & Servers
• Designing for Performance

InfoCubes and DSOs
BI- Accelerator
 Sizing and Implementation

Management and Costs
• EarlyWatch Reports
• Wrap-up
43
TIP 19: Use BI Accelerator ASAP
SAP
BW
Any
tool
The SAP BI
Accelerator makes
query response time
50-10,000 faster.
You use process
chains to maintain
the HPA engine after
each data load
HP, Sun and IBM have standard solutions ranging
from $32K to $250K+ that can be installed and
tested in as little as 2-4 weeks (+ SAP license fees)
Breaking news: 32 Gb Blades are
now certified by SAP (July 2008) 44
How does SAP BIA Work?
Currently, the BIA performs aggregation and data selection
for the query, all other processing is done by the OLAP
analytical engine. (this means that 99% of the previous
recommendations in this session still holds true)…
SAP BIA is not used when the result set exceeds 3 million records
(max. default). When the result set is less, the data is sent as one
large data package to the application server (need fast network).
In the next SAP NetWeaver release the BIA will handle more of the
analytics processing such as “top-5 products sales” which is
currently done in the OLAP analytical engine.
You get BIA sizing estimates by running the
SAP program available in SAP Note: 917803
45
Performance Benchmarks for BIA
BIA’s strength resides in
its near-linear scalability.
Performance is measured
in terms of:
1.
2.
3.
4.
BIA index creation time
Multi-user throughput per hr.
Average report response time
Average number of records
touched by each report.
BIA Currently reads data from
InfoCubes. DSOs & InfoObjects
are still read from
base/physical tables (even
when the InfoObject is indexed
as part of master data).
46
A Real BI-A Client Example from 2008
Environment
Production
Production
Production
Production
Production
Production
Production
Production
QA
QA
QA
QA
QA
QA
QA
QA
Development
Development
Development
Development
Development
Development
Development
Development
Area
Blade servers
Memory
Processors
Processor speed
Network cards
External storage
File system
Chassis
Blade servers
Memory
Processors
Processor speed
Network cards
External storage
File system
Chassis
Blade servers
Memory
Processors
Processor speed
Network cards
External storage
File system
Chassis
Recommended size
14 Blades
2x8 GB (2x4) DDR2 total 16 GB
2 x Quad Core Intel Xeon Processor
3.00 GHz+
2 x Gigabit Cisco cards
Dedicated disks (500 GB+)
General Parallel file system (GPFS)
14 blades capacity
14 Blades
2x8 GB (2x4) DDR2 total 16 GB
2 x Quad Core Intel Xeon Processor
3.00 GHz+
2 x Gigabit Cisco cards
Dedicated disks (500 GB+)
General Parallel file system (GPFS)
14 blades capacity
4 Blades
2x8 GB (2x4) DDR2 total 16 GB
2 x Quad Core Intel Xeon Processor
3.00 GHz+
2 x Gigabit Cisco cards
Dedicated disks (300 GB+)
General Parallel file system (GPFS)
14 blades capacity
IBM example*
BladeCenter HS21 -8853G6U
39M5797
2 x Quad Core Intel Xeon Processor
3.00 GHz
32R1760
DS-4800
GPFS
H-series (rack-mount/9U) 88524XU
BladeCenter HS21 -8853G6U
39M5797
2 x Quad Core Intel Xeon Processor
3.00 GHz
32R1760
DS-4800
GPFS
H-series (rack-mount/9U) 88524XU
BladeCenter HS21 -8853G6U
39M5797
2 x Quad Core Intel Xeon Processor
3.00 GHz
32R1760
DS-4800
GPFS
H-series (rack-mount/9U) 88524XU
The BIA should be sized for critical applications. Most companies use
BIA only for Production, while others have a complete landscape
47
BIA is becoming mainstream
Some of SAP reference clients
BIA is no longer
something exotic.
Nike
Many of the large BI
systems have already
implemented BIA and
many more projects are
under way in Europe
and in the Americas.
Once you exceed a few hundred critical users and/or 3-4 Tb of
data you should seriously consider SAP BIA
48
What We’ll Cover …
• Introduction
• Performance Issues & Tips




MultiProviders and Partitioning
Aggregates
Query Design & Caching
Hardware & Servers
• Designing for Performance

InfoCubes and DSOs
BI- Accelerator
 Sizing and Implementation
Management and Costs
EarlyWatch Reports

Wrap-up
49
Tip 20: SAP Solutions Manager - EarlyWatch Reports Are Great!
• EarlyWatch reports provide a
simple way to confirm how your
system is running and to catch
problems
A
“goldmine” for system
recommendations
• Run them periodically & read the
details
• This is a real EarlyWatch report
from a mid-sized company that
has been running SAP BW for the
last four years
On a large global project, system issues
can be hard to pin-down without access
to EarlyWatch reports. The monitoring
reports allows you to tune the system
before the user community gets access
and complaints arise.
50
EarlyWatch Performance Info
1 Performance Indicators
The following table shows the relevant performance indicators in various system areas.
Area
System Performance
Hardware Capacity
Database Space Management
Query Performance
Indicators
Active Users
Max. CPU Utilization on DB Server
Max. CPU Utilization on Appl. Server
DB Size
Last Month DB Growth
Avg. Total Runtime of the BW Queries
Avg. Database Runtime of the BW Queries
Value
18
74 %
74 %
355.52 GB
118.63- GB
11.5 s
8.0 s
Trend
down
steady
steady
steady
steady
down
steady
In a 24-hour operational systems
dueandtototaltime-zones, you will have
The performance of your system was analyzed with respect to the average response times
workload. We did not detect any major problems that could affect the performance of your system.
less time to react and fix issues.
1 Performance Overview
Therefore, early detection of
system issues are critical to the
success of a global project.
The following table shows the average response times for various task types:
Task type
DIALOG +
RFC
UPDATE
UPDATE2
BATCH
HTTP
Dialog
Steps
195240
Avg. Resp.
Time in ms
3253.3
Avg. CPU
Time in ms
728.7
Avg. Wait
Time in ms
1.8
Avg. Load
Time in ms
2.5
5
48
59288
257762
984.2
133.2
11599.3
693.5
28.2
17.1
2091.2
183.7
26.0
0.7
0.6
4.4
15.2
3.3
8.5
2.2
Avg. DB
Avg. GUI
Time in ms Time in ms
1110.9
6.3
585.4
80.8
5772.6
405.0
1.1 Current Workload
The following table lists the number of current users (measured from our workload analysis) in your system.
Users
Measured in System
Low Activity
98
Medium Activity
11
High Activity
7
Total Users
116
51
EarlyWatch Reports – Finds Oracle fixes
In this real example, we can the EarlyWatch report identified that the
system was several Oracle notes are behind that needed to be
applied to optimize DB performance.
Before this was done, this system took 24 to 26 minutes to execute
some queries.
SAP Note
number
841728
871096
871735
850306
1021454
952388
Description
Oracle 10.2.0: Composite note for problems and workarounds
Oracle Database 10g: Patch sets/Patches for 10.2.0
Current Patchset for Oracle 10.2.0
Oracle Critical Patch Update Program
Oracle Segment Shrinking may cause LOB corruption.
Kernel <= 6.40:UNIX error due to 9i Client software
52
EarlyWatch Reports – Finds Backup Problems
0.1 Backup Frequency
When we checked the backup log files, we detected that your backup strategy does not follow the SAP backup
recommendations.
In the time period from 09.01.2008 to 05.02.2008 , we noticed the following problems:
- There was no successful backup on Friday 01.02.2008
- There was no successful backup on Thursday 31.01.2008
- There was no successful backup on Wednesday 30.01.2008
- There was no successful backup on Tuesday 29.01.2008
- There was no successful backup on Monday 28.01.2008
There are 5 working days without successful backup this week.
- There was no successful backup on Friday 25.01.2008
- There was no successful backup on Thursday 24.01.2008
- There was no successful backup on Wednesday 23.01.2008
- There was no successful backup on Tuesday 22.01.2008
- There was no successful backup on Monday 21.01.2008
There are 5 working days without successful backup this week.
- There was no successful backup on Friday 18.01.2008
- There was no successful backup on Thursday 17.01.2008
- There was no successful backup on Wednesday 16.01.2008
- There was no successful backup on Tuesday 15.01.2008
- There was no successful backup on Monday 14.01.2008
There are 5 working days without successful backup this week.
In this real example,
the EarlyWatch
report identified that
there were no valid
backups for almost
one month.
- There was no successful backup on Friday 11.01.2008
- There was no successful backup on Thursday 10.01.2008
- There was no successful backup on Wednesday 09.01.2008
There are 3 working days without successful backup this week.
There is no successful backup at all for this period.
53
What We’ll Cover …
• Introduction
• Performance Issues & Tips




MultiProviders and Partitioning
Aggregates
Query Design & Caching
Hardware & Servers
• Designing for Performance

InfoCubes and DSOs
BI- Accelerator
 Sizing and Implementation
Management and Costs
EarlyWatch Reports

Wrap-up
54
7 Key Points to Take Home
• Use best practices for query design before you start massive hardware
performance tuning efforts.
• Plan for growth – what is the plan when you have 200,500, 1000+ users?
• Start with aggregates (poor man’s BIA), thereafter go with caching.
• Monitor the system usage- do you need more app servers, memory, HW?
• Check database statistics and indexes and keep them up to date.
• If you are building an Enterprise Data Warehouse, plan and budget for a
BIA installation.
• EarlyWatch reports are a tool to live (and ‘die’) by. Use the report before
you have performance issues.
55
Resources
Dr. Bjarne Berg's web page -- 75+ presentations, tutorials & articles
http://csc-studentweb.lrc.edu/swp/Berg/BB_index_main.htm
SAP SDN Community web page for Business Intelligence Performance
Tuning https://www.sdn.sap.com/irj/sdn/bi-performance-tuning
ASUG407 - SAP BW Query Performance Tuning with Aggregates by
Ron Silberstein (requires SDN or Marketplace log-on). 54 min movie.
https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/media/uuid/d9fd
84ad-0701-0010-d9a5-ba726caa585d
Large scale testing of SAP BI Accelerator on a NetWeaver Platform
https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/b00
e7bb5-3add-2a10-3890-e8582df5c70f
56
Your Turn!
How to contact me:
Dr. Bjarne Berg
[email protected]
57