MDX in Analysis Services: Calculated Members: Leveraging Member Properties - Page 2

November 24, 2003

Calculated Members and Member Properties

Member properties comprise attributes associated with members. As most of us are aware, a member is an item (called a category in other products, such as Cognos PowerPlay) in a dimension level. A member property is created in a dimension for the purpose of providing end users with additional information, usually less important than the information provided by the members themselves, about the dimension's members.

Member properties can exist in both shared and private dimensions. As an illustration, a store dimension might have member properties such as square footage, postal code and telephone area code that we can use in providing more specific information about the store, based upon various business and / or other-than-standard analysis perspectives.

Selecting and Retrieving Member Properties

Special considerations come into play, in using member properties for calculated members, not the least of which is the fact that the values stored at the member property level, even numeric values, are stored as text strings. This means that conversion of the string to a number is required to perform any operations where the property is supposed to behave like a number. We will illustrate this set of circumstances, as well as a means of handling it, to achieve "rollup" of the properties to higher levels, in the simple exercise that follows.

One of the member properties that occurs within a dimension of the Warehouse and Sales cube will serve well to illustrate the selection and retrieval of member properties. As we have discussed in previous lessons, a key activity in getting a good understanding of the cube structure is obtaining an understanding of the metadata. A great place to get a bird's eye view of the metadata of a cube is the Cube Editor in Analysis Services. A quick review of the Store dimension within the tree in the Cube Editor (right-click the cube and select Edit to reach the view from the Analysis Manager console) reveals the presence of member properties for the Store dimension, as shown in Illustration 1.

Illustration 1: Cube Editor View of the Store Dimension Member Properties

The member properties include an example of a numerical value, Store SqFt, which contains the square footage area for the respective store members. As we have discovered, although numeric in nature, the member property information is stored as a text string. This provides us with an excellent example for our exercise in selecting and retrieving such properties in a way that will make them useful to information consumers.

Let's begin the hands-on portion of the lesson by creating a calculated member within which we will work with member properties. The MDX Sample Application will be our tool for constructing and executing the MDX we examine, and for viewing the result datasets we obtain.

1.  Start the MDX Sample Application.



4.  Select the Warehouse and Sales cube in the Cube drop-down list box.

5.  Type the following, which forms the core of our exercise, into the Query pane:

-- MXAS09 - 1:  Tutorial Query - Step 1
   {[Measures].[Store Sales]} ON COLUMNS, 
   {[Store].[Store Name].Members} ON ROWS 
From [Warehouse and Sales]

Our query appears in the Query pane as partially shown in Illustration 2.

Illustration 2: The Core Query in the Query Pane

6.  Execute the query by clicking the Run button.

The result dataset appears partially displayed (scrolled to the bottom) in Illustration 3.

Illustration 3: The Initial Query Result Dataset