Download Report

Session ID: BI-206
Performance tuning SAP
NetWeaver BW 7.x
Dr. Bjarne Berg
Agenda
• Background
• The Right Design

InfoCubes, DSOs and MultiProviders
• Performance at All Levels





Query Design and BOBJ Tips
Building and Using Aggregates
Using MDX and OLAP Cache correctly
Some Hardware settings
BW- Accelerator
• What is New in BW 7.3
• EarlyWatch Reports
2
User's reaction to poor performance
Is this your user community?
How can you avoid this BEFORE it happens?
3
The Key to BI Success
Performance is more important than price for the senior management.
1,873 managers and business
leaders were asked what
factor was most important for
their BI application.
Even in a recession, the key to
BI success was functionality,
ease of use, integration and
Performance.
Price, standards, product
reputation and architecture
was of lesser importance.
Source: Business
Research Center Survey
4
Agenda
• Background
• The Right Design

InfoCubes, DSOs and MultiProviders
• Performance at All Levels





Query Design and BOBJ Tips
Building and Using Aggregates
Using MDX and OLAP Cache correctly
Some Hardware settings
BW- Accelerator
• What is New in BW 7.3
• EarlyWatch Reports
5
Who wins? - Functionality Vs. Performance
6
An Example of a Real System
On the surface, this company
appears to have a typical BW
implementation with a set of
InfoCubes, MultiProviders and DSOs
The system supported five different
department and had been “live” for
about one year.
Number of BW storage objects
17
InfoCubes
Multicubes
ODS
1
2
7
A Quick View of the technical design
Just because an area is colored “red” does not mean it is
wrong. However, an InfoProvider with many red areas is worth
taking a close look at.
Key Question: How well do you
know your own system?
Ledger
Within typical design parameters
Approaching recommended regular configuration parameters
Not frequently used design parameters
BW InfoCubes Observations
Infocubes
Available options
Jobcost history cube
Options sold
MH AP line item cube
MH AR line item cube
MH general ledger cube
Cost cube
MH property master (LOT) cube
Purchasing item data
Inventory cube
PS controlling MH
PS dates MH
PS controlling and dates cube
Earnest money estimation cube
MHSD overview
SD commissions cube
Characteristics Navigational Time char- Hierarchies Dimensions Key figures
attributes
acteristics
20
4
4
2
9
4
57
0
1
0
12
1
23
19
4
1
11
14
52
13
4
3
10
15
29
0
4
1
8
20
30
2
5
6
8
9
14
0
3
2
8
2
24
2
4
1
11
7
13
10
5
3
11
15
33
4
2
3
9
23
44
37
5
10
12
35
25
19
5
4
8
17
34
48
3
5
9
30
40
24
5
7
13
16
49
28
5
7
13
27
51
22
4
4
10
21
Record Complexity
length
Moderate Moderate
High
High
Low
Low
High
High
Low
Low
Low
Low
Low
Low
Low
Low
Low
Low
Low
Low
High
Moderate
Moderate
Low
High
Low
Moderate
High
High
High
High 8
High
Characteristics
BW InfoCubes Observations
Infocubes
Available options
Jobcost history cube
Options sold
MH AP line item cube
MH AR line item cube
MH general ledger cube
Cost cube
MH property master (LOT) cube
Purchasing item data
Inventory cube
PS controlling MH
PS dates MH
PS controlling and dates cube
Earnest money estimation cube
MHSD overview
SD commissions cube
Characteristics Navigational Time char- Hierarchies Dimensions Key figures Record Complexity
attributes
acteristics
length
20
4
4
2
9
4
Moderate Moderate
High
57
0
1
0
12
1
High
23
19
4
1
11
14
Low
Low
52
13
4
3
10
15
High
High
Low
29
0
4
1
8
20
Low
Low
30
2
5
6
8
9
Low
Low
14
0
3
2
8
2
Low
24
2
4
1
11
7
Low
Low
13
10
5
3
11
15
Low
Low
Low
33
4
2
3
9
23
Low
High
44
37
5
10
12
35
Moderate
Moderate
25
19
5
4
8
17
Low
High
34
48
3
5
9
30
Low
40
24
5
7
13
16
Moderate
High
High
49
28
5
7
13
27
High
High
51
22
4
4
10
21
High
In general, a common BW configuration contains a set of characteristics
that are used for analysis purposes. The number of these characteristics
varies from implementation to implementations.
Typically configurations ranges from 1 to 40y characteristics. The design
depends largely on the requirements of the business, but there are some
technical tradeoffs in load times when adding a very high number of
characteristics. This is particularly true when these contains large text
fields that are loaded at a high frequency and at a high number of records.
9
Navigational Attributes and Hierarchies
Navigational attributes
Navigational attributes lends flexibility to the way users can access data.
Common configuration consists of 1 to 30y attributes. While technically not
incorrect one should review InfoCubes that does not contain any
navigational attributes. One should also review of any InfoCube that
contained more than thirty. This may be an indicator that too much
information is being placed in a single InfoCube.
Hierarchies
Hierarchies are ways for users to "drill-down" into the data and is
commonly used for analysis purposes. Typical configurations tends to
have one to eight. One should review any InfoCube with no hierarchy to
validate this design with end user navigation, as well as question any
design that contains a very high degree of these.
Developer quote: “The consultants told me I could not cram all this
stuff into one InfoCube. I told them – you just watch me!!”
10
Dimensions and Key Figures
Dimensions
BW allows for upto 13 dimensions to be created by a developer on a
single InfoCube. However, using all of these on a first implementation
severely limits any future extensions without major redesign of the
system. One should review any InfoCubes that are approaching this
limit.
Lessons Learned: Don’t “paint” yourself in a corner on day one!!
Key figures
While no limitations are imposed by BW in terms of number of key
figures (measures), typical implementations contains 1 to 20 of these.
While a higher number of these may be required, there a are
significant tradeoffs of load performance when a high number records
are loaded (these are loaded with each transaction).
11
Record Length
Record length
In general, as the record length of an InfoSource increases, more data
may be populated to the InfoCube.
Since an InfoCube might have more that one InfoSource, the length of
each may be an indicator of the InfoCube growth size as the company
rolls out BW to other divisions.
You should review of the design of InfoSources with large record
lengths to determine the true need of including all the fields in the
InfoCube vs. using alternate fields i.e. short text or codes, or removing
them from the system..
Lessons Learned: Don’t throw in the “kitchen sink”
because it might come in handy one day…..
12
Data Loads
BW Sources to InfoCubes/ODS and MultiCubes
Sources
Infocubes
Available options
Jobcost details multicube*
Jobcost history cube
Options sold
Pending close multicube (new)*
MH AP line item cube
MH AR line item cube
MH general ledger cube
General ledger line item ODS
Cost cube
MH property master (LOT) cube
Purchasing item data
Inventory cube
Inventory multicube*
PS controlling MH
PS dates MH
PS controlling and dates cube
Earnest money estimation cube
MHSD overview
SD commissions cube
Number
1
2
1
1
2
N/A
1
1
2
1
1
1
2
2
5
1
4
1
2
1
Complexity
Low
Low
High
Moderate
Low
High
Moderate
Low
High
Low
Low
Low
Moderate
Low
High
Moderate
High
Moderate
High
Moderate
When fixing data load
problems, narrow the
problem down quickly and
focus on these areas.
Most data loads can be
de-coupled from each
other in the process
chains.
This may reduce the time
needed for activation
Lessons Learned: Spend
your time and effort in a
focused manner!!
13
Database Performance - RSA1--> Manage InfoCubes --> Performance
Database statistics are used
by the database optimizer to
route queries. Outdated
statistics leads to
performance degradation.
Indexes
Statistics
Aggregates
Ledger
BW index diagnostic
BW diagnostic of statistics that is recommended to be updated
User designed aggregates (performance & existence)
Outdated indexes can lead
to very poor search
performance in all queries
where conditioning is used
(i.e. mandatory prompts).
14
Use of Line Item Dimensions and Monitoring tools
Line item dimensions are
basically fields that are
transaction oriented.
Programs that can help you monitor
the system design:
1.SAP_ANALYZE_ALL_INFOCUBES
2.ANALYZE_RSZ_TABLES
3.SAP_INFOCUBE_DESIGNS
Once flagged as a ‘line item
dimension’, the field is actually
stored in the fact table and have
no table joins.
The results is significant
improvements to query speeds
(10%-15%)
Explore the use line item dimensions for fields that are frequently
conditioned in queries. This model change can yield faster queries
15
B-three Vs. Bitmap 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
16
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 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).
17
MultiProviders and 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, is actually sequential
processing plus the startup phase of parallel processing.
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
18
More on MultiProviders and Parallel Processing
Consider deactivating parallel processing for those queries that are
MultiProvider queries and have large result sets (and “hints” cannot be
used)

