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)