(MDX) EXISTING operator - inconsistent behaviour when using subcubes?

I am getting what I think is inconsistent behaviour when using the EXISTING operator to obtain a set defined by the current query context... See the two queries below (explanation follows).

WITH MEMBER [Measures].[MyDate] AS

Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue

SELECT {[Measures].[MyDate]} ON 0

FROM [Adventure Works]

WHERE [Date].[Date].[October 26, 2002]

... returns [26-10-2002]

WITH MEMBER [Measures].[MyDate] AS

Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue

SELECT {[Measures].[MyDate]} ON 0

FROM

(

SELECT [Date].[Date].[October 26, 2002] ON 0

FROM [Adventure Works]

)

... return [31-08-2004] (the last date in the Adventure Works time dimension).

From my point of view the two queries should return the same date (October 26, 2002), since the context is the same. The only difference is that in the first query the context is specified in the WHERE clause, whereas the second query specifies the context in a subcube. There really should not be any difference, should there?

I agree this looks confusing but I suspect it's intended functionality: calculated members can 'look outside' a subcube defined in the FROM clause (see http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!212.entry for example). This would be useful, for example, if you had a previous period growth calculation that you wanted to return a meaningful value even when you were querying a subcube which had been restricted to just one Date.|||

Chris, I was looking for an answer from you - not quite the answer I hoped for though.

I see your point, but if what you are saying is true, the value of the EXISTING operator is not very high and it will be very questionable if Moshas approach to writing "multiselect friendly MDX calculations" (http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx) is applicable (which I really hope it is!). Does it not also fail to explain why the following query only returns the sum of [Internet Sales Amount] from 2002 and 2003 (which - to me - is expected behavior)?

WITH MEMBER [Measures].[Test 2] AS

SUM(EXISTING [Date].[Date], [Measures].[Internet Sales Amount])

SELECT [Measures].[Test 2] ON 0

FROM

(SELECT {[Date].[Calendar Year].&[2002], [Date].[Calendar Year].&[2003]} ON 0 FROM [Adventure Works])

If what you are saying is true, wouldn't the calculated member return a sum for all years - since the calculated member would "look outside" the subsube, which is restricted to 2002 and 2003?

|||

I think you've made a mistake in your query - the expression [Date].[Date] resolves to the All Member of that hierarchy. I think what you wanted to do was this:

WITH MEMBER [Measures].[Test 2] AS

SUM(EXISTING [Date].[Date].[Date].Members, [Measures].[Internet Sales Amount])

SELECT [Measures].[Test 2] ON 0

FROM

(SELECT {[Date].[Calendar Year].&[2002], [Date].[Calendar Year].&[2003]} ON 0 FROM [Adventure Works])

Which returns a different (and higher) value, the sum of all years.

You're right, though, it does cause problems if you're using subcubes instead of sets in the where clause and following Mosha's advice on multi-select friendly MDX.

Chris

|||You're right Chris - thanks... I will try go get Mosha to comment on this issue... I will follow up in this thread.|||

I know this was an area where there was some discussion about what the most useful behaviour would be - on balance, I think they've done the right thing. As far as the multi-select issue goes, is there any reason why you're not using sets in the WHERE clause instead?

<Cheeky Plug>
This topic is discussed in the second edition of 'MDX Solutions' in one of George's chapters:
http://www.amazon.co.uk/exec/obidos/redirect?link_code=ur2&tag=chriswebbsbib-21&camp=1634&creative=6738&path=ASIN%2F0471748080%2Fqid%3D1141661836%2Fsr%3D8-3%2Fref%3Dsr_8_xs_ap_i3_xgl
</Cheeky Plug>

Chris

|||

Just placed an order for this book yesterday, so I am looking forward to reading the good arguments for the current behavior.

Why am I not using sets in the where clause for multi-select? There are more than one reason actually.

1) If a hierarchy is placed in the WHERE clause, the same hierarchy cannot - as you know - be placed on an axis (which, in many cases, in desireable).

2) The use of the WHERE clause vs. the use of subcubes for restricting the query result varies from client tool to client tool. The cube browser in SSMS, for example, uses both subcubes and sets in the WHERE clause depending on how you setup the query.

One could argue - perhaps - that the client tool should be smart enough to always place any hierarchy not already on an axis in the WHERE clause?! I wonder if this would always solve any problem? Hmm...

|||

EXISTING operator takes into account current coordinate. The whole difference between WHERE clause and subselect, is that WHERE clause sets current coordinate, while subselects merely do top level Exists with Axis (BTW WHERE clause also does it), and apply visual totals.

The confusion between WHERE and subselects is big and seem to grow every day :( It is unfortunate that not everybody realizes the semantic difference between the two and when each should be used. Perhaps we need another operator in MDX which will take into account the current restriction of subselect and/or CREATE subcube. Excel 12 is only going to add to the confusion, since it uses subselects extensively and it uses them for multiselect too, rendering my advice on how to write multiselect friendly calculations less useful...

Mosha

|||

I blogged on this topic.

http://sqljunkies.com/WebLog/reckless/archive/2006/03/08/18601.aspx

Comments are welcome...