Since SAP BW 3.0B SP14 , you can change the default value of 30,000 rows Refer to SAP Note 629541, SAP Note 622841, SAP Note 607164, and SAP Note
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, which results 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.
19
SAP BW 7.0 Performance - Data Activation
With BW 7.01 we can disable delta consistency check for writeoptimized DataStore objects. This protects delta requests that
have been already propagated per delta mode from deletion.
This can be switched on/off – e.g. for write-optimized DataStore
objects as initial staging layer. When doing so, significant load
performance benefits can be achieved (10-30%).
Higher benefits are obtained from very large InfoProviders with
thousands of requests.
20
Semantic partitioned object in SAP BW 7.3
• In BW 7.3 SPO is introduced to help partition InfoCubes for query
performance, and DSOs for load performance.
BW 7.3 provides wizards to help you
partition objects by year, business units or
products.
Source: SAP
AG, 2010
BW also generate automatically all needed
DTP such as transformation rules and
filters to load the correct infoProvider.
• SAP suggests that this will make the maintenance is easier since
any remodeling only need to change the reference structure.
SPOs can be added to MultiProviders for simpler
query administration and to mask complexity
21
Agenda
• Background
• The Right Design

InfoCubes, DSOs and MultiProviders
• Performance at All Levels





Query Design and BOBJ Tips
Building and Using Aggregates
Using MDX and OLAP Cache correctly
Some Hardware settings
BW- Accelerator
• What is New in BW 7.3
• EarlyWatch Reports
22
Query Read Modes
There are three query read modes that determines the
amount of data to be fetched from a database and sent to
the application server:
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
Data is read when requested by users in navigation
Key Feature: Reading data during navigation minimizes the
impact on the application server resources because only data
that the user requires will be retrieved
23
Recommendation: Query Read Mode for Large Hierarchies
• For queries involving large hierarchies, it is smart to select Read data
during navigation and when expanding this option to avoid reading
data for the hierarchy nodes that are not expanded.
• Reserve the Read all data mode for special queries— i.e. when a majority of
the users need a given query to slice and dice against all dimensions, or data
mining

