MDX中EXISTING 和 subselects 的差别

This is the first post in my blog, which will be focusing on BI-related issues on the SQL Server platform - i.e.: Analysis Services, Integration Services and Reporting Services.

This blog entry is about the use of the EXISTING operator in MDX calculations (well, really it is about one of the pitfalls of using EXISTING). The operator is new to Analysis Services 2005 and can be used to filter a set by the current coordinate in the cube. This is very useful and can for instance be used for making MDX calculations "multi-select friendly" (for more details on this, see Mosha's blog post here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx). The following MDX query (running on the Adventure Works cube database) illustrates the use of EXISTING.

WITH MEMBER [Measures].[MyDate] AS
Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue
SELECT {[Measures].[MyDate]} ON 0
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2003]

This query will return the last date in calendar year 2003, which of course is december 31 2003. It does this because the WHERE clause slices on calendar year 2003 and the last date to exist with this is december 31 2003.

What happens if we rewrite the query a bit to use a subselect to filter on calendar year 2003 instead of specifying this in the WHERE clause?

WITH MEMBER [Measures].[MyDate] AS
Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue
SELECT {[Measures].[MyDate]} ON 0
FROM 
(SELECT [Date].[Calendar Year].&[2003] ON 0
FROM [Adventure Works])

Now, I might have been the only one, but I certainly expected this query to return the same date as the first one: December 31 2003. This is not the case. Our new, rewritten, query will return August 31 2004 (which is the last available date in the date dimension in the Adventure Works cube database). What this really means is that our subselect does not set the current coordinate in the cube (in Moshas words it "merely does top level Exists with axis and applies visual totals"). This is really important to remember if you ever want to use the EXISTING operator for some fancy MDX calculations, because if your client tools use subselects (which, for example, Excel 12 does extensively I am told), you might run into some unexpected results.

Now, it is not all bad - if our query for instance sets the current coordinate on the date dimension by using one of the hierarchies from this dimension on an axis, we will get our expected result.

WITH MEMBER [Measures].[MyDate] AS
Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue
SELECT {[Measures].[MyDate]} ON 0,
[Date].[Calendar Year].[Calendar Year] ON 1
FROM 
(SELECT [Date].[Calendar Year].&[2003] ON 0
FROM [Adventure Works])