Subselect vs Slicer in MDX

Question

If I want my results to be evaluated in the context of any tuple in MDX, but don't want this tuple to be part of the results, I use either of the below two options.

1. SUBSELECT

SELECT [Measures].[SomeMeasure] ON 0,
[DimName].[HierName].children ON 1
FROM
(SELECT foo.bar.&[Val] ON 0 FROM
[MyCube])

2. SLICER

SELECT [Measures].[SomeMeasure] ON 0,
[DimName].[HierName].children ON 1
FROM    
[MyCube]
WHERE (foo.bar.&[Val])

Third option that came to my mind is EXISTSclause, but soon I realized that it is meant for something else altogether.

So, other aspects aside, I am interested in the general performance of these queries, any benchmarks or best practices to be kept in mind and which one to go for in which circumstances.

Answer 1

As mostly with optimizer questions, the answer is: It depends. I would say WHERE is faster in many situations, but there are cases where subselect is faster.

Optimizers are a normally not documented to each detail by vendors (even if some ore more documented than others, and Analysis Services is a typical example of an engine with a less documented optimizer). I would think they have many, many rules in their code like "if this and that, but not a third condition, then go along that route". And this constantly changes, hence any documentation would be outdated with more or less each hotfix.

As said, the situation is a bit better for many relational engines, where for SQL Server, you can at least show a plan that is more or less understandable. But even there you do not know why exactly the optimizer chose this plan and not another, and sometimes have to try several approaches to get the optimizer on another path (like using an index, ...). And a new release of SQL Server may handle things differently, hopefully better in most cases, but possibly worse in a few rare cases.

That clearly is also not a clear and documented way of writing code, but just trial and error.

In summary: You will have to test with your cube and your typical queries.

Anyway, in many cases, the performance difference is so small that it is not relevant.

Finally, the best documentation that is available for the Analysis Services optimizer is the old blog of one of the Analysis Services query engine developers at http://sqlblog.com/blogs/mosha/default.aspx. This being a blog, it is not very systematic, but just a collection of some random samples of optimizer behavior with the reasons behind it.

Answer 2

As far as I know, If you want to cache results of your queries and improve overall throughput then slicer is better, but if you just care about single query performance then you can get better performance with subselect.

Answering the question below, Following information is from Chris Webb

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c1fe120b-256c-425e-93a5-24278b2ab1f3/subselect-or-where-slice?forum=sqlanalysisservices First of all, it needs to be said that subselects and the Where clause do two different things - they're not interchangeable in all circumstances, they may return different results, and sometimes one performs better, sometimes the other does, because they may result in different query plans being generated. One technique is not consistently 'better' than the other on all cubes, and any differences in performance may well change from service pack to service pack.

To answer the original question: use whichever you find gives you the best query performance and returns the results you want to see. In general I prefer the Where clause (which is not deprecated); the reason is that while a subselect may perform faster initially in some cases, it limits Analysis Services' ability to cache the result of calculations which means slower performance in the long term: http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!3057.entry