This places heavy demand on database and memory resources and may impact
other BW processes

A query read mode can be defined on an individual query or as a default for new
queries (transaction RSRT)
SAP's recommendations for OLAP Universes & Ad-Hoc analysis
(formerly: 'Webi'):
1. Use of hierarchy variable is recommended
2. Hierarchy support in SAP Web Intelligence for SAP BW is limited
3. The Use Query Drill option significantly improves drilldown performance
4. Look at the 'Query Stripping' option for power users.
24
Reduce the use of conditions-and-exceptions reporting
Conditions & exceptions are usually processed by the application server
This generates additional data transfer between database & application servers
If conditions and exceptions have to be used, the amount of data to be
processed should be minimized with filters
When multiple drilldowns are required, separate the drilldown steps by using free
characteristics rather than rows and columns
BENEFIT: This 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 approach separates the drill-down steps. In addition to accelerating query
processing, it provides the user more manageable portions of data.
25
Performance settings for Query Execution
This decides how many records are read
during navigation.
Examine the
request status
when reading
the InfoProvider
In 7.x BI: OLAP
engine can read
deltas into the
cache. Does not
invalidate existing
query cache.
Turn off/on parallel
processing
Displays the level of
statistics collected.
When will the
query program be
regenerated based
on database
statistics
26
Filters in Queries
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 large
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.
27
The RSRT Transaction to examine slow queries
P1 of 4
The RSRT transaction is one of
the most beneficial transaction
to examine the query
performance and to conduct
'diagnostic' on slow queries.
28
Do you need an aggregate - some hints
P2 of 4
This suggests that an Aggregate
would have been beneficial
29
Get Database Info
P3 of 4
In this example, the basis
team should be involved
to research why the
Oracle settings are not
per SAP's
recommendation
The RSRT and RSRV
codes are key for
debugging and analyzing
slow queries.
30
Get Design Feedback in RSRT
P4 of 4
We can see that the system creates a yellow
flag for the 6 base cubes in the MultiProvider
and the yellow flag for the 14 free chars.
HINT: Track front-end data transfers & OLAP
performance by using RSTT in SAP 7.0 BI
(RSRTRACE in BW 3.5)
31
Debug Queries using the transaction- RSRT
Using RSRT you can execute the
query and see each breakpoint,
thereby debugging the query and
see where the execution is slow.
Try running slow queries in debug mode
with parallel processing deactivated to
see if they run faster.
32
The Performance Killers - Restrictive Key Figures
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
My 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?:
Benefit: Formulas within an Infoprovider are
returned at runtime and held in cache.
Drawback: Local formulas and selections are
calculated with each navigation step.
33
SAP's recommendation for Key Figures in OLAP universes
1. "A large number of Key Figures in the BEx query will incur a
significant performance penalty when running queries,
regardless of whether the Key Figures are included in the
universe or used in the SAP BusinessObjects Ad-hoc (WebI)
query
2. Only include KFs used for reporting in the BEx query
3. This performance impact is due to time spent loading metadata
for units, executed for all measures in the query".
After SAP BusinessObjects Enterprise XI 3.1 FP 1.1, the impact
of large number of key figures was somewhat reduced by
retrieving metadata information only when the unit/currency
metadata info is selected in the Webi Query
34
The Performance Killers - Calculated Key Figure
Calculated Key Figures (CKF) are computed
during run-time, and a many CKFs can slow
down the query performance.
How to fix this:
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).
SAP's recommendation for OLAP universes: "RKF and CKF should be built
as part of the underlying BEx query to use the SAP BW back-end processing for
better performance
Queries with a larger set of such KFs should use the “Use Selection of Structure
Members” option in the Query Monitor (RSRT) to leverage the OLAP engine"
35
The BI Analytical Engine and Sorting
Sorting is done by the BI Analytical
Engine. Like all computer systems,
sorting data in a reports with large
result sets can be time consuming.
Try reducing the number of sorts in the 'default view'. This
may improve the report execution & provide the users with
data faster. User can then choose to sort the data themselves.
Reducing the text in query will also
speed up the processing some.
36
Agenda
• Background
• The Right Design

