THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

DefaultMember, subcubes and non-aggregatable attributes

Today I discovered that DefaultMember might result in a member other thant the default dimension member.

Reading documentation, I got the idea that DefaultMember would be always the default member defined for a dimension into a cube. In reality, the default member could change into a subcube that excludes the original default member from a calculation. This is important because writing MDX Scripts you cannot anticipate any possible use of your cube... and you should carefully consider when DefaultMember is the real appropriate syntax on a case-by-case basis. You have to be particularly careful whenever you use DefaultMember on a non-aggregatable attribute.

Let's look an example.

In Adventure Works you can write the following query.

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM [Adventure Works]

 

This query provides this result:

image

 

I'm not interested in the real meaning of this query. This is only an excuse to see the DefaultMember in action against a non-aggregatable attribute. The DiffActual calculated member has a formula that use Scenario.Scenario.DefaultMember. The default member for Scenario.Scenario attribute is Actual. The DiffActual row shows the difference between the scenario in column and the Actual scenario.

Now, if we encapsulate the previous query into a subquery that apparently should return the same members, we can write this query.

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM 
  (SELECT 
    { [Scenario].[Scenario].[Forecast], 
      [Scenario].[Scenario].[Budget] } ON COLUMNS
FROM [Adventure Works])

 

Before executing this query, what is the expected result? First time I thought "it should be the same", but in reality it is different, like shown here:

image

What happened? Simply, the SSAS calculation engine needs a default member included in the "context" - when you use a subquery, you are defining a subset of the cube space that must have an existing default member for each attribute - if a default member is outside of the subcube, it is replaced by another member of the same attribute.

In the case we are exmining, the default member of the subcube becomes Forecast. This default member depends on the order of members of the same attribute used in the subcube. If we invert Forecast and Budget members only in the subcube, the default member becomes Forecast instead of Budget, changing the calculated member result (based on DefaultMember syntax).

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM 
  (SELECT 
    { [Scenario].[Scenario].[Budget], 
      [Scenario].[Scenario].[Forecast] } ON COLUMNS
FROM [Adventure Works])

 

This is the result for the query above:

image

To avoid this issue you can use a specific member instead of the keyword DefaultMember. In our example, the solution is:

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.[Actual], Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM [Adventure Works]

 

With this syntax, any use of this query as a subcube in another query will not have the side effects we have seen before.

Why this is so important? Simply because Excel 2007, often used as a client by end users, makes heavy use of subqueries in MDX generated by PivotTable. I had to substitute all DefaultMember in MDX Scripts with well-known member names (this also affects my DateTool dimension, which I will update soon with this and other improvements).

Final note: a special thanks to Mosha Pasumansky for illuminating me on hidden secrets of DefaultMember. Mosha also said that using the member name of default member (instead of DefaultMember keyword) improves query performance.

Published Tuesday, October 02, 2007 12:35 AM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ramunas Balukonis said:

Marco,

As Excel 2007 generates MDX using subqueries, there is no way to avoid errors. Please refer to my example http://ssas-info.com/RamunasBalukonisBlog/?p=14 . I registered incident to Microsoft support, but they doesn't recognize this as error. No workaround yet provided.

November 5, 2007 3:05 AM
 

Marco Russo (SQLBI) said:

Ramunas, thank for your feedback!

This is the second time that Excel 2007 and SSAS team refuse to get responsibility for an issue that involves a combination of factors. This is really disappointing.

Marco

November 5, 2007 3:16 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement