Excel and MDX multiselect problems revisited

Multiselect problems ins SSAS 2005 ff come in several forms. Today, I want to take a look at several forms of multiselect problems, how they interact with Excel as a MDX client and how they can be avoided. Let's first look a problem that can be easily detected.

The current coordinate is a set

In his blog article Multiselect friendly MDX for calculations looking at current coordinate
Mosha Pasumansky explains how multiselect problems that use a set in the current coordinate occur and how they can be avoided. Let's show an example for the classical Adventure Works Cube. I use SSAS 2008 and Excel 2007. Let's define the sales amount for the year before as follows:

create member [Measures].[Prior Year Sales Amount] as ([Measures].[Sales Amount],ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember));

Let's look at a simple example in Excel 2008:






So far, it works fine, now let's filter on the dates, first on calendar year 2002.

It still works fine. Now let's filter on two months, say May and June 2002. Here's the result.
It doesn't work anymore because now, the current coordinate is a set. Here's the query:

SELECT {[Measures].[Sales Amount],[Measures].[Prior Year Sales Amount]} ON COLUMNS , NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})}) ON ROWS FROM (SELECT ({[Date].[Calendar].[Month].&[2002]&[5],[Date].[Calendar].[Month].&[2002]&[6]}) ON COLUMNS FROM [Adventure Works])

The set occurs in the subselect that Excel 2007 uses to implement the filter. If we do the same in Excel 2003, we get a similar result. it doesn't work but we get a least an error.

Now, the query uses a where clause and a calculated member to represent the set. Here's the query:

WITH MEMBER [Date].[Calendar].[XL_QZX] AS 'Aggregate({[Date].[Calendar].[Month].&[2002]&[6],[Date].[Calendar].[Month].&[2002]&[5]})' SELECT {[Measures].[Sales Amount],[Measures].[Prior Year Sales Amount]} ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Product].[Product Categories].[All Products]})})) ON ROWS FROM [Adventure Works] WHERE ([Date].[Calendar].[XL_QZX])

The error basically states the the CurrentMember function, which is used inside the ParallelPeriods function of our calculated measure [Prior Year Sales Amount] now contains a set.
A simple try of the same in Excel 2010 beta also shows that is currently does not work (even if you use the new SSAS 2008 R2 CTP).

Let's summarize: Calculations that use CurrentMember (or functions that use CurrentMember) are not multiselect-safe in Excel 2003, 2007 and ,at least up to now, in Excel 2010.

A possible workaround is described in Korrekte MDX-Berechnungen mit den Analysis Services trotz multiselect. The idea is to add a named calcuation in the data source view and to scope this calculation later on. Alternatively, you can just live with it, you just get no result or an error.

subselects and the current coordinate´problem

While the first problem can be easily detected Excel(you get no results or an error), the following problem is more subtle and cannot easily be detected. It occurs in Excel version 2007 (at up to now in 2010 beta). Let us again show an example in the adventure works cube. We define

create member [Measures].[Number of Products]

as

Count(Existing([Product].[Product].[Product].MEMBERS));

Here is a simple excel 2007 example:

Let's now filter on the products dimension.We restrict to Accessories and Bikes and also leave out the touring bikes. Here's the result:

Well nice try, but unfortunately a wrong result. The visual totals are wrong, the sum of bikes should be 103 and the total 138. Let's look at the query:

SELECT NON EMPTY
Hierarchize(DrilldownMember({{DrilldownLevel({[Product].[Product Categories].[All Products]})}}, {[Product].[Product Categories].[Category].&[1]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Product].[Product Categories].[Subcategory].[Category] ON COLUMNS
FROM
(SELECT ({[Product].[Product Categories].[Subcategory].&[2], [Product].[Product Categories].[Subcategory].&[1],
[Product].[Product Categories].[Category].&[4]}) ON COLUMNS
FROM [Adventure Works]) WHERE ([Measures].[Number of Products]
)

The filter step in Excel 2007 is implemented by a subselect. Unfortunately, the subselect does not change the current coordinate and thus the query execution context is the [All] member of the product dimension. And indead, the totals and subtotals are those for an unfiltered query.

A solution is to rely on a feature of SSAS2008, dynamic sets. The following solves the problem for SSAS 2008 and Excel 2007 and 2010.

create dynamic set Products as [Product].[Product].[Product]; create member[Measures].[Number of Products]
as count(existing(Products));
Here's the result:


Although Excel 2003 doesn't have subselect problems (it does not generate queries that use subselect), this solution does not work for Excel 2003 (the generated query does not benefit from the dynamic set, i.e., the results are still wrong.

Additionally, the problem still exists if we use different measures like Adventure Works
[Measures].[Ratio to Parent Product] because here, dynamic sets cannot help to solve the problem. Let's show this in the next example:

Now let's filter again:


Wow, amazing, all ratio to parent values are wrong, column real ratio gives you the correct values. Again, this is the effect of the subselect, that implements the filter. The current coordinate does not change, thus you see values of the form sum of filtered bikes / unfiltered parent of bikes, i.e. the 73,15% in the bikes row is equal to $80.324,94 / $109.809.274,20 (unfiltered total). The real ratios are in column "real ratio".

This also does not work in Excel 2010, but Excel 2003 computes correct values:


A solution to solve this problem in Excel 2007 and 2010 uses the same idea above, adding a hard measure (named calculation) together with scoping.

Let's summarize our results:

"The current coordinate is a set"-problem occurs in Excel versions 2003, 2007 and 2010 beta. You can either live with it (your get no result or an error) or you have to define a hard measure and scope it.

The "subselect and current coordinate"-problem occurs only in Excel versions 2007 and 2010 beta as these Excel versions rely on subselect to implement filters. Sometimes this problem can be circumvented by the use of dynamic sets (if your calculated measures relies on dynamically counting a set). In other cases, you have to define a hard measure and scope it.