InfoCubes, DSOs and MultiProviders
• Performance at All Levels





Query Design and BOBJ Tips
Building and Using Aggregates
Using MDX and OLAP Cache correctly
Some Hardware settings
BW- Accelerator
• What is New in BW 7.3
• EarlyWatch Reports
37
Correct Aggregates Are Easy to Build
We can create proposals from
the query, last navigation by
users, or by BW statistics
Create aggregate proposals based on
queries that are performing poorly.
Create aggregate proposals based
on BW statistics. For example:
• Select the run time of queries to
be analyzed
• Select time period to be analyzed
• Only those queries executed in this
time period will be reviewed to
create the proposal
38
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
Agenda
• Background
• The Right Design

InfoCubes, DSOs and MultiProviders
• Performance at All Levels





Query Design and BOBJ Tips
Building and Using Aggregates
Using MDX and OLAP Cache correctly
Some Hardware settings
BW- Accelerator
• What is New in BW 7.3
• EarlyWatch Reports
41
Different Uses of the MDX and the OLAP Cache
The OLAP Cache is used by BW as the core in-memory
data set. It retrieves the data from the server if the data
set is available.
The Cache is based on First-in --> Last out.
This means that the query result set that was accessed
by one user at 8:00am may no longer be available inmemory when another user is accessing it at 1:00pm.
Therefore, queries may appear to run slower sometimes.
The MDX cache is used by MDX based interfaces,
including the OLAP Universe.
42
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).
43
Agenda
• Background
• The Right Design

