Skip to main content

About Default Hierarchical Members

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

For example, suppose that your model has a Date dimension that contains a hierarchy named Date Month. A fact dataset in the model 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 model, 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

image

note

Note: You cannot specify secondary attributes as default dimension members. Doing so 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.

  1. In Design Center, open a dimension, then click its context menu and select Edit. The Edit Dimension panel appears.
  2. In the Hierarchies section, click the context menu for the hierarchy you want to set as a default member, and select Edit. The Edit Hierarchy panel opens.
  3. In the Data Handling section, enable the Use default member literal expression option.
  4. In the text 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.
  5. Click Apply.
  6. Deploy the project that contains the model in order to use the default member you've created. After publishing, test the default member in your BI client.

Example​

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 2. A role-played Date dimension with the Order Date Month hierarchy

image

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 open the hierarchy for editing, enable the Use default member literal expression option and, in the text field that appears below it, enter 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 Apply. The Design Center does not validate that the expression is correct syntactically or that it uses a valid value for the member. You must deploy the project that contains the model 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

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 image below shows 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 is 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 3. The Date dimension and its two hierarchies

image

Testing Default Hierarchical Members​

When editing a hierarchy, Design Center does not validate that the expression is correct syntactically or that it uses a valid value for the member. You must deploy the project that contains the model 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 deploying the project:

  1. In Design Center, open the Deployed Catalogs panel, then open your deployed catalog.
  2. Switch to the Connect tab and copy the MDX + Token string.
  3. In your BI client, connect to the model by using Microsoft Analysis Services (MAS) and the MDX connection URL. To use MAS, your BI client must be running on Windows.
  4. Issue a query against a measure in a fact dataset.
  5. In Design Center, open the main menu and select Queries. The Queries page opens.
  6. Locate your MDX query by searching by user, catalog/model, and time.
  7. 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.

Queries fail 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 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.