Using MDX User-Defined Member Properties

Microsoft SSAS's user-defined member properties enable you to retrieve fine-grained property attributes (such as PII fields) without causing expensive and complex CROSSJOIN queries. For example, you can run a query that groups by Customer and also displays details like the customer’s email and mailing address, without computing dimensional crossproduct cells or subtotals for those fields.

AtScale enables you to expose your model's secondary attributes as user-defined member properties in Microsoft Excel. Excel users can then retrieve these attributes using calculations, with the .Properties MDX syntax.

Note

AtScale only supports the use of user-defined member properties with Excel and MDX. This functionality is not available to DAX or SQL users.

The following sections describe how to configure and access user-defined member properties in AtScale. For more information on member properties in general, refer to the SSAS documentation.

Configure user-defined member properties in AtScale

To configure a secondary attribute as a user-defined member property, enable the Expose as user-defined property toggle in the Secondary attribute properties panel:

You can also use the Expose as Excel Pivot Table property to determine whether the attribute can be added to Excel pivot tables:

When this toggle is enabled, users can add the attribute to pivot tables in Excel. When the toggle is disabled, users cannot add the attribute to pivot tables; however, they can still add it to reports using the Properties context menu.

Note

For attributes 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.

For more information on working with secondary attributes in AtScale, see About Dimension Attributes and Edit a Secondary Dimensional Attribute.

Access user-defined member properties

Once a secondary attribute has been marked as a user-defined member property, Excel users can retrieve it using calculations, with the .Properties MDX function.

For example, the following expression returns the [Calendar] member's Day of Year property:

[Date].[Calendar].currentMember.Properties('Day of Year')

And the following expression returns the Day of Year property for the date 2025-12-31:

[Date].[Calendar].&['2025-12-31'].Properties('Day of Year')

For more information on the .Properties function, see Properties.