THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Slicer and axis interaction in MDX Part 2 - Implicit Exists

Last time we talked about interaction between slicer and axes in MDX SELECT query, we focused on how coordinate overwrite rules changed in SP2 to make actual results the same as expected results. This time we will discuss the implicit Exists between slicer and axes. We touched briefly on this subject, but it actually deserves more discussion. Let's start with basic example:

SELECT {} ON 0
, [Product].[Product].[Product] ON 1
FROM [Adventure Works]
WHERE [Product].[Subcategory].[Pedals]

Even though we asked for all products on rows, we are only getting 7 products in the resulset - all products from the Pedals subcategory. The reason is an implicit Exists between rows axis and slicer axis - i.e. rows actually returned

Exists([Product].[Product].[Product], [Product].[Subcategory].[Pedals])
This seems to be logical and good, but let's try now something a little bit more complex. Let's suppose that we are analyzing our heaviest products (perhaps because they cost us extra shipping fees). The query to look at 5 heaviest products would be

WITH MEMBER Measures.ProductWeight AS Product.Weight.CurrentMember.MemberValue
SELECT 
Measures.ProductWeight on 0
,TopCount([Product].[Product].[Product],5,Measures.ProductWeight) on 1
FROM [Adventure Works]

We got the top 5 products by weight - turns out that Rear and Front Wheels can be really heavy (In fact they are much heavier then the whole bike for some reason! Perhaps there are data quality problems in Adventure Works ?). Now we want to focus more on the Pedals subcategory, so we naturally change the query to

WITH MEMBER Measures.ProductWeight AS Product.Weight.CurrentMember.MemberValue
SELECT 
Measures.ProductWeight on 0
,TopCount([Product].[Product].[Product],5,Measures.ProductWeight) on 1
FROM [Adventure Works]
WHERE [Product].[Subcategory].[Pedals]

But this time, the result comes empty ! This looks really wrong. Well, the result can be explained - if we do implicit Exists between top level axis set and slicer axis, then we would get

Exists(TopCount([Product].[Product].[Product],5,Measures.ProductWeight), [Product].[Subcategory].[Pedals] )
And since we already saw that top 5 heaviest products are all wheels - none of them exists with Pedals subcategory - so the result is empty. This explanation might sound reasonable, but it doesn't help users - whatever the technical details of the design is - the result is counterintuitive, and the users will consider it as a bug. Especially, after we notice, that subselects behave really good in the very same scenario:

WITH MEMBER Measures.ProductWeight AS Product.Weight.CurrentMember.MemberValue
SELECT 
Measures.ProductWeight on 0
,TopCount([Product].[Product].[Product],5,Measures.ProductWeight) on 1
FROM 
(SELECT [Product].[Subcategory].[Pedals] ON 0
 FROM [Adventure Works])

We get, as expected, top 5 pedal products by weight. Turns out, that subselects apply implicit Exists in a smarter way then WHERE clause. Subselects apply Exists not to the end result of the axis, but they can push it deeper, depending on the semantics of the MDX functions used to construct the set. I.e. instead of doing

Exists(TopCount([Product].[Product].[Product],5,Measures.ProductWeight), [Product].[Subcategory].[Pedals] )

subselects will actually do

TopCount(Exists([Product].[Product].[Product],[Product].[Subcategory].[Pedals]),5,Measures.ProductWeight)

For functions like Filter or Order it doesn't matter whether to do Exists before or after applying the function since the results are going to be the same, but it does matter for performance - the deeper Exists is pushed - the better performance is likely to be. Of course, query optimizer takes all this into consideration. And it will work correctly when there are as many nested functions as you want - i.e.

WITH MEMBER Measures.ProductWeight AS Product.Weight.CurrentMember.MemberValue
SELECT 
Measures.ProductWeight on 0
,Filter(TopCount([Product].[Product].[Product],5,Measures.ProductWeight), ProductWeight > 170) on 1
FROM (SELECT [Product].[Subcategory].[Pedals] ON 0
FROM [Adventure Works])

Exists is going to be pushed to the deepest nested call here - i.e. into TopCount.

So it appears that subselect does the right thing, but WHERE clause doesn't, and, as we noted, most client tools do use WHERE and not subselects. Therefore, SP2 introduced additional change, to make implicit Exists between WHERE and axes to go as deep as subselect does - therefore the following query on SP2 returns good results

WITH MEMBER Measures.ProductWeight AS Product.Weight.CurrentMember.MemberValue
SELECT 
Measures.ProductWeight on 0
,TopCount([Product].[Product].[Product],5,Measures.ProductWeight) on 1
FROM [Adventure Works]
WHERE [Product].[Subcategory].[Pedals]
Published Sunday, November 12, 2006 2:49 PM by mosha
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement