About Default Hierarchical Members

You can specify a default member of one or more dimensional hierarchies in a cube to serve together as a default filter in MDX queries on that cube.

For example, suppose that your cube has a Date dimension that contains a hierarchy named Date Month. A fact dataset in the cube contains a measure named freight. You set the default member of the Date Month hierarchy to be the year 2016. After you publish the project that contains the cube, a BI client issues a query for a sum of freight without specifying any date criteria. The query results would be the sum of freight for the year 2016.

If a query specifies a level in a hierarchy that has a default member, the default is not used. For example, if the query in the previous example were given a WHERE clause that specified the date criteria as Q1 of the year 2017, the query would return the sum of freight for Q1 of 2017, not for all of the year 2016.

Moreover, default hierarchy members are not used in queries that populate select fields and filter dialogs in BI client software, such as Microsoft Excel and Tableau Desktop. Here is an example of such a filter dialog in Tableau Desktop:

Figure 1. The filter dialog that appears in Tableau Desktop when the City level of the Geography City hierarchy is dragged into the Filters area

/public/images/def_dim_member_selector.png

Consider that you cannot specify secondary attributes as default dimension members. For instance, in the Date Month Hierarchy dimension below, Quarter Number and Month Abbreviation are both secondary attributes.

Figure 2. Secondary attributes in the Date Month hierarchy

/public/images/def_dim_member_secondary_attribute_updated.png

Specifying a value for Quarter Number as the default member for this dimension is not valid and will cause queries to fail.

Specifying a Default Member of a Hierarchy

After creating a hierarchy in a dimension, you can edit the hierarchy to specify the default member to use. You use MDX syntax to do so.

In the cube canvas, double-click the name of the hierarchy to open the Edit a Hierarchy dialog.
Under the Data Handling section of the dialog, turn on the Use default member literal expression toggle.
In the Default Member Expression (MDX) field that appears, specify the default member using MDX syntax. In the MDX expression, you must include the same query name used for the hierarchy.
Click Save.
Publish the project that contains the cube in order to use the default member you've created. After publishing, test the default member in your BI client.

Below is an example of role-playing relationships and default hierarchical members, using the hierarchy Order Date Month. Here, you must include the name of the role-playing relationships, Order.

Figure 3. A role-played Date dimension with the Order Date Month hierarchy

/public/images/date_dim_roleplay_hierarchy.png

You want to set a default for the Order Date Month hierarchy, and you want to set that default to August in Q3 of the year 2005.

You double-click the name of the hierarchy to open the Edit a Hierarchy dialog. In this dialog, there is a section labeled Data Handling.

Figure 4. The default member option and field (highlighted) of the Edit a Hierarchy dialog

You turn on the Use default member literal expression option and, in the text field that appears below it, type the following MDX expression:

[Order Date Dimension].[Order Date Month Hierarchy].[Order YearMonth].[2005].[2005-Q3].[2005-08]

Notice that the role-playing relationship name is specified (Order), the query name is specified, and the values for the year, quarter, and month are actual values from the role-played dimension. If you have more than one role-playing relationship between a fact dataset and a dimension level that has a default member, you must use the name of one of the role-playing relationships in the MDX expression for the default member. A default dimension member can be used for only one role-playing relationship at a time.

You can then click Save to close the dialog. The Design Center does not validate that the expression is correct syntactically or that it uses a valid value for the member. You must publish the project that contains the cube and then test the default member.

Examples of Alternative MDX Expressions for Setting a Default Member in the Date Month Hierarchy

When you specify an MDX expression, you give values from the top level of the hierarchy all the way down to the level at which you want to set the default.

Set the default member to September in Q4 of the year 2005.

[Order Date Dimension].[Order Date Month Hierarchy].[Order YearMonth].[2005].[2005-Q3].[2005-09]

Set the default member to February in Q1 of the year 2008.

[Order Date Dimension].[Order Date Month Hierarchy].[Order YearMonth].&[2008].&[2008]&[1].&[2008]&[200802]

Set the default member to the year 2008.

[Order Date Dimension].[Order Date Month Hierarchy].[Order YearMonth].[2008]

Set the default member to the "Day" level's member with key 20080102 using AtScale's "Level Shortcut Syntax"
```
[Order Date Dimension].[Order Date Month Hierarchy].[Day].&[20080102]
```

Examples of MDX Expressions for Setting a Dynamic Default Member on a Time Hierarchy

In addition to static expressions like the ones listed above, you can also create default members on time hierarchies using dynamic expressions. This enables you to create default members that update automatically over time or in response to ETL triggers. You can use dynamic default members to reduce query sizes and provide more relevant query results without having to add manual restrictions to your models.

Note: Dynamic default members are only supported on time hierarchies.

You create dynamic default members by including VBA date and timestamp functions in your MDX expressions. For the full list of supported functions, see VBA Date Functions.

The following are examples of dynamic expressions:

Set the default member to the current date for an integer date key:

[DateCustom].[StandardMonth].[Date].&[`CLONG(Format(Now(), "yyyymmdd"))`]

Set the default member to the current month for a date key:

[DateCustom].[StandardMonth].[Month].&[`MonthStartDate(Now())`]

Set the default member to the current year for a date key:

[DateCustom].[StandardMonth].[Year].&[`YearStartDate(Now())`]

Set the default member to a date offset by 17 years:

[DateCustom].[StandardMonth].[Year].&[`DateAdd("yyyy", -17, YearStartDate(Now()))`]

Set the default member to a date offset by 17 years (alternative):
```
[DateCustom].[TestCalendar].[YearLevel].&[`Year(Now())-17`]
```

Set the default member to the high-water mark from a table:

[DateCustom].[StandardMonth].[Date].&[`selectMaxDate("adventureworks2012.FactInternetSales", "OrderDateKey")`]

When Multiple Hierarchies are in a Dimension

The below image is a dimension with two hierarchies. If an inbound query queries the Date Week Hierarchy, but you have a default member set to 2016 on the Date Month Hierarchy, then the query will be filtered to only return results from 2016. Be aware that setting default dimension members on dimensions with multiple hierarchies can produce unexpected results because it is easy to forget about the default member filtering on another hierarchy.

Figure 5. The Date dimension and its two hierarchies in the sample cube Internet Sales Cube

/public/images/date_dim_two_hierarchy.png

Testing Default Hierarchical Members

When you click Save to close the Edit a Hierarchy dialog, the Design Center does not validate that the expression is correct syntactically or that it uses a valid value for the member. You must publish the project that contains the cube and then run a query on a measure in a fact dataset, checking the outbound query (the one that AtScale generates from the client's MDX query) to see whether the default was used.

To test the member after publishing the project:

In Design Center, find your published project and go to the cube's overview page.
On the cube overview page, select the Connect tab and copy the MDX string listed in the Connection Information table for MDX.
In your BI client, connect to the cube by using Microsoft Analysis Services (MAS) and the MDX connection URL. To use MAS, your BI client must be running on Windows.
Issue a query against a measure in a fact dataset.
On the Design Center Queries page, find your MDX query in the Filters tray by searching by user, project, cube and time.
Once you've found the inbound MDX query, examine the outbound query (the query that the AtScale engine generated based on the query you issued) to see whether the default member was used.

The AtScale engine fails queries if either of these conditions is true:

More than one default member is set for a single hierarchy. Though it is not possible to set two default members in the Edit a Hierarchy dialog for a particular hierarchy, it is possible to use one literal expression for two different hierarchies accidentally.
The literal expression for a default dimension member specifies a secondary attribute.