Banner
Banner
Banner

Attention: open in a new window. PDFPrintE-mail

2007
12
Nov

How to do performance tuning on InfoCube?

I have a report that was generating results a couple of days back but when I am trying to run it today, am getting a timeout error..can someone suggest how I can fine tune the infocube to get the result?

 

Quick Guide - BW Performance Tuning by Shreekant W. Shiralkar
Download extract
See more detail

Extract of the foreword: "In this book we have listed performance tuning parameters right through the data model, to extraction of data, to data loading, to fine tuning of the database while ending with proactive operational maintenance mode (e.g. review aggregates, build new ones, delete unused ones, find problem queries) as an ongoing cycle.
Also covered in the book is how to evaluate the actual performance whether there is a scope of improvement or other avenues of performance tuning have to be explored. However the book is not dealing with ‘extremely technical’ aspects of the performance tuning, as such content would cater to “purely technical” readers..."

Your question is very general and performance is a big, important and difficult topic in BW.
First steps to improve performance.
Go to Cube Management -> Tab Performance:
Create Index
Create Statistics

On Tab -> Rollup you can create Aggregates.
To learn more about aggregates check http://service.sap.com for Document "Performance Tuning for Queries with Aggregates"

-----------------------------------------------

Try this hope it will solve your problem of report .

Steps are like this :-

1)Turn on the BW Statistics: RSA1, choose Tools -> BW statistics for InfoCubes
   (Choose OLAP and WHM for your relevant Cubes)
2)Check whether you have overall query performance problem or Single Query Performance problem

a)overall query performance problem
Use ST03N -> BW System load values to recognize the problem. Use the number given in table 'Reporting - InfoCubes:Share of total time (s)'  to check if one of the columns %OLAP, %DB, %Frontend shows a high number in all InfoCubes.
You need to run ST03N in expert mode to get these values

2)Single/specific Query performance
TX- ST03N

Use Details to get the runtime segments

Possible causes for the performance

A) High Database Runtime
B) High OLAP Runtime
C) High Frontend Runtime

Depending upon your analysis

A)Strategy - High Database Runtime
Check if an aggregate is suitable (use All data to get values "selected records to transferred records", a high number here would be an indicator for query performance improvement using an aggregate)

Check if database statistics are update to data for the Cube/Aggregate, use TX RSRV output (use database check
for statistics and indexes)

Check if the read mode of the query is unfavourable - Recommended (H)

B)Strategy - High OLAP Runtime

Check if a high number of Cells transferred to the OLAP (use "All data" to get value "No. of Cells")
a) Use RSRT technical Information to check if any extra OLAP-processing is necessary (Stock Query, Exception Aggregation, Calc. before Aggregation, Virtual Char. Key Figures, Attributes in Calculated Key Figs, Time-dependent Currency Translation) together with a high number of records transferred.
b) Check if a user exit Usage is involved in the OLAP runtime?
c) Check if large hierarchies are used and the entry hierarchy level is as deep as possible. This limits the levels of the hierarchy that must be processed.
Use SE16 on the inclusion tables and use the List of Value feature on the column successor and predecessor to see which entry level of the hierarchy is used.

C)Strategy - High Frontend Runtime
1)Check if a very high number of cells and formattings are transferred to the Frontend ( use "All data" to get value "No. of Cells") which cause high network and frontend (processing) runtime.
2) Check if frontend PC are within the recommendation (RAM, CPU Mhz)
3) Check if the bandwidth for WAN connection is sufficient.

Good Luck

Last Updated (Tuesday, 30 November 1999 00:00)
Banner
Free software downloads