Aspects of SAP BW Query Performance
Sound Data Model
Dimensional Modelling
Logical Partitioning
Physical Partitioning
BW Reporting Performance
Aggregates
Pre-calculated Web Templates
OLAP Cache
Definition: Aggregates are materialized subsets of InfoCube data, where the data is pre-aggregated and stored in an InfoCube structure.Definition: Aggregates are materialized subsets of InfoCube data, where the data is pre-aggregated and stored in an InfoCube structure.
Purpose: To accelerate the response time of queries, by reducing the amount of data that must be read in the database for a navigation step.
Aggregates can be created:
For Basic InfoCubes
On dimension characteristics
On 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)
Defining Aggregates
* Group according to characteristic or attribute value
H Group according to nodes of a hierarchy level
F Filter according to fixed value
Aggregation Using Hierarchies
Time-independent hierarchies are stored outside the dimension, in this example, in a table called /BI0/ICOUNTRY
OLAP Processor: Query Splitter
The split of a query is rule-based: Parts of the query on different aggregation level are split. Parts with different selections on characteristic are combined. Parts on different hierarchy levels or parts using different hierarchies are split.
After the split, OLAP processor searches for an optimal aggregate each part. Parts which use the same aggregate will be combined again (in some cases it is not possible to combine them).
runtimeSuggestion based on the last entry of the database tables RSDDSTAT/RSDDSTATAGGRDEF for the current user
Suggestion based on the database tables RSDDSTAT/RSDDSTATAGGRDEF
Suggestion based on the InfoCube BW StatisticsChoose
Proposals can be restricted to queries with a minimum runtimeSuggestion
Choose the period to be used for the proposalsProposals
Aggregates suggested from BW statistics get the name STAT <N>
Aggregates suggested from BW statistics get the names MIN <N> and MAX <N>
Building Good Aggregates
Tips for Building (and Maintaining) Good Aggregates:
Relatively small compared to parent InfoCube
Try for summarization ratios of 10 or higher??Find good subsets of data (frequently accessed)
Build on some hierarchy levels, not all
Not too specific, not too general -should serve many different query navigations
Consider "component"aggregates??Should be frequently used and used recently (except basis aggregates)
Building Bad Aggregates
Characteristics of Bad Aggregates:
Too many very similar aggregates
Aggregates not small enough (compared to parent cube)
Too many "for a specific query"aggregates, not enough general ones
Old aggregates, not used recently??Infrequently or unused aggregates
Exceptions:
A large aggregate containing navigational attributes may benefitperformance despite its size (but remember the tradeoff)
Basis Aggregate may be large and may not be used for reporting but still be useful for maintenance
Analysis Tools: Workload Monitor (ST03)
Scenario
Find out the queries with the worst performance and try to optimize them.
Useful Features
Expert Mode
BW System Load -->Analysis of table RSDDSTAT
Check following parameter values:
Check queries with highest runtimes and check where most time has been consumed
OLAP init
DB??OLAP
Frontend
Check for ratio of selected to transferred
How to Tell if an Aggregate Will Help
Do the following steps
Call query or InfoCube overview in technical content or ST03
Sort by mean overall time to find queries/InfoCubes with highestruntimes
Calculate the KPI 'aggregation ratio'= number of records read from DB / number of records transferred
Check quota of database time to total runtime
As a rule of thumb, an aggregate will be helpful if the query statistics show
Summarization Ratio > 10Summarization Ratio > 10,I.e. 10 times more records are read than are displayed, AND
Percentage of DB time > 30%Percentage of DB time > 30%,I.e. the time spent on database is a substantial part of the whole query runtime
Overview: Reporting Performance Analysis Tools
Table RSDDSTATBW
Queriesof BW STATISTICS
Usingtable RSDDSTAT asInfoSourceTable
ST03Collecting information fromtable RSDDSTAT
Function moduleRSDDCVER_RFC_BW_STATISTICS
BW Workload Analysis -ST03
Sound Data Model
Dimensional Modelling
Logical Partitioning
Physical Partitioning
BW Reporting Performance
Aggregates
Pre-calculated Web Templates
OLAP Cache
Definition: Aggregates are materialized subsets of InfoCube data, where the data is pre-aggregated and stored in an InfoCube structure.Definition: Aggregates are materialized subsets of InfoCube data, where the data is pre-aggregated and stored in an InfoCube structure.
Purpose: To accelerate the response time of queries, by reducing the amount of data that must be read in the database for a navigation step.
Aggregates can be created:
For Basic InfoCubes
On dimension characteristics
On 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)
Defining Aggregates
* Group according to characteristic or attribute value
H Group according to nodes of a hierarchy level
F Filter according to fixed value
Aggregation Using Hierarchies
Time-independent hierarchies are stored outside the dimension, in this example, in a table called /BI0/ICOUNTRY
OLAP Processor: Query Splitter
The split of a query is rule-based: Parts of the query on different aggregation level are split. Parts with different selections on characteristic are combined. Parts on different hierarchy levels or parts using different hierarchies are split.
After the split, OLAP processor searches for an optimal aggregate each part. Parts which use the same aggregate will be combined again (in some cases it is not possible to combine them).
runtimeSuggestion based on the last entry of the database tables RSDDSTAT/RSDDSTATAGGRDEF for the current user
Suggestion based on the database tables RSDDSTAT/RSDDSTATAGGRDEF
Suggestion based on the InfoCube BW StatisticsChoose
Proposals can be restricted to queries with a minimum runtimeSuggestion
Choose the period to be used for the proposalsProposals
Aggregates suggested from BW statistics get the name STAT <N>
Aggregates suggested from BW statistics get the names MIN <N> and MAX <N>
Building Good Aggregates
Tips for Building (and Maintaining) Good Aggregates:
Relatively small compared to parent InfoCube
Try for summarization ratios of 10 or higher??Find good subsets of data (frequently accessed)
Build on some hierarchy levels, not all
Not too specific, not too general -should serve many different query navigations
Consider "component"aggregates??Should be frequently used and used recently (except basis aggregates)
Building Bad Aggregates
Characteristics of Bad Aggregates:
Too many very similar aggregates
Aggregates not small enough (compared to parent cube)
Too many "for a specific query"aggregates, not enough general ones
Old aggregates, not used recently??Infrequently or unused aggregates
Exceptions:
A large aggregate containing navigational attributes may benefitperformance despite its size (but remember the tradeoff)
Basis Aggregate may be large and may not be used for reporting but still be useful for maintenance
Analysis Tools: Workload Monitor (ST03)
Scenario
Find out the queries with the worst performance and try to optimize them.
Useful Features
Expert Mode
BW System Load -->Analysis of table RSDDSTAT
Check following parameter values:
Check queries with highest runtimes and check where most time has been consumed
OLAP init
DB??OLAP
Frontend
Check for ratio of selected to transferred
How to Tell if an Aggregate Will Help
Do the following steps
Call query or InfoCube overview in technical content or ST03
Sort by mean overall time to find queries/InfoCubes with highestruntimes
Calculate the KPI 'aggregation ratio'= number of records read from DB / number of records transferred
Check quota of database time to total runtime
As a rule of thumb, an aggregate will be helpful if the query statistics show
Summarization Ratio > 10Summarization Ratio > 10,I.e. 10 times more records are read than are displayed, AND
Percentage of DB time > 30%Percentage of DB time > 30%,I.e. the time spent on database is a substantial part of the whole query runtime
Overview: Reporting Performance Analysis Tools
Table RSDDSTATBW
Queriesof BW STATISTICS
Usingtable RSDDSTAT asInfoSourceTable
ST03Collecting information fromtable RSDDSTAT
Function moduleRSDDCVER_RFC_BW_STATISTICS
BW Workload Analysis -ST03
Great Information sharing .. I am very happy to read this article .. thanks for giving us go through info.Fantastic nice. I appreciate this post. type 1 delivered birmingham
ReplyDelete