Advanced MDX - Microsoft SQL Server 2008 Analysis Services - page 158

to define them in the model. Examples of intrinsic properties include MEMBER_NAME ,
MEMBER_UNIQUE_NAME , MEMBER_CAPTION , PARENT_UNIQUE_NAME , and many others. You
can find a full list of intrinsic properties in SQL Server Books Online.

. Custom member properties are defined by relationships between attributes in the

multidimensional model. All dimension attributes that are related to each other
make up a set of the custom member properties. In our FoodMart example, Store
Manager , Store Sqft , and Store Type are custom member properties for all members
of the Store attribute. (For more information, see Chapter 5, “Dimensions in the
Conceptual Model.”)

In Analysis Services 2000, you had to define all member properties when creating a multi-
dimensional model. In Analysis Services 2005 and Analysis Services 2008, member proper-
ties are implicitly created when you define dimension attributes and the relationships
between them, but they can be modified by a designer of multidimensional model if needed.

There are two different ways of using an MDX query to request a member property. In the
following query, we use a DIMENSION PROPERTIES clause of an axis specification to get the
name of the manager of Store 1:

SELECT [Store 1] DIMENSION PROPERTIES [Store].[Store].[Store Manager]
ON COLUMNS FROM [Warehouse and Sales]

This query, in addition to standard properties such as MEMBER_NAME , MEMBER_UNIQUE_NAME ,
and so on retrieves the Store Manager property for all the members you specified in the
axis specification. You can use this syntax if, for example, you want to display member
properties in the user interface, maybe as an additional column to a report. You can see
member properties in SQL Server Management Studio by double-clicking the member
name, as shown in Figure 11.1.

Another way to retrieve member properties is to use the MDX Properties function. This
function is called on the member and takes a string containing the name of member prop-
erty. One way you might use this function is to order a set of members by a property. For
example, the following query orders the set of all stores by the store size:

SELECT
{[Measures].[Store Cost]} ON COLUMNS,
Order( [Store].[Store].[Store].members,
store.store.currentmember.Properties(“Store Sqft”)) ON ROWS FROM [Warehouse and
Sales]

Cell Properties

Just like the dimension member has member properties associated with it, each cell has
cell properties associated with it. Analysis Services supports only intrinsic cell properties,
unlike the member properties for which both intrinsic and custom properties are
supported. The designer of the multidimensional model can't create custom cell properties.

All MDX SELECT statements return a default set of cell properties: