MDX in Analysis Services: Calculated Members: Leveraging Member Properties

Monday Nov 24th 2003 by William Pearson

Summary: Discover potential uses for member properties using calculated members. Join author Bill Pearson in a practical example of delivering detail about members, as well as the use of an external function, within a calculated member.

About the Series ...

This is the ninth tutorial article of the series, MDX in Analysis Services. The series is designed to provide hands-on application of the fundamentals of MDX from the perspective of MS SQL Server 2000 Analysis Services ("Analysis Services,"); our primary focus is the manipulation of multidimensional data sources, using MDX expressions in a variety of scenarios designed to meet real-world business intelligence needs.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see the first lesson of this series: MDX Concepts and Navigation.

Note: At the time of writing, Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.


In our last tutorial, Calculated Members: Further Considerations and Perspectives, we continued the exploration of calculated members that we began in the previous lesson, Calculated Members: Introduction. We discussed ways of handling the creation of calculated members with MDX, and practiced the addition of multiple calculated members in a single MDX query. We explored additional perspectives of calculated members along the way, including format considerations. Finally, we exposed a means for controlling the order in which calculated members are solved, discussing why precedence of calculation might be critical in generating the results we expect, and examining an instance where the SOLVE_ORDER keyword made it possible to bring about the desired results in a returned dataset.

