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

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

• Tom Kerry

  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.
  
  

• Noam37704

  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

  
  
  

• simon Hong

  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

  
  
  

• AnilK110285

  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...

  
  
  

• kingace

  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

  
  
  

• C.K.McCann

  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

  
  
  

• Semilemon

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

• Rahmat Faisal

  I blogged on this topic.

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

  Comments are welcome...

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