Add a Level Alias

Level aliases enable the creation of tabular reports that select hierarchical expressions without forcing the user to drill down a hierarchy. For more information on how level aliases work, see About Dimension Attributes.

To add a level alias to a level:

1. In Design Center, open the dimension you want to add a level alias to for editing. The Dimension properties panel opens.

2. In the Hierarchies section, click the context menu for the level you want to add a level alias to and select Create Level Alias.

   

   The Level alias properties panel opens.

   

3. Complete the following fields:

   • Display name: The display name for the level alias. This is the name of the level alias that will appear in BI tools. This value does not need to be unique.

   • Unique name: The unique name for the level alias. This value must be unique within the dimension.

     Note

     You should not change this value after the model has been published. Doing so may break existing data sources and reports that rely on this model.

   • Dataset: The source dataset that contains the column the level alias is based on. This defaults to the dataset for the parent level and cannot be changed.

   • Name Column: The column in the Dataset that the level alias is based on.

   • Sort Column: By default, sorts are performed on the Name Column. If you want to sort on a different column, select it here.

4. In the Data Handling section, enable/disable the following options as needed:

   • Exclude from System-Generated Dimension-Only Aggregates: Excludes the level alias from system-generated dimension-only aggregates. This is useful if the alias contains a large number (millions) of distinct values that you don't want to aggregate.
   • Exclude from System-Generated Fact-Based Aggregates: Excludes the level alias from system-generated fact-based aggregates. This is useful if the alias contains a large number (millions) of distinct values that you don't want to aggregate.
   • Custom Empty Member: Enable this option to ensure that un-joinable values (for example, NULL) that may reside in the foreign key columns of the fact dataset are included in your query results. The Custom Empty Member feature provides the convenience of handling NULL values so data architects are not forced to fix their fact tables. For maximum compatibility with BI tools, you will have to add an empty member record to the dimension dataset. To learn more about Custom Empty Members, see Using Custom Empty Members for Levels and Attributes.

5. In the Visualization section, update the following fields as needed:

   • Visualize in BI tool?: Determines whether the level alias appears in BI tools.

   • Expose as Excel Pivot Table property: Determines whether the level alias appears in Microsoft Excel pivot tables.

     When enabled, users can add the alias to pivot tables in Excel. When disabled, users cannot add the alias to pivot tables; however, they can still add it to reports using the Properties context menu. For more information, see Using MDX User-Defined Member Properties.

     Note

     For aliases that do not have this setting configured, it defaults to enabled when the model is deployed. You can control this behavior with the schema.userDefinedMember.defaultExcelPivotTableProperty global and model settings. For more information, see Schema Settings and Other Model Settings.

   • Expose as user defined property: Determines whether the level alias is exposed to Excel and MDX users as a user-defined member property. For more information, see Using MDX User-Defined Member Properties.

   • Folder: Defines the folder in which the level alias appears in BI tools.

6. Click Apply in the Level alias properties panel.

7. Click Apply in the Dimension properties panel.

The level alias is added to the level and appears in the Model properties panel: