cancel
Showing results for 
Search instead for 
Did you mean: 

Query performane

Former Member
0 Kudos

How we do query performance various techniques used in Bi 7.0?

Regards,

Sridhar

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member345199
Active Contributor
0 Kudos

Hi,

This info may be helpful.

General tips

Using aggregates and compression.

Using less and complex cell definitions if possible.

1. Avoid using too many nav. attr

2. Avoid RKF and CKF

3. Many chars in row.

By using T-codes ST03 or ST03N

Go to transaction ST03 > switch to expert mode > from left side menu > and there in system load history and distribution for a particual day > check query execution time.

Try table rsddstats to get the statistics

Using cache memoery will decrease the loading time of the report.

Run reporting agent at night and sending results to email.This will ensure use of OLAP cache. So later report execution will retrieve the result faster from the OLAP cache.

Also try

1. Use different parameters in ST03 to see the two important parameters aggregation ratio and records transferred to F/E to DB selected.

2. Use the program SAP_INFOCUBE_DESIGNS (Performance of BW infocubes) to see the aggregation ratio for the cube. If the cube does not appear in the list of this report, try to run RSRV checks on the cube and aggregates.

Go to SE38 > Run the program SAP_INFOCUBE_DESIGNS

It will shown dimension Vs Fact tables Size in percent.If you mean speed of queries on a cube as performance metric of cube,measure query runtime.

3. To check the performance of the aggregates,see the columns valuation and usage in aggregates.

Open the Aggregates...and observe VALUATION and USAGE columns.

"---" sign is the valuation of the aggregate. You can say -3 is the valuation of the aggregate design and usage. ++ means that its compression is good and access is also more (in effect, performance is good). If you check its compression ratio, it must be good. -- means the compression ratio is not so good and access is also not so good (performance is not so good).The more is the positives...more is useful the aggregate and more it satisfies the number of queries. The greater the number of minus signs, the worse the evaluation of the aggregate. The larger the number of plus signs, the better the evaluation of the aggregate.

if "-----" then it means it just an overhead. Aggregate can potentially be deleted and "+++++" means Aggregate is potentially very useful.

In valuation column,if there are more positive sign it means that the aggregate performance is good and it is useful to have this aggregate.But if it has more negative sign it means we need not better use that aggregate.

In usage column,we will come to know how far the aggregate has been used in query.

Thus we can check the performance of the aggregate.

Refer.

http://help.sap.com/saphelp_nw70/helpdata/en/b8/23813b310c4a0ee10000000a114084/content.htm

http://help.sap.com/saphelp_nw70/helpdata/en/60/f0fb411e255f24e10000000a1550b0/frameset.htm

4. Run your query in RSRT and run the query in the debug mode. Select "Display Aggregates Found" and "Do not use cache" in the debug mode. This will tell you if it hit any aggregates while running. If it does not show any aggregates, you might want to redesign your aggregates for the query.

Also your query performance can depend upon criteria and since you have given selection only on one infoprovider...just check if you are selecting huge amount of data in the report

Check for the query read mode in RSRT.(whether its A,X or H)..advisable read mode is X.

5. In BI 7 statistics need to be activated for ST03 and BI admin cockpit to work.

By implementing BW Statistics Business Content - you need to install, feed data and through ready made reports which for analysis.

http://help.sap.com/saphelp_nw70/helpdata/en/26/4bc0417951d117e10000000a155106/frameset.htm

/people/vikash.agrawal/blog/2006/04/17/query-performance-150-is-aggregates-the-way-out-for-me

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/1955ba90-0201-0010-d3aa-8b2...

You can find out whether an aggregate is usefull or useless you can find out through a proccess of checking the tables RSDDSTATAGGRDEF*

Run the query in RSRT with statistics execute and come back you will get STATUID... copy this and check in the table...

This gives you exactly which infoobjects it's hitting, if any one of the object is missing it's useless aggregate.

Achieving BI Query Performance Building Business Intelligence

http://www.dmreview.com/issues/20051001/1038109-1.html

ODS Query Performance  

Performance Tuning with the OLAP Cache

http://www.sapadvisors.com/resources/Howto...PerformanceTuningwiththeOLAPCache$28pdf$29.pdf

OLAP: Cache Monitor 

http://help.sap.com/saphelp_nw2004s/helpdata/en/41/b987eb1443534ba78a793f4beed9d5/frameset.htm

Cube Performance

Hope this helps.

Thanks,

JituK

Former Member
0 Kudos

Hi Sridhar,

Please find links below:

for query performance

oss note

557870 'FAQ BW Query Performance'

and 567746 'Composite note BW 3.x performance Query and Web'.

Prakash weblog for good query design

BW Performance Tuning Knowledge Center - SAP Developer Network (SDN)

Hope it helps you!

Regards,

Priyanka

Former Member
0 Kudos

You may wish to check my link below

https://forums.sdn.sap.com/click.jspa?searchID=10346224&messageID=3990271

Hope it Helps

Chetan

@CP..