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

Filter value selection in Queries takes a long time

Hello BW gurus....

I have a problem here. When a user tries to run a consolidated query it takes a long time. what are some of the performance tuning that can be done. My other issue is that once the query is run and the user tries to change the filter values say hes trying to restrict Cons group it takes like 30 min for the options to come up or even more than that and eventually fails. Is there any thing which i can do to improve the performance of these filter value selection option. we are on portal EP 6.0. I know this has nothing to do with interface but the crux of the problem is in the back end.

Any help is really appreciated

thanks

SG

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Aug 29, 2006 at 08:43 PM

    To expand on Kedar's post -

    Check the Infobject that they are trying to filter on. Under the Business Exploere tab for the object, You have a Query Filter Execution Val Selection Option. It is probably set to <b>Only Posted Values in Navigation</b>. This means that the only options that should be presented to the user to select from are those that meet all the other query criteria. In order ot do this, BW starts a SQL query that joins some of the the fact table to some of th edimension and master data tables and writes teh values for Cons Group that it found to a temp table and that is what is presented to the user.

    Now if you have large cube, it can take a while to read teh fact table and find out the values that meet the rest of your query's criteria.

    By choosing <b>Only Values in InfoProvider</b>, BW only needs to read the dimension table for that characteristic and finds all the values that exist in the InfoCube, although some might be valid for your query based on other restrictions you might have, so you can get a No Available Data result. This is very fast unless your dimension is very large, but almost certainly faster than the previous option.

    The other option is <b>Only Values in Master Data</b>. Bw just reads the values from the master data table for Cons group. Some of these values may simply not exist anywhere in your InfoCube, but it will present th elist very quickly.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Unfortunately the setting "Only posted values in Navigation" will not be used if the query is run on web. There is a parameter called <booked_values>on the web template that controls this this Note: 817335

  • author's profile photo Former Member
    Former Member
    Posted on Aug 29, 2006 at 07:32 PM

    HI,

    Just try to create an aggregate depending on the filters and the user selection .

    hope it gives better result .

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 29, 2006 at 07:40 PM

    Hi,

    For the filter criteria, you can try to switch the look up to Master data or the dimension.

    For general query performance run ST03 in expert mode to check and see where most time is being taken. Based on that you can check to see where you can tightern things. Aggregates is one option, refreshing stats on the cube is another. Check to see if there are any formulae or customer exit variables that are taking a long time and see if you can do the same using update rules, etc. When you run ST03, you'll anyways know where the problem lies, Frontend, GUI, backend, etc.

    Cheers,

    Kedar

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 30, 2006 at 03:39 PM

    Thanks for all your replies I ran the DB stat and that had consideralbly increased the performance.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      The Only Posted Values in Navigation generates a query that must examine most to your cube to find all filter options that correspond to your query. If the cube is large and you don't have much in the way of other restrictions, this can take a while. The benefit of this option is that the user is only presented with filter options that are in the domain they are interested in.

      If you switch to either of the other options, the filter options are quickly deteremined by examing either the dimension or master table. The downside is that those filter options may not be in the data the your query would exclude, e.g.

      Lets say you have a query on Bus Area and Cost Center.

      - Master Data for Cost Centers = 0001 - 9999

      - Cost Centers in your data are 0001 - 2999

      - Only Cost Centers 1000 - 1099 in Bus Area 10.

      If you have an input variable to select only Bus Area = 10 and run the query, then want to filter on Cost Center:

      - with Only Values in Navigation, the user would only see Cost Centers 1000 - 1099 in the filter selection list.

      - with Only Values in InfoProvider, the user would see Cost Centers 1000 - 2999.

      - with Only Values in Master Data, user would see 0001 - 9999.

      This may or may not be acceptable. If a user chooses Cost Center 2000, the query would copme back with No Available Data since ther are not transaction with Bus Area = 10 and Cost Center 2000.

      Now lets say you are using CALMONTH, chances are the user would understand that if they select a November of 2006 while in August, they wouldn't get any data. They might understand what Cost Centers to expect back in Bus Area 10, but they may not be as familiar with all the values for all Characteristics.

      Like everything else, there are pros + cons.

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.