InfoCubes, DSOs and MultiProviders
• Performance at All Levels





Query Design and BOBJ Tips
Building and Using Aggregates
Using MDX and OLAP Cache correctly
Some hardware settings
BW- Accelerator
• What is New in BW 7.3
• EarlyWatch Reports
44
The Memory Cache Size
The OLAP Cache is by default 100 MB for local and 200 MB for global use
This may be too low...
Look at available hardware
and work with you basis team
to see if you can increase this.
If you decide to increase the
cache, use the transaction
code RSCUSTV14.
WARNING: 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
45
Monitor Application Servers and Adjust Cache Size
To monitor the usage of the cache on each of the application servers,
use transaction code RSRCACHE and also periodically review the
analysis of load distribution using ST03N – Expert Mode
PS! 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.
46
The Four Options for OLAP Cache Persistence Settings
CACHE OLAP Persistence settings
Note
When
What
t-code
Flatfile
Change the logical file
BW_OLAP_CACHE when
installing the system (not
valid name)
Optional
Cluster table
RSR_CACHE_DBS_IX
Medium and small result sets RSR_CACHE_DB_IX
Optional
Binary Large Objects
(blob)
Best for large result sets
Default
Available
since BW Blob/Cluster
7.0 SP 14 Enhanced
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 2010.
47
Application Server Memory Usage
In this real example, be the judge. Do we:
a) Need another application server?
b) Need to upgrade the application server with more hardware?
c) Performance tune the application?
Roll memory was never
maxed out in the period
Paging memory was never
maxed out in the period
Extended memory was never
maxed out in the period
Only 3Gb of 9 Gb of heap
memory was ever used
48
Agenda
• Background
• The Right Design

InfoCubes, DSOs and MultiProviders
• Performance at All Levels





Query Design and BOBJ Tips
Building and Using Aggregates
Using MDX and OLAP Cache correctly
Some Hardware settings
BW- Accelerator
• What is New in BW 7.3
• EarlyWatch Reports
49
Why In-memory processing?
Disk speed is growing slower than all other hardware components
Technology Drivers
1990
2010
CPU
0.05
253.31
MIPS/$
MIPS/$
Memory
0.02
50.15
MB/$
MB/$
216
264
Addressable
Memory
Network
Speed
100
100
Mbps
Gbps
Architectural Drivers
Improvement
5066x
2502x
248x
1000 x
1990
2010
Disk-based data
storage
In-memory data
stores
Simple
consumption of
apps (fat client
UI, EDI)
Multi-channel
UI, high event
volume, cross
industry value
chains
Generalpurpose,
applicationagnostic
database
Applicationaware and
intelligent data
management
Source: 1990 numbers SAP AG ; 2010 numbers, Dr. Berg
Disk
Data Transfer
5
600
MBPS
MBPS
120x
Physical hard drive speeds only grew by 120 times
50
since 1990.
All other hardware components grew faster.
In Memory Processing - General Highlights - BWA
3. Queries are routed to BWA
by the Analytical engine
SAP BW
BI Analytical Engine
2. Indexes copied in
to RAM on blades
InfoCubes
DSOs
51
1. Indexing and
compression
stored on a file
system
BI Analytical Engine’s Query Executing Priorities
Information Broadcasting /
Precalculation
Information Broadcasting /
Precalculation
Query Cache
Query Cache
Aggregates
SAP BW Accelerator
InfoProvider
Query Execution
Without SAP NetWeaver
BW Accelerator
Query Execution
With SAP NetWeaver
BW Accelerator
Aggregates can be replaced with SAP BW
Accelerator, while the memory cache is still useful.
52
Number of Queries
BW Accelerator Performance Increases - real example
Number of Queries
Seconds
Seconds
The major
improvement is
to make query
execution more
predictable and
overall faster
SAP BW Accelerator Administration is minimal and simple
The Admin work is
done through a
single interface
The admin
interface is
available under the
transaction code
RSDDBWAMON.
Health checks for
SAP BW
Accelerator are
available under the
transaction code
RSRV
Plan for 2-5 days of SAP BW Accelerator training. You
need a maximum of 1-2 administrators (1 for backup)
54
Health-Checks and Reconciliation
The SAP BW Accelerator
interface allows you to
compare the data in SAP
BW vs. the indexes. This
means that you can
easily check if they are
outdated.
Other tools include the
ability to run queries to
see if the numbers in the
two databases match.
55
Proposals and Estimations
The Analysis and Repair options
include many proposals and time
estimation tools that you should
leverage.
The interface can propose deltaindexes for periodic updates (not
complete builds).
You can estimate the run-time of
indexing the fact table of an
InfoCube before you place it into
a process chain or a manual job.
You can also estimate the
memory you need before you
add new records into memory.
56
The SAP BW Accelerator “Reset Button”
The simple way to fix
most issues is to delete
all indexes and rebuild
them during a weekend
Think of this as the
ultimate “reset” button
You can also rebuild
master data indexes
57
Agenda
• Background
• The Right Design

