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

Short answer: In SP1 the PPS Planning add-in will use the where clause and life for MDX experts will be back to normal.*

Long answer: Because of the extra properties needed for writeback. The CellSet returned by AdoMd.Net does not return member properties for members specified in the where clause. What additional properties the add-in needs is a topic for another post. In many cases nobody notices that by putting the filter selections on pages (which is more like a crossjoin of a single element than a where filter) that there is any difference in the filter. But everybody notices when they take try to run the Matrix query in most other MDX tools like SQL Management Studio:

Executing the query ...
Obtained object of type: Microsoft.AnalysisServices.AdomdClient.CellSet
Formatting.
Results cannot be displayed for cellsets with more than two axes.
Execution complete

(Trust me, that display error irritates me an awful lot, just so you know I feel your pain if you don't like this. It's definitely something I am very excited about in SP1 that won't impact many users. But for the troubleshooters I think it is a fantastic luxury to just cut and paste the query for a change...ok you will still have to search and replace $CurrentPeriod$...)

In the matrix itself we don't really render a multi-dimensional result, we just render the first slice so that it looks like a 2D result. (And for our query we try hard to make sure that the 3D result we produce only has a single slice returned which is equivalent a 2D result and no extra data is queried other than the member properties).

So in practice the "on pages" approach worked well to simplify the rendering engine at first. But crucially there are some cases we've found where it degrades performance of the MDX query. And at least in current builds of AS2005 that PPS depends on, the cost of running an extra query to retrieve the missing member properties is negligible. So we were able to make this change in SP1. There simply wasn't enough time for us to have made this change earlier - and I don't remember if when we were working with pre-release versions of AS2005 if the second query for properties was always of negligible cost.

Another convenient side effect of this change in SP1 is that the filter selection is now conveniently available in custom MDX scenarios...but that, of course, is another post.

 

* As of this writing I can't promise any dates for SP1. All I've seen is what it pretty commonly known: spring 2008. We of course have an internal schedule and targets, but software releases are not easy. When the date is known and "official", It will be posted quite widely, on clearly "official" microsoft.com sites, including the technet forums as soon as there's an official date. (Oh and don't worry, asfaik the core PPS dev/test/pm team is based in the northern hemisphere so spring isn't 6 months away.)

Comments

• Anonymous
  April 18, 2008
  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

• Anonymous
  April 18, 2008
  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.

• Anonymous
  April 18, 2008
  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.

• Anonymous
  April 18, 2008
  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

• Anonymous
  April 19, 2008
  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.