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.
2.
Clear the top
area (the Query pane) of any queries or remnants that might appear.
3.
Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.
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
SELECT 
{[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
» 
Comment and Contribute