InfoCubes, DSOs and MultiProviders
• Performance at All Levels





Query Design and BOBJ Tips
Building and Using Aggregates
Using MDX and OLAP Cache correctly
Some Hardware settings
BW- Accelerator
• What is New in BW 7.3
• EarlyWatch Reports
58
SAP BW 7.3 Performance - Data Movement & Activation
BW version 7.3 has significant performance benefits:
1. Semantic Partitioned Objects (SPO) as we already covered.
2. Improved data activation due to new package fetch of active
table instead of single lookups. The new 7.3 runtime option
“new, unique data records only” prevents all lookups during
activation.
3. A new monitor in BW Administration Cockpit so that
database usage can be tracked.
59
Agenda
• Background
• The Right Design

InfoCubes, DSOs and MultiProviders
• Performance at All Levels





Query Design and BOBJ Tips
Building and Using Aggregates
Using MDX and OLAP Cache correctly
Some Hardware settings
BW- Accelerator
• What is New in BW 7.3
• EarlyWatch Reports
60
EarlyWatch Reports in Solution Manager 4.0
EarlyWatch reports provide a simple way to confirm how your
system is running and to catch problems
A “goldmine” for system recommendations
EarlyWatch reports are available since Solution manager
version 3.2 SP8.
The more statistics cubes you have activated in BW, the
better usage information you will get.
Depending on your version of SAP BW, you can activate 1113 InfoCubes. Also, make sure you capture statistics at the
query level (set it to 'all').
System issues can be hard to pin-down without access
to EarlyWatch reports. Monitoring reports allows you to
tune the system before user complains
61
Information about an pending 'disaster'
This system is
about to 'crash'
The system is
growing by 400+ Gb
per month, the app
server is 100%
utilized and the Db
server is at 92%.
This customer needed
to improve the
hardware to get the
query performance to
an acceptable level
62
Inconsistent patches may be caught
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
In this example, we see that the EarlyWatch report found many known
issues at the Oracle level that should be implemented before the
performance tuning effort started.
Before the patches were applied it took 24 to 26 minutes to execute
some queries, after the fixes, the queries ran at less then two minutes.
63
More at:
Performance tuning presentations, tutorials & articles
www.ComeritInc.Com
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/d9fd8
4ad-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/b00e
7bb5-3add-2a10-3890-e8582df5c70f
64
Questions
and
Answers
Dr. Berg
[email protected]
65