Buy

Buy

List of Posts

Get this widget
To get notifications, Please like our Facebook Page >>>>>>

Use Search box to find a topic in this Blog!!!

Aggregates -- SAP BW Query Performance

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

2 comments:

Note

This blog is solely for the purpose of getting educated in SAP. This blog does not encourage distribution of SAP hold copyright and any other publication and/or materials bearing SAP mark. If you find any copyright materials, please mail me so I can remove them. This blog is not in association with SAP.

ALL Posts

Get this widget