Why not use the "where clause" for filters instead of "on pages"?

re: Why not use the "where clause" for filters instead of "on pages"?

Alain — Fri, 18 Apr 2008 20:50:56 GMT

Thanks for the post. Do you have any other information on enhancements and/or fixes for SP1. I am loiking at updating my Project Plan and would like to know what items we can expect.

Thanks again

re: Why not use the "where clause" for filters instead of "on pages"?

Alan Whitehouse — Fri, 18 Apr 2008 21:05:17 GMT

Good information.

Peter, does there exist anywhere a comprehensive list all all the features/fixes in SP1? I keep seeing different bits and pieces on different blogs but have not seen a full list.

Thanks.

re: Why not use the "where clause" for filters instead of "on pages"?

petereb — Fri, 18 Apr 2008 21:18:41 GMT

On the add-in side it's mostly things related to rendering performance and bug fixes. (e.g. the rollup of all the hotfixes so far plus a couple more) So there really aren't new features per se, it's just that in addressing some perf. bottlenecks things like the on pages/where clause change which have other pleasant side effects. There is an official document that is pending will describe them but I don't think that can be published until the release date is firm, since that also means no more updates will happen. Depending on what the official news looks like I might have a few posts describing the new options or the improvements in more detail.

re: Why not use the "where clause" for filters instead of "on pages"?

ChrisWebb — Sat, 19 Apr 2008 10:04:36 GMT

Could you expand on the comment you made that running an MDX query with three axes performs worse than one with two and a where clause? I've heard rumours of this for a while but never managed to find any details on when this happened or why it was happening.

Thanks,

Chis

re: Why not use the "where clause" for filters instead of "on pages"?

petereb — Sat, 19 Apr 2008 17:46:33 GMT

Sure I actually just heard a better description from my boss about this yesterday (i didn't do the perf. investigation in this case). Specifically in our case we are generating a single position on the pages axis. So for us there is little functionality difference between pages and the where clause. But for AS they can't tell this from the query until after evaluating the query. (Maybe there is an obscure optimization they could do but the payoff doesn't seem big). We also looked at using the from select... sub cube but we had too many problems with calculated members especially in PC hierarchies.

Anyway the where clause and the "from select...." sub cube are kinds of filtering of the cube that can be done before hand. They both let AS efficiently run the query over a subset of the data.

On pages is more like a crossjoin, the cube cannot be scoped further, its just the query result that is scoped. So there's no advantage for AS.

When I login to work again I can get the exact phrasing that was used to describe it to me if you want. But conceptually I think this description is enough. (Offhand I don't know the perf. numbers either)

Since we just need metadata from the members selected in the where clause our 2nd query is very fast and essentially always serviced by the cache. It just makes our code a little more complex, but even ignoring a perf benefit, the convenience of using the where clause I think makes this worth it in the long run.