Skip to Content
author's profile photo Former Member
Former Member

Query performane

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

Regards,

Sridhar

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Apr 03, 2008 at 12:39 AM

    You may wish to check my link below

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

    Hope it Helps

    Chetan

    @CP..

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 03, 2008 at 04:46 AM

    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

    Query Creation Checklist

    Query Optimization

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

    Business Intelligence Performance Tuning

    Hope it helps you!

    Regards,

    Priyanka

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 03, 2008 at 04:54 AM

    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-8b2a4ef6bbb2

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.