Retrieving Member Properties

The set expression in the axis specification for a given axis 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. Many times, members have additional properties associated with them. Properties are usually available for all members at a given level. For example, the Products level may offer the SKU, SRP, Weight, and Volume properties for each product. Those properties are not dimensions, but contain additional information about dimension members. An application may want to extend member information by adding member properties on the axis. Each level of each dimension may contain a set of available properties for the members.

Additional member properties can be selected by using the DIMENSION PROPERTIES keyword after the set expression of the axis specification. For example, the expression

SELECT NON EMPTY Products.MEMBERS
   DIMENSION PROPERTIES Products.SKU, Products.SRP ON ROWS,
CROSSJOIN(Years, (Sales, BudgetedSales)) ON COLUMNS
FROM SalesCube
WHERE (January, SalesPerson.All, Geography.USA)

returns the following dataset:

You may specify only those dimension properties projected on the axis for that particular axis.

OLE DB for OLAP requires that all providers support a list of mandatory member properties, which are the same as the columns of the MEMBERS rowset. For more information, see “Mandatory Member Properties” in Chapter 4.

The syntax for specifying properties in the axis specification is <property_name> for mandatory properties, and <level_name>.<property_name> for all other properties. Because optional properties are associated with levels, named levels have to exist in order for this to work. If the data source does not support named levels, OLE DB for OLAP requires that it recognize a dummy level whose name is the same as the dimension name. All members belong to this level. Now the consumer can get any optional property by referring to it as <dimension_name>.<property_name>.

Note   Because mandatory properties cannot be qualified by the dimension name or the level name, a consumer cannot choose different mandatory properties for different dimensions (or levels) on an axis. For example, if the ROWS axis has Geography and SalesRep dimensions, then the consumer cannot choose MEMBER_CAPTION for the Geography dimension, and MEMBER_UNIQUE_NAME for the SalesRep dimension. It has to choose the same property (or properties) for all dimensions on an axis.