Overview: A bit of a cautionary tale regarding the consequences of setting a default member within a SQL Server Analysis Services attribute hierarchy. Setting this property can create the perception of problems with the cube or, at minimum, user training issues.

Purpose of a Default Member

Unless you specifically set it otherwise, the default member for any aggregatable attribute is the “All” member. Let’s say we have a small cube with 3 dimensions: Date, Cost Center, and Product. We’ll create a quick pivot table with Date on the Columns, Product on the rows, & the measure. Even though no Cost Center attributes were added, the cube actually retrieves “All” Cost Centers.

TechNet explains it perfectly:

“The default member of an attribute hierarchy is used to evaluate expressions when an attribute hierarchy is not included in a query. The default member is ignored whenever a query includes an attribute hierarchy or user hierarchy that contains the attribute that sources the attribute hierarchy. This is because the member specified in the query is used.”

As a cube designer, you might be tempted to set a default member for user convenience. For instance, perhaps the vast majority of your reports are done on a Manufacturing Cost Center, excluding Non-Manufacturing. However, it may end up being more confusing than helpful.

The General Rule: Avoid Setting a Default Member

Let’s revisit our previous example. But first, let’s presume we have set a default Cost Center of Manufacturing. We construct the same pivot table with Date on the Columns, Product on the rows, & the measure. Same as before, no Cost Center attributes are added to our pivot table. What do we get? We just get Manufacturing – not All anymore.

This might seem fine and dandy, until you have a user who forgets or doesn’t know about the default member. If forgotten, you can have a user of your BI System lose time thinking there’s a data integrity issue when there really isn’t. It’s so easy to forget because it’s not visible – the end user would think that since Cost Center is nowhere on the pivot table, why should there be a filter happening? As a designer of the cube, it makes sense. To an end user, not so much.

… An Exception to the Rule for Non-Aggregatable Attribute Hierarchies

Yeah, yeah, rules were meant to be broken. If you have an attribute which is set to be non-aggregatable, you need to set a default member so SSAS is aware of what to use. You’d set IsAggregatable = False if the aggregation of an attribute hierarchy would be a meaningless number that you want to avoid displaying. For example, you have Scenario members of Actual and Budget. Adding Actual and Budget together into an All total would be a meaningless value, so setting a default of Actual is typical. Another typical non-aggregatable situation is a parent/child dimension.

Summary

Because a default member performs its filter behind the scenes, it can risk reducing usability rather than increasing it. In most cases, leaving the All member is the best choice. If setting a default is still the best solution in your circumstance, just make sure it’s documented for the end user population.

Finding More Information

TechNet: Defining a Default Member

MSDN: SQL Server Best Practices Article (applies to SQL Server 2005 – but useful info for 2008 as well)