/ Analysis

The beauty of the #SSAS DefaultMember

imageThe 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.image

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.

image

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.

image

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

image image

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.

image image

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.

image

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.

image

-JP (@jppp)

Jan Pieter Posthuma

Jan Pieter Posthuma

I am Jan Pieter Posthuma and I am a Microsoft Data Consultant working for DataScenarios. With the current changes I found it time to share my thoughts and ideas with the community.

Read More