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. 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 is hard to anticipate what could go wrong. If we are 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 the 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 easy-to-read text. That is also the only way to define the default member of a role-playing dimension.

In this recipe, we will 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 2016 instance.
2. Click on the New Query button and check that the target database is Adventure Works DW 2016. 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 the Net Income account and its value in this context is a bit more than 12.6 million USD.
4. Next, open Adventure Works DW 2016 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 or 2016 Preview version).
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 the 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 2016 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 non-existing 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, then deploy it:

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

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

Let's analyze the result with the following query:

SELECT  
  [Measures].[Sales Amount] ON 0, 
  [Date].[Fiscal].[Fiscal Year] ON 1 
FROM 
  [Adventure Works] 

The result is shown in the following screenshot:

The analysis of the Sales Amount measure for each fiscal year returns empty results except in FY 2011 and FY 2012. They are empty because the intersection between the fiscal year and the calendar year 2012 (the latter being the default member in the calendar hierarchy) is a non-existing combination, except FY 2011 and FY 2012. Remember, the calendar year 2012 doesn't get overwritten by the fiscal year 2011 or 2012. It gets combined (open the Date dimension in SSDT and observe the relationships in the corresponding tab). Moreover, when you put the fiscal year 2011 or 2012 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. The complete MDX is shown in the following query. Only then will we get correct results using fiscal hierarchies. The question is—will the end users remember that every time?

SELECT [Measures].[Sales Amount] ON 0, 
    [Date].[Calendar Year].[All] * 
    [Date].[Fiscal].[Fiscal Year] ON 1 
FROM 
  [Adventure Works] 

The correct results from this query can be seen in the following screenshot:

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 2012 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 2012 from the [Date].[Calendar Year] hierarchy in the slicer, you get an empty result. Again, the intersection formed a non-existing 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 2016 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.