Using Member Properties

Analysis Services

Using Member Properties

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 [<axis_specification>
       [, <axis_specification>...]]
  FROM [<cube_specification>]
[WHERE [<slicer_specification>]]

The <axis_specification> value includes an optional <dim_props> value, which enables querying of dimension, level, and member properties using the DIMENSION PROPERTIES keyword. 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 Dimension. 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. Intrinsic member properties for dimensions and levels must be preceded with the name of the dimension and/or level. Intrinsic member properties for members cannot be qualified by the dimension or level name. Custom member properties should be preceded by the name of the level in which they reside.

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:

For more information about building calculated members, see Building Calculated Members in MDX.

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.

Using Member Properties

Analysis Services