Filter value selection in Queries takes a long time

[Former Member]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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

[Former Member]

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

[Former Member]

You may want to review how/when stats are begin refreshed on the InfoCubes. You should either be refreshing them regularly either with the settings on the Perfromane tab or Process Chain, or check with your DBA to see if they are running BR CONNECT and the stats are being collected with it.

Inaccurate/missing stats can really cause bad things to happen with respect to query performance.

I still wouldn't rule out changing the Query Exec Filter option.

[Former Member]

Hello Pizzaman,

We have set the query execution option for the info object to be Only Posted Values in Navigation. Isnt this the standard SAP delivered option. So you are suggesting that the best option is from master table. Will look in that too. Thanks for your reply

[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.

[Former Member]

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

[Former Member]

HI,

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

hope it gives better result .