Using PROPERTIES to Query on Dimension, Level, and Member Properties

Within an MDX query, you can use the PROPERTIES clause to query on the following properties and retrieve their values:

standard properties of dimensions and levels:
- ID
- key
- name
member properties

A typical MDX query has the following basic syntax:

SELECT <axis_specification> [, <axis_specification>...]

FROM <cube_specification>

WHERE <slicer_specification>

An <axis_specification> value includes an optional <dim_props> value, which enables querying of dimension, level, and member properties. The breakdown of the <axis_specification> syntax with the <dim_props> value is:

<axis_specification> ::= <set> [<dim_props>] ON <axis_name>

The <set > and <axis_name> values are described in Specifying the Contents of an Axis and its subtopics. The breakdown of the <dim_props> syntax is:

<dim_props> ::= [DIMENSION] PROPERTIES <property> [,<property>...]

The breakdown of the <property> syntax varies depending on the property you are querying.

Property	Breakdown of <property> Syntax
Dimension ID	`<property> ::= <dimension_name>.ID`
Dimension key	`<property> ::= <dimension_name>.KEY`
Dimension name	`<property> ::= <dimension_name>.NAME`
Level ID	`<property> ::= [<dimension_name>.]<level_name>.ID`
Level key	`<property> ::= [<dimension_name>.]<level_name>.KEY`
Level name	`<property> ::= [<dimension_name>.]<level_name>.NAME`
Member property	`<property> ::= <level_name>.<member_property_name>`

For level properties, the dimension name is optional unless required for qualification.

Example

The following MDX query includes the PROPERTIES clause and queries a member property in the SalesCube cube described in Cube and Dataset Example. This example assumes:

A member property named Bonus_Month exists in the Quarters dimension.
The Bonus_Month member property is defined in the Months level.
The Bonus_Month member property records whether bonuses are awarded during the month because they are not awarded at a regular frequency. In months that bonuses are awarded, Bonus_Month equals 1 (True); in the remaining months, Bonus_Month equals 0 (False).
After the Bonus_Month member property was added, the Quarters dimension and then the SalesCube cube were processed.

The PROPERTIES clause is used in the y-axis (ROWS) because this axis also contains the Quarters dimension, which contains the member property.

SELECT CROSSJOIN({Smith, Jones},

{USA_North.CHILDREN, USA_South, Japan}) ON COLUMNS,

{Qtr1.CHILDREN, Qtr2, Qtr3, Qtr4.CHILDREN}

PROPERTIES Months.Bonus_Month ON ROWS

FROM SalesCube

WHERE (Sales, [1997], Products.All)