Search This Blog

Sunday, February 14, 2010

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

with

MEMBER CountDays_TooSimple
AS DrillDownLevel([Date].[Calendar].[Date], [Date].[Calendar].[Date]).count

MEMBER CountDays_Using_Existing
AS DrillDownLevel(existing [Date].[Calendar].[Date], [Date].[Calendar].[Date]).count

select {CountDays_TooSimple,CountDays_Using_Existing} on 0

from [Adventure Works]

where [Date].[Calendar].[Calendar Year].&[2003]

The output looks like this:

image_thumb9

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:


where {[Date].[Calendar].[Month].&[2003]&[1],[Date].[Calendar].[Month].&[2003]&[2] }

image_thumb8

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:

with

MEMBER CountDays_TooSimple
AS DrillDownLevel([Date].[Calendar].[Date], [Date].[Calendar].[Date]).count

MEMBER CountDays_Using_Existing
AS DrillDownLevel(existing [Date].[Calendar].[Date], [Date].[Calendar].[Date]).count

select {CountDays_TooSimple,CountDays_Using_Existing} on 0

from (
select {[Date].[Calendar].[Month].&[2003]&[1],[Date].[Calendar].[Month].&[2003]&[2] } on 0
from [Adventure Works]
)

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

image_thumb7

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:

with

DYNAMIC SET [CountDaysDynaSetDays] AS [Date].[Calendar].[Date]

MEMBER CountDays_Dynamic
AS DrillDownLevel(CountDaysDynaSetDays, [Date].[Calendar].[Date]).count

select {CountDays_Dynamic} on 0

from (
select {[Date].[Calendar].[Month].&[2003]&[1],[Date].[Calendar].[Month].&[2003]&[2] } on 0
from [Adventure Works]
)

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:

image_thumb6

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:

CREATE MEMBER CURRENTCUBE.CountDays_TooSimple
AS DrillDownLevel([Date].[Calendar].[Date], [Date].[Calendar].[Date]).count ,
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.CountDays_Using_Existing
AS DrillDownLevel(existing [Date].[Calendar].[Date], [Date].[Calendar].[Date]).count ,
VISIBLE = 1 ;

CREATE DYNAMIC SET CURRENTCUBE.[CountDaysDynaSetDays] AS [Date].[Calendar].[Date] ;
CREATE MEMBER CURRENTCUBE.CountDays_Dynamic
AS DrillDownLevel(CountDaysDynaSetDays, [Date].[Calendar].[Date]).count ,
VISIBLE = 1 ;

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:

image_thumb11

Now let’s open Excel and try there:

image_thumb13

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

7 comments:

  1. Hello

    Your example is very good - in fact it is the best result afer searching one week. The shown numbers depending on the filter - that's true, but not on the level. If you go down one level the figure stays the same.

    Would you be so kind to publish an example which regognises the levels as well?
    I'm new to MS-olap but in IBM Cognos it is called "category count". This measure type always counts the included number of unique members at a certain level aggregates them over them.

    If you have a dimension of an organizational structure down to the user id you may want to have the number of employees (sum(count(user id)) on each level with the aggregation rule sum. This measure enables you to make other calculations like avg turnover per user.

    Thank you.

    ReplyDelete
  2. The number should also be correct on the levels as filters and axis are both changing the context of the calculation. I have another example posted in a later blog post that also uses this technique and shows values on different levels of the hierarchy. Hope this helps. You can find the other post here:
    http://ms-olap.blogspot.com/2010/05/analyzing-number-of-visits-per-customer.html

    ReplyDelete
  3. Hello,

    Thanks for your post. But the solution works only, when i am using one dimension for filtering. Can you please give me a hint what i can do when i want to filter on more than one dimension?

    Thanks!

    ReplyDelete
  4. Does it also work when you select date values in multiple years? It doesn't seemt to work here, but otherwise the dynamic set trick is the solution. Thanks for a great blog post.

    ReplyDelete
  5. The solution with the dynamic set should also work if dates from multiple years are selected (even if you selected elements on different levels of the date hierarchy - I just checked this). Maybe there is another cause here (e.g. attribute relationship of the time dimension).

    You can also extend this method on multiple dimension hierarchies but using one dynamic set for each hierarchy in the filter.

    ReplyDelete
  6. Yes you are correct that it works if I don't use a hierarchy. So it must be related to the hierarchy somehow. Thanks!

    ReplyDelete
  7. Thank you so much!!

    ReplyDelete