Subselect vs Slicer in MDX
If I need my results to be evaluated in the context of any MDX tuple, but don't want that tuple to be part of the results, I use one of the following two parameters.
1. Subselection
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])
The third option that came to my mind was a proposal EXISTS
, but I soon realized that it was meant for something else altogether.
So other aspects aside, I am interested in the overall performance of these queries, any benchmarks or best practices to keep in mind, and which ones to look for in what circumstances.
source to share
As with the optimization questions, the answer is: it depends. I would say that in many situations it is faster in all cases, but there are cases where subselect is faster.
Optimizers are usually not documented in every detail by vendors (even though some are more documented than others, and Analysis Services is a typical example of an engine with a less documented optimizer). I would have thought that there are many rules in their code, for example "if this and this, but not the third condition, then go this way." And that is constantly changing, so any documentation will be outdated with more or less every fix.
As said, the situation is slightly better for many relational engines, where for SQL Server you can at least show a plan that is more or less clear. But even there, you don't know why exactly the optimizer chose this plan and not another, and sometimes you have to try several approaches in order to optimize in a different way (for example, using an index ...). And the newer version of SQL Server can handle things differently, hopefully better in most cases, but perhaps worse in a few rare cases.
This is clearly not a clear and documented way of writing code, but just trial and error.
In brief: . You will have to test your cube and your typical queries.
Anyway, in many cases, the performance difference is so small that it doesn't matter.
Finally, the best documentation available for the Analysis Services Optimizer is the old blog from one of the Analysis Services Query Engine developers at http://sqlblog.com/blogs/mosha/default.aspx . This is a blog, it is not very systematic, but is a collection of random samples of optimizer behavior with the reasons behind it.
source to share
As far as I know, if you want to cache the results of your queries and improve overall throughput then slicer is better, but if you just care about the performance of a single query, you can get better performance with a subquery.
Answering the question below, the 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 must be said that subselects and Where clauses do two different things: they are not interchangeable under all circumstances, they can return different results, and sometimes some work better, sometimes others do because they can result in different query plans. One method is not always "better" than the other on all cubes, and any performance differences may well change from service pack to service pack.
To answer the original question: use what 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 the subquery may be faster in some cases, it limits the ability of Analysis Services to cache the computation result, which means slower performance in the long run: http://cwebbbi.spaces.live.com/blog/cns! 7B84B0F2C239489A! 3057.entry
source to share