Default Member Trap (be careful of what you filter)


Default members are a great way to customise user visibility without limiting cube access through some other security restriction. However, there is an interesting issue around the use of default members and analysis in excel. When a default value exists, and the level is used as a filter in excel, the data displayed is limited to the default member and not the filter in the pivot.

When there is no default member, and we browse [Sales Territory].[Sales Territory Country] x [Calendar Year] the filter applied to [Sales Territory Country] limits the data shown.

However, when the dimension has a default member (in the shots below the default member is Australia) the result data only shows for Australia and not the two selected members (which were Australia and Canada). I have chosen to show rows with no data so that the views are consistent but we can see that the [Reseller Sales Amount] has dropped to $847,430 (the value of Australia).

This is caused by way that the filter generates the sub cube in the request. The mdx generated by this pivot is shown below and creates the sub cube on the filtered members (Australia & Canada). However, when the ‘outer’ statement is executed against the sub cube, the default member is applied and this restricts the data to Australia.

SELECT
Hierarchize

{DrilldownLevel({[Date].[Calendar Year].[All Periods]} , , , INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM
(
   SELECT ({[Sales Territory].[Sales Territory Country].&[Canada]
           ,[Sales Territory].[Sales Territory Country].&[Australia]})
           ON COLUMNS
    FROM [Adventure Works]
)
WHERE     ([Measures].[Reseller Sales Amount])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

For attributes that are non aggregatable (for example a version or scenario), there exists a very valid case to use default members (since actual + budget just dosent make sense and should not be displayed to the user). When the dimension is more descriptive (as is the case with a [Country]), aggregation is a usual request and may issue incorrect results to users.

 

Advertisements

14 thoughts on “Default Member Trap (be careful of what you filter)

  1. Pingback: Dangers in Dashboard Design | The Degenerate Dimension

  2. If user requirement is make it default member at the same time he wanted to aggregate data based on selected filter, what is the solution?

      • Thanks Paul, we have requirement to have it :(. Can we do anything with SSAS StoredProcedures etc, to dynamically change default hierarchy member.?

      • Hi Rajendra,

        I think we may be missing communicating here. You can have a default member and it can be dynamic on a user/role basis. This would be done in the same manner as dynamic security. That’s no problem and may suite your needs. However, the issue in this post relates to the use of default members and the generation of subcubes (which is how excel does its filtering). The subcube filters the cubes data and restricts on the basis of the default member. However, that restriction is not applied/shown to the filters in excel. That may be ok if the user doesn’t change the value (and know its default).

        I hope this makes sense?

  3. Hi Paul,

    I got you point :). I have not clearly expalined the requirement in per my previous post. Exactly as in this article, User wants to make default member of a dimension hierarchy. when user drags hierarchy in excel pivot, aggreated value default to this dimension default member. after that even user selected other level member along with this default member, grand total sum has not been changed, it is only showing default member aggregated value.. may be no resolution for this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s