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

How to improve query performance built on a ODS

Hi,

I've built a report on FI_GL ODS (BW3.5). The report execution time takes almost 1hr.

Is there any method to improve or optimize th query performance that build on ODS.

The ODS got huge volume of data ~ 300 Million records for 2 years.

Thanx in advance,

Guru.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 22, 2009 at 06:33 AM
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 22, 2009 at 06:39 AM

    Hi Raj,

    Here are some few tips which helps you in improving ur query performance

    Checklist for Query Performance

    1. If exclusions exist, make sure they exist in the global filter area. Try to remove exclusions by subtracting out inclusions.

    2. Use Constant Selection to ignore filters in order to move more filters to the global filter area. (Use ABAPer to test and validate that this ensures better code)

    3. Within structures, make sure the filter order exists with the highest level filter first.

    4. Check code for all exit variables used in a report.

    5. Move Time restrictions to a global filter whenever possible.

    6. Within structures, use user exit variables to calculate things like QTD, YTD. This should generate better code than using overlapping restrictions to achieve the same thing. (Use ABAPer to test and validate that this ensures better code).

    7. When queries are written on multiproviders, restrict to InfoProvider in global filter whenever possible. MultiProvider (MultiCube) queries require additional database table joins to read data compared to those queries against standard InfoCubes (InfoProviders), and you should therefore hardcode the infoprovider in the global filter whenever possible to eliminate this problem.

    8. Move all global calculated and restricted key figures to local as to analyze any filters that can be removed and moved to the global definition in a query. Then you can change the calculated key figure and go back to utilizing the global calculated key figure if desired

    9. If Alternative UOM solution is used, turn off query cache.

    10. Set read mode of query based on static or dynamic. Reading data during navigation minimizes the impact on the R/3 database and application server resources because only data that the user requires will be retrieved. For queries involving large hierarchies with many nodes, it would be wise to select Read data during navigation and when expanding the hierarchy option to avoid reading data for the hierarchy nodes that are not expanded. Reserve the Read all data mode for special queriesu2014for instance, when a majority of the users need a given query to slice and dice against all dimensions, or when the data is needed for data mining. This mode places heavy demand on database and memory resources and might impact other SAP BW processes and tasks.

    11. Turn off formatting and results rows to minimize Frontend time whenever possible.

    12. Check for nested hierarchies. Always a bad idea.

    13. If "Display as hierarchy" is being used, look for other options to remove it to increase performance.

    14. Use Constant Selection instead of SUMCT and SUMGT within formulas.

    15. Do review of order of restrictions in formulas. Do as many restrictions as you can before

    calculations. Try to avoid calculations before restrictions.

    17. Turn off warning messages on queries.

    18. Check to see if performance improves by removing text display (Use ABAPer to test and validate that this ensures better code).

    19. Check to see where currency conversions are happening if they are used.

    20. Check aggregation and exception aggregation on calculated key figures. Before aggregation is generally slower and should not be used unless explicitly needed.

    21. Avoid Cell Editor use if at all possible.

    22. Make sure queries are regenerated in production using RSRT after changes to statistics, consistency changes, or aggregates.

    23. Within the free characteristics, filter on the least granular objects first and make sure those come first in the order.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 22, 2009 at 10:40 AM
    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.