Archive

Archive for June, 2011

Default Member Trap (be careful of what you filter)

June 5, 2011 3 comments

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.

 

Categories: SSAS
Follow

Get every new post delivered to your Inbox.

Join 228 other followers