Our work with calculated members in the previous lessons brings us to a new consideration: the selection and retrieval of member properties. In this article, we will discuss the nature of member properties, providing illustrations of how they can be used to help us to meet our business needs. We will then focus upon the selection of member properties within our MDX queries, using calculated members. We will see how MDX queries allow for the selection of member properties, which are defined for the dimensions themselves, or on individual member levels. Member properties can be useful within the realm of business intelligence, as we shall see with the practice example that we explore together.

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
   {[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

The purpose of the first-step core query is simply to set up the axes for the enhancements we will make to allow the retrieval and display of member properties. As a useful way to contrast the behavior of the text-string property that we will be attempting to present as a number, we have included a numerical value from the cube, Store Sales, simply to compare to the Store SqFt member property that we will be retrieving.

7.  Save the query with changes as MXAS09-1.

We will now add the syntax required to create a calculated member based upon the Store SqFt member property.

8.  Modify the comment line in the query to read as follows:

-- MXAS09 - 2: Tutorial Query - Step 2

9.  Add the following before the SELECT statement:

 MEMBER [Measures].[Store Size] AS 
 'Val(Store.CurrentMember.Properties("Store Sqft"))'

The syntax addition above contains Val, a VBA function that returns numbers contained in a string as a numeric value of appropriate type.

10.  Modify the first line of the SELECT statement as follows:

Step 1 Affected Lines:

   {[Measures].[Store Sales]} ON COLUMNS,

Step 2 Modified Lines:

{[Measures].[Store Sales], [Measures].[Store Size]} ON COLUMNS,

Our query now appears in the Query pane as shown in Illustration 4.

Illustration 4: Step 2, With Additions Noted, as it Appears in the Query Pane

11.  Execute the query by clicking the Run button.

The result set appears partially displayed in Illustration 5.

Illustration 5: Step 2 Partial Results Dataset

In the results set, we note that the new calculated member, Store Size, appears, with single zeros filling the fields for which no square footage is defined in the cube (the "nulls" are converted to zeros). We can see that the text strings that occupy the Store SqFt member property have been converted to numerical values, as the nulls that exist in the cube for certain members have become zeros instead.

12.  Save the query with changes as MXAS09-2.

Next, let's change the SELECT clause to retrieve and display not only the Store Names (the lowest level of the Store dimension), but to retrieve and display the "rollup" levels in the hierarchy of Store City, Store State, and Store Country.

13.  Modify the comment line in the query to read as follows:

-- MXAS09 - 3:  Tutorial Query - Step 3

Within the SELECT clause of the query, immediately following the {[Measures].[Store Profit], [Measures].[Store Size]} ON COLUMNS, line, modify the next existing line as follows:

Step 2 Affected Line:

{[Store].[Store Name].Members} ON ROWS

Step 3 Modified Line:

{[Store].Members} ON ROWS 

The modification above changes the SELECT clause to retrieve the members of the Store dimension in general, which will include all members - not just Store Names. This will mean that the individual stores, as well as the level members to which they belong, will be retrieved.

The modified query appears in the Query pane, in its entirety, as depicted in Illustration 6:

Illustration 6: Step 3, with Modification Circled, as Displayed in the Query Pane

14.  Execute the Step 3 query by clicking the Run button.

The result dataset appears partially displayed in Illustration 7 below:

Illustration 7: Partial Results Dataset, Query Step 3

We notice that, at the rollup levels, we have #ERR appearing as the result in the affected fields. This occurs because, although we have converted the Store SqFt member property to a numeric value, we have yet to make provision for an aggregation to occur; the square footage values are held at the member level, and not at the various dimension levels to which the stores belong in our cube. We will handle this with the next step, where we will create a calculated member, based upon the Store dimension, to sum the individual store square footages at the appropriate rollup levels.

We will create a calculated member that correctly handles the summing, while leaving our original calculated member in place for comparison purposes.

15.  Save the query with changes as MXAS09-3.

16.  Modify the comment line in the query to the following:

-- MXAS09 - 4:  Tutorial Query - Step 4

17.  Within the WITH statement of the query, immediately following the 'Val(Store.CurrentMember.Properties("Store SqFt"))', line, add the following syntax to create a new calculated member for Total Store Area:

MEMBER[Measures].[Total Store Area] AS 
   'SUM(Descendants([Store].CurrentMember, [Store Name]) , [Store Size])' 

18.  Within the SELECT statement of the query, in the first line, modify the syntax to include selection of the new calculated member, Total Store Area:

Step 3 Affected Line:

{[Measures].[Store Sales], [Measures].[Store Size]} ON COLUMNS,

Step 4 Modified Line:

{[Measures].[Store Sales], [Measures].[Store Size], 

    [Measures].[Total Store Area]} ON COLUMNS,

The modified query appears in the Query pane, as shown in Illustration 8.

Illustration 8: Step 4, with Modifications Circled, as it Appears in the Query Pane

19.  Execute the Step 4 query by clicking the Run button.

The results dataset appears as partially depicted in Illustration 9.

Illustration 9: Step 4, Partial Results

The "parallel calculated member," Total Store Area, appears alongside its predecessor, and appears to have overcome the limitations of the Store Size calculated member. We see rollups occurring accurately, in that the individual stores' square footages now add correctly at the corresponding Store City, Store State, and Store Country levels.

20.  Save the query with changes as MXAS09-4.

Were this destined to support a "real world" reporting platform for a client, I would perform formatting at this stage, after removing the Store Size calculated member from the SELECT statement, so as not to display it. We will leave the query at this stage, however, with an invitation to all readers to play with the layout further, as time is available.

With this example, we have advanced another step within our examination of calculated members, retrieving a member property and returning it, after a conversion with the val function, in a way that allows it to deliver more value to the information consumers. We have taken steps to leverage the property beyond its standard capability to store information for the member that contains it, and to provide rollup values through the SUM function. This demonstrates additional possible uses for the aggregation of values using a non-measure calculated member.

Summary and Conclusion ...

In this lesson, we extended our growing familiarity with calculated members, to include the selection and retrieval of member properties. We briefly discussed the nature of member properties, providing illustrations of how they can be used to help us to meet our business needs. We then focused upon the selection of member properties within our calculated members, performing a practice example in which we used member properties to provide square footage of our store locations, after converting the values concerned to a usable datatype, to further demonstrate possible ways that we might add value to the projects we develop for information consumers.

» See All Articles by Columnist William E. Pearson, III

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved