The DefaultMember property for the dimension is know by every Microsoft BI professional. This property sets the default selected member of a dimension attribute, so that member will be selected by default.
The dimension DefaultMember can be declared in BIDS or by using an Alter Cube statement as part of the cube calculations. While the first is static the latter can be used to dynamically sets a DefaultMember. The DefaultMember can also be set by defining a role, very useful for dynamic role security.
But the dimension DefaultMember property isn’t the only one. Almost every object in Analysis Service has a DefaultMember property, but in this blogpost I only look at the DefaultMember of the Measures ‘dimension’.
Determination of the DefaultMember
The big question is: how is the DefaultMember property determined when it is not set? If we take a look at the BOL there is a remark for the DefaultMember property. (Although the article is referring to Analysis Service 2005, the behavior hasn’t changed):
If DefaultMember is not specified, or is set to an empty string, then Microsoft SQL Server 2005 Analysis Services (SSAS) chooses a member to use as the default member.
But what if the default behavior of choosing the DefaultMember by Analysis Service: it chooses the first member which can be found. So if we query a DefaultMember of a hierarchy, the **All level **is returned.
And if you query an attribute with the property IsAggregatable is set to false, the FirstChild is returned, like the Scenario attribute in the AdventureWorks cube.
But there is one very important DefaultMember that helps the overall experience of Analysis Service. This is the DefaultMember of the measures.
DefaultMember of the Measures
What will a query return without a Measure added? In this case the Query Engine of Analysis Service is adding the **DefaultMember **of the Measures ‘dimension’, the DefaultMeasure. If we look at the AdventureWorks cube this value is set to the Reseller Sales Amount
Or when not set, Analysis Service will take the first Measure from the first Measure Group, in this case the Internet Sales Amount. You can manually remove the DefaultMeasure without processing the AdventureWorks cube.
But how can this behavior impact the overall experience? When we select in Excel a dimension attribute at the rows or columns, the following query is fired against Analysis Service.
This is a query without a measure included, so Analysis Service is adding the DefaultMember (DefaultMeasure) to the query as described above.
So this could result in a performance delay when this DefaultMeasure is part of a Measure Group with a lot of partitions and data. Then it can take a long time before the members of the dimension attribute is shown in Excel.
A simple way to solve this is to either set the DefaultMeasure to a measure from a small Measure Group or move that Measure Group up to be the top of the Measure Groups. In this case the measure less query will only hit the partitions of that small Measure Group.
-JP (@jppp)