Setting a default member of a hierarchy in the MDX script

Setting a default member is a tempting option which looks like it can be used on any dimension we would like to use it on. The truth is far from that. Default members should be used as exceptions and not as a general rule when designing dimensions.

The reason for that is not so obvious. The feature looks self-explanatory, and it's hard to anticipate what could go wrong. If we're not careful enough, our calculations can become unpredictable, especially on complex dimensions with many relationships among attributes.

Default members can be defined in three places. The easy-to-find option is the dimension itself, using the DefaultMember property found on every attribute. The second option is the role, on Dimension Data tab. Finally, default members can be defined in the MDX script. One of the main benefits of this place is easy maintenance of all default members in the cube because everything is in one place, and in the form of an easy-to-read text. That is also the only way to define the default member of a role-playing dimension.

In this recipe we'll show the most common option, that is, the last one, or how to set a default member of a hierarchy in the MDX script. More information on setting the DefaultMember is available at http://tinyurl.com/DefaultMember2012.

Getting ready

Follow these steps to set up the environment for this recipe:

1. Start SSMS and connect to your SSAS 2012 instance.

2. Click on the New Query button and check that the target database is Adventure Works DW 2012. Then execute the following query:

   WITH
   MEMBER [Measures].[Default account] AS
        [Account].[Accounts].DefaultMember.Name
   SELECT
      { [Measures].[Amount],
        [Measures].[Default account] } ON 0
   FROM
      [Adventure Works]

3. The results will show that the default member is Net Income account and its value in this context is a bit more than 12.6 million USD.

4. Next, open Adventure Works DW 2012 solution in SSDT.

5. Double-click on the Adventure Works cube and go to the Calculations tab. Choose Script View.

6. Position the cursor at the beginning of the script, just beneath the CALCULATE command.

How to do it…

Follow these steps to set a new default member:

1. Enter the following expression to set a new default account:

   ALTER CUBE CurrentCube 
       UPDATE DIMENSION [Account].[Accounts],
         Default_Member = [Account].[Accounts].&[48];
                          //Operating Profit

2. Save and deploy (or just press the Deploy MDX Script icon if you're using BIDS Helper 2012).

3. Run the previous query again.

4. Notice that the result has changed. The new default account is Operating Profit, the one we specified in the MDX script using ALTER CUBE command. The value changed as well – now it's above 16.7 million USD.

How it works…

The ALTER CUBE statement changes the default member of a hierarchy specified in the UPDATE DIMENSION part of the statement. The third part is where we specify which member should be the default member of that hierarchy.

Don't mind that it says UPDATE DIMENSION. SSAS 2005 interprets that as a hierarchy.

There's more…

Setting the default member on a dimension with multiple hierarchies can lead to unexpected results. Due to attribute relations, related attributes are implicitly set to corresponding members, while the non-related attributes remain on their default members, that is, the All member (also known as the root member). Certain combinations of members from all available hierarchies can result in a nonexisting coordinate. In that case, the query will return no data. Other times, the intersection will only be partial. In that case, the query will return the data, but the values will not be correct, which might be even worse than no data at all.

Enter the following expression in the MDX script, deploy it, and then analyze the result in the Cube Browser tab:

ALTER CUBE CurrentCube 
    UPDATE DIMENSION [Date].[Calendar],
      Default_Member = [Date].[Calendar]
                      .[Calendar Year].&[2007];
                       -- "current" year on the user hierarchy

The expression sets the year 2007 as the default member of the [Date].[Calendar] user-defined hierarchy.

The analysis of the Sales Amount measure in the Cube Browser shows good results in almost all cases except in a few. Fiscal hierarchies that have the fiscal year level in them return empty or incomplete results when used in a slicer. They are empty because the intersection between the fiscal year 2006 and the calendar year 2007 (the latter being the default member in the calendar hierarchy) is a nonexisting combination. Remember, the calendar year 2007 doesn't get overwritten by the fiscal year 2006. It gets combined (open the Date dimension in SSDT and observe the relationships in the corresponding tab). Moreover, when you put the fiscal year 2007 into the slicer, you only get a portion of data, the portion which matches the intersection of the calendar and the fiscal year. That's only one half of the fiscal year, right? In short, you have a potential problem with this approach.

Can we fix the result? Yes, we can. The correct results will be there when we explicitly select the All member from the Date.Calendar hierarchy in the slicer. Only then will we get good results using fiscal hierarchies. The question is – will the end users remember that every time?

The situation is similar when the default member is defined on an attribute hierarchy, for example, on the Date.Calendar Year hierarchy. By now, you should be able to modify the previous expression so that it sets the year 2007 as the default member on the [Date].[Calendar Year]. Test this to see it for yourself.

Another scenario could be that you want to put the current date as the default member on the Date.Date hierarchy. Try that too, and see that when you use the year 2006 from the Date.Calendar Year hierarchy in the slicer, you get an empty result. Again, the intersection formed a nonexisting coordinate.

To conclude, you should avoid defining default members on complex dimensions. Define them where it is appropriate: on dimensions with a single non-aggregatable attribute (that is, when you set the IsAggregatable property of an attribute to False) or on dimensions with one or more user hierarchies where that non-aggregatable attribute is the top level on each user hierarchy, and where all relationships are well defined.

The Account dimension used in this example is not such a dimension. In order to correct it, two visible attributes should be hidden because they can cause empty results when used in a slicer. Experimenting with a scope might help too, but that adds to the complexity of the solution and hence the initial advice of keeping things simple when using default members should prevail.

Take a look at other dimensions in the Adventure Works DW 2012 database. There you will find good examples of using default members.

Helpful tips

When you're defining the default members in an MDX script, do it at the beginning of the script. This way the calculations that follow can reference them.

In addition, provide a comment explaining which member was chosen to be the default member, and perhaps why. Look back at the code in this recipe to see how it was done.