Home > SQL Analysis Services > Excel 2007 subcubes/subselects Issue

Excel 2007 subcubes/subselects Issue

I had problems with different results generated by Excel 2007 and BIDS. Michael Barrett well explained the reason in its blog. The below is his original post at
http://barrettbi.spaces.live.com/

MDX: EXISTING operator and sub-selects

by Michael Barrett

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])

UPDATE 20060610: The problems with subselects mentioned above are
not limited to the use of the EXISTING operator. They apply in all
situations where the current context is needed in an MDX expression in
order to return a correct result.

Advertisements
Categories: SQL Analysis Services
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: