Solution von SSAS 2008 multi-selects in Excel (Dynamic Sets)

SQL Server 2008

Being able to select multiple dimension elements in an MDX client requires some care in designing calculations for the cube. However, with SQL Server 2005 it was really difficult to design calculations in a way that work fine with Excel 2007. The reason for this is that Excel 2007 uses sub cubes for filtering and sets in SQL Server 2005 did not reflect sub cubes. So this article is about multi-select friendly queries in SSAS 2008 using dynamic sets.

There have been some posts about the problems around multi-selects. Of course you cannot use a currentmember reference in your calculation (if a set is in the where condition, there is no single current member) but use sets in most cases. However, also sets do not react on where conditions in an MDX statement by default. You have to add the EXISTING keyword to get the desired result. In order to illustrate this, we’ll start with a very simple calculated measure. Our measure should just return the number of days being selected as the filter for our date dimension. I’ll use the good old Adventure Works example database for my tests here.

The output looks like this:

As you can clearly see, the expression set expression without the EXISTING keyword (used for the measure CountDays_TooSimple) did not react on the filter (resulting in all 1188 days that are stored in the Adventure Works database), while the calculation with EXISTING (used in the measure CountDays_Using_Existing) did. Ok, this has nothing to do multi selects (as we only selected a single year) but it illustrated how we could use EXISTING to adjust sets to the given scope. The behavior is basically the same with multi selects. So, if we change the where condition to January and February we also get the right result for our measure that uses the EXISTING-method:

However, and this is where things are getting complicated, our measures still depend on the way how we implemented our filter. If we turn the filter into a sub cube, then the result looks different:

Now, even our calculation that used the EXISTING keyword to filter the set does not work anymore:

Since Excel 2007 uses sub cubes to filter queries, this behavior is exactly how Excel would return this calculation (see below for the complete example in Excel).

So, how can this be solved? In SQL Server 2008 there is a new feature, called dynamic named sets (or dynamic sets). The good thing about dynamic sets is, that they also react on sub cubes filters. So let’s look at the following calculation of the same measure:

At first glance, nothing has really changed here. We simple declare the reference to our set (level here) [Date].[Calendar].[Date] as a dynamic set and used the set name in the calculation instead. For the result, I also included the two other measures from above in order to show the difference:

This shows how dynamic sets can be used to react on multiple selects provided as a sub cube. I didn’t show it here, but our calculation using the dynamic sets (CountDays_Dynamic) also works fine with a simple where clause.

In order to check if this calculation also works in Excel, let’s transfer all the measures above to a cube calculation. We simply add them at the end of the Adventure Works’s cube script:

For the cube browser, everything looks as expected. For the following screenshot I also selected January and February 2003. Please note, that also the calculation using the EXISTING keyword works fine here:

Now let’s open Excel and try there:

As Excel uses a sub cube method, our second measure (CountDays_Using_Existing) fails here (giving the total number of days). But the calculation using the dynamic set still works fine in all scenarios.

So, if you’re facing problems with multi selects you should also think of dynamic sets as one possible option to circumvent the problems.

Also, dynamic named sets can also be a solution for performance optimizations (see Mosha’s blog for example).