Default Member inconsistency

Posted bymartinistiPosted inSSASTags:,

Issue

Example is based on Adventure Works – SQL 2012

The user would like to browse the cube mainly for data related to the Accessories products so the default member of that attribute is set to:

[Product].[Category].&[4]

You have to make that modification in Adventure Works in order to test this behaviour!

image

But as the user digs deeper he’s interested in other statuses like Bikes for Calendar Year 2004. The result should be like this:

Query for this from profiler:

SELECT 
  NON EMPTY 
    Hierarchize
    (
      {
        DrillDownLevel({[Product].[Product Categories].[All Products]}
        ,,,INCLUDE_CALC_MEMBERS)
      }
    )
   ON COLUMNS
FROM 
(
  SELECT 
    {
      [Product].[Product Categories].[Category].&[1]
     ,[Product].[Product Categories].[Category].&[4]
    } ON COLUMNS
  FROM [Adventure Works]
)
WHERE 
  (
    [Date].[Calendar].[Calendar Year].&[2004]
   ,[Measures].[Order Quantity]
  )

So far it looks OK because if the Product Category is not in the filter but displayed on rows/columns the values are not summed up but displayed individually. Let’s try putting it from Row Labels to Filters:

image

Again the query in the background:

SELECT 
FROM 
(
  SELECT 
    {
      [Product].[Product Categories].[Category].&[1]
     ,[Product].[Product Categories].[Category].&[4]
    } ON COLUMNS
  FROM [Adventure Works]
)
WHERE 
  (
    [Date].[Calendar].[Calendar Year].&[2004]
   ,[Measures].[Order Quantity]
  )

So Excel (and not just Excel, other cube browsers can also do this, though I haven’t tested) mixes it up. The same problem is highlighted very clearly in this blog post, I just provide Adventure Works example and some more explanation: http://peteadshead.wordpress.com/2011/08/27/issues-with-msas-default-members/

Solution Explanation

No real solution for that one just workarounds like providing an initial spreadsheet for the user that has the Product Category set to Accessories. The problem lies in how Excel (and as I mentioned possibly other query tools, too) constructs the query (you can catch them via Profiler) because it embeds
filters in subqueries instead of WHERE clauses.

So here’s the incorrect, Excel-created MDX once more with some bold-highlighting:

--Incorrect results
SELECT 
  { [Measures].[Order Quantity] } ON COLUMNS
FROM 
(
  SELECT 
    { [Product].[Category].&[4], [Product].[Category].&[1] } ON COLUMNS
  FROM [Adventure Works]
)
WHERE 
  ( [Date].[Calendar].[Calendar Year].&[2004] )

The result is 27 560 but should be 48 713 as you can check with the next query by yourself. The only thing happens is that I move the filtering from a subquery into the WHERE clause:

--Correct results 
SELECT 
  {[Measures].[Order Quantity]} ON COLUMNS
FROM [Adventure Works]
WHERE 
  (
    [Date].[Calendar].[Calendar Year].&[2004]
   ,{ [Product].[Category].&[4], [Product].[Category].&[1] }
  );

So basically it’s not (or not just) Excel’s fault that the MDX query returns an incorrect value but the cube engine or MDX itself has a flaw, but it comes to daylight when using Excel due to its query writing mechanism.

Further details as a result of using team knowledge J If you duplicate and put the filter into the WHERE part, too then the result is OK:

SELECT 
  { [Measures].[Order Quantity] } ON COLUMNS
FROM 
(
  SELECT 
    { [Product].[Category].&[4], [Product].[Category].&[1] } ON COLUMNS
  FROM [Adventure Works]
)
WHERE 
  ( [Date].[Calendar].[Calendar Year].&[2004] 
  ,{[Product].[Category].&[4], [Product].[Category].&[1]}
  );

So it seems there are 3 things happen when executing the first, incorrect initial query:

  1. The subquery is evaluated with all the listed statuses, overriding the default value (so Accessories and Bikes are selected by overriding the default which would have been only Accessories)
  2. The outer query is evaluated as it should
  3. The filtering to default member is applied again

Steps 2 and 3 can be replaced, the end result is the same. When everything is evaluated in only 1 query the default member is correctly overridden.

There’s something similar to MDX and SSAS as the logical query processing phases for SQL but it’s a lot more complicated. According to SSAS Unleashed:

“The default members are overwritten by the members of attributes specified in the WHERE clause, and then those members are overwritten by members of attributes from each axis. When we say overwritten, we mean that the member corresponding to the attribute is changed. In reality, the process is a little more complicated…” – page 175

And it might have something related to the scopes but this is beyond my present knowledge.

There’s a technet article (http://technet.microsoft.com/en-us/library/bb630310.aspx) about default members but though it gives some advice what to be aware of when defining default members it is not valid for this issue! (I wanted to mention because you might find it and suggest considering those ideas.) That refers to non-aggregatable attributes wheres Status is aggregatable.

Leave a comment