In the axis specification for a given axis, the set expression selects tuples to populate the axis. The dataset returns some basic information about each member in each tuple, such as the member name, parent level, the number of children, and so on. These are referred to as member properties. Members often have additional properties associated with them, and member properties are available for all members at a given level. In terms of organization, member properties are treated as dimensionally organized data, stored on a single dimension.
For example, the Products level may offer the SKU, SRP, Weight, and Volume properties for each product. These properties are not members, but contain additional information about members at the Products level. All members support intrinsic member properties, such as the formatted value of a member, while dimensions and levels supply additional intrinsic dimension and level member properties, such as the ID of a member. Additional member properties can be created in Analysis Manager using Dimension Editor or Cube Editor, or with Multidimensional Expressions (MDX) statements. Member properties can be retrieved through the use of the DIMENSION PROPERTIES keyword or the Properties function.
DIMENSION PROPERTIES Keyword
An application might want to extend member information by adding member properties on the axis. Therefore, each level of each dimension may contain a set of available properties for the members.
The DIMENSION PROPERTIES keyword is used to specify member properties to be used for a given axis dimension. The following syntax defines the MDX SELECT syntax, adding the syntax for the DIMENSION PROPERTIES keyword:
SELECT [
[, ...]]
FROM []
[WHERE []]
The
::= [] ON
The
::= [DIMENSION] PROPERTIES [,...]
The breakdown of the
Additional member properties can be selected by using the DIMENSION PROPERTIES keyword after the set expression of the axis specification. For example, the following MDX query:
SELECT
CROSSJOIN(Years, (Sales, BudgetedSales)) ON COLUMNS,
NON EMPTY Products.MEMBERS
DIMENSION PROPERTIES Products.SKU, Products.SRP ON ROWS
FROM SalesCube
WHERE (January, SalesRep.[All], Geography.USA)
returns the following dataset:
You can specify only those dimension properties projected on the axis for that particular axis. You can mix requests for intrinsic dimension and level member properties in the same query with intrinsic member properties. The difference between intrinsic dimension and level member properties and intrinsic member properties is explained in greater detail later in this topic.
Properties Function
Member properties can also be retrieved by the use of the Properties function in MDX. For example, the following MDX query uses the WITH keyword to create a calculated member consisting of the [Store Sqft] member property:
WITH
MEMBER [Measures].[Store Size] AS
'Val(Store.CurrentMember.Properties("Store Sqft"))'
SELECT
{[Measures].[Unit Sales], [Measures].[Store Size]} ON COLUMNS,
{[Store].[Store Name].Members} ON ROWS
From Sales
to generate a result set similar to the one in the following table:
Note the use of the Val() function in the MDX query example. The Properties function is a string function; all member properties retrieved with the Properties function will be coerced into strings.
Intrinsic Dimension and Level Member Properties
All dimensions and levels support a list of intrinsic member properties, displayed in the following table. These member properties are used in the context of a specific dimension or level, and supply values for each member of the specified dimension or level. For example, specifying the following statement in a Multidimensional Expressions (MDX) query:
[Sales].Name
returns the names of each referenced member of the [Sales] dimension.
| Property | Description |
|---|---|
| ID | The internally maintained ID for the member |
| Key | The value stored in the MEMBER_KEY column of the MEMBERS schema rowset for the member |
| Name | The name of the member |
Dimension member properties are preceded by the name of the dimension to which the property applies. The following example demonstrates the appropriate syntax:
DIMENSION PROPERTIES «Dimension».ID
Level member properties can be preceded with the level name or, for additional specification, the dimension and level name, as shown here:
DIMENSION PROPERTIES [«Dimension».]«Level».ID
Intrinsic Member Properties
All members support a list of intrinsic member properties as well, displayed in the following table. Intrinsic member properties cannot be requested for a specific dimension or level; they apply to all members of an axis dimension in a Multidimensional Expressions (MDX) query. Specifying, for example, the following statement in an MDX query:
PROPERTIES DESCRIPTION returns the description of each member in the axis dimension.
The following table lists the intrinsic member properties supported by Microsoft® SQL Server™ 2000 Analysis Services.
| Property | Description |
|---|---|
| CALCULATION_PASS_DEPTH | For calculated cells only. The pass depth for the calculation formula, this property determines how many passes are needed to resolve the calculation formula. For more information about pass order, see Understanding Pass Order and Solve Order. |
| CALCULATION_PASS_NUMBER | For calculated cells only. The pass number for the calculation formula, this property determines on which pass the calculation formula will begin evaluation and end calculation. The default for this property is 1; its maximum value is 65,535. For more information about pass order, see Understanding Pass Order and Solve Order. |
| CATALOG_NAME | The name of the catalog to which this member belongs. |
| CHILDREN_CARDINALITY | The number of children that the member has. This can be an estimate, so you should not rely on this to be the exact count. Providers should return the best estimate possible. |
| CONDITION | For calculated cells only. The calculation condition of the calculated cells. This property receives an MDX logical expression, which is evaluated on each cell in the calculation subcube. If it returns True, the calculation formula is applied and the cell returns the resulting value. If it returns False, the cell returns the original cell value. If not specified, CONDITION defaults to True (in other words, the calculation formula applies to all cells in the calculation subcube.) |
| CUBE_NAME | The name of the cube to which this member belongs. |
| DESCRIPTION | A human-readable description of the member or calculated cells definition. |
| DIMENSION_UNIQUE_NAME | The unique name of the dimension to which this member belongs. For providers that generate unique names by qualification, each component of this name is delimited. |
| DISABLED | For calculated cells only. A Boolean property that indicates whether or not the calculated cells are disabled. DISABLED defaults to False. |
| HIERARCHY_UNIQUE_NAME | The unique name of the hierarchy. If the member belongs to more than one hierarchy, there is one row for each hierarchy to which it belongs. For providers that generate unique names by qualification, each component of this name is delimited. |
| LEVEL_NUMBER | The distance of the member from the root of the hierarchy. The root level is zero. |
| LEVEL_UNIQUE_NAME | Unique name of the level to which the member belongs. For providers that generate unique names by qualification, each component of this name is delimited. |
| MEMBER_CAPTION | A label or caption associated with the member. It is used primarily for display purposes. If a caption does not exist, MEMBER_NAME is returned. |
| MEMBER_GUID | The member GUID. |
| MEMBER_NAME | The name of the member. |
| MEMBER_ORDINAL | The ordinal number of the member. This is the sort rank of the member when members of this dimension are sorted in their natural sort order. If providers do not have the concept of natural ordering, this should be the rank when sorted by MEMBER_NAME. |
| MEMBER_TYPE | The type of the member. It can be one of the following values:
MDMEMBER_TYPE_FORMULA takes precedence over MDMEMBER_TYPE_MEASURE. Therefore, if there is a formula (calculated) member on the Measures dimension, it is listed as MDMEMBER_TYPE_FORMULA. |
| MEMBER_UNIQUE_NAME | The unique name of the member. For providers that generate unique names by qualification, each component of this name is delimited. |
| PARENT_COUNT | The number of parents that this member has. |
| PARENT_LEVEL | The distance of the member's parent from the root level of the hierarchy. The root level is zero. |
| PARENT_UNIQUE_NAME | The unique name of the member's parent. NULL is returned for any members at the root level. For providers that generate unique names by qualification, each component of this name is delimited. |
| SCHEMA_NAME | The name of the schema to which this member belongs. |
Columns in the MEMBERS schema rowset support the intrinsic member properties. Other intrinsic member properties can be supported, depending upon the provider. However, all providers must support the intrinsic member properties listed here to be compliant with the OLAP section of the OLE DB specification dated March 1999 (2.6).
Intrinsic member properties are used without additional specification of any sort, as intrinsic member properties apply to all members. The following syntax example demonstrates usage:
PROPERTIES «Property» Important Because intrinsic member properties cannot be qualified by the dimension or level name, a consumer cannot choose different intrinsic member properties for different dimensions (or levels) on an axis. For example, if the ROWS axis has Geography and SalesRep dimensions, the consumer cannot choose the MEMBER_CAPTION intrinsic member property for the Geography dimension or the MEMBER_UNIQUE_NAME intrinsic member property for the SalesRep dimension. The consumer must choose the same intrinsic member property (or properties) for all dimensions on an axis.
Custom Member Properties
Custom member properties can be added to a specific named level in a dimension. Custom member properties cannot be added to the (All) level of a dimension, or to the dimension itself. Custom member properties can be added to server based dimensions or cubes using Dimension Editor or Cube Editor in Analysis Manager, or by an application using the Decision Support Objects (DSO) library. Additionally, custom member properties can be defined as part of the CREATE CUBE statement when creating local cubes in PivotTable® Service.
The syntax used to refer to custom member properties is similar to that used to refer to intrinsic level member properties, as demonstrated in the following example:
PROPERTIES [«Dimension».]«Level».«Custom Member Property»
