MDX Secret Code Revealed

An undocumented MDX function handles large numbers of member properties

In "Member Properties Revisited," December 2000, I wrote about using a cube that had a very large number of member properties, which are attributes of dimension members. (For example, members of a Product dimension might have properties such as size, color, and weight.) OLAP Services and Analysis Services let you create a virtual dimension based on the unique values of a member property so that you can use member properties during your analysis without your analysis application needing to support specific member-property features. But creating a virtual dimension for each member property isn't practical because most analytical applications don't support large numbers of dimensions well. Even if the application did support large numbers of dimensions, who would want to try to navigate a cube that had 50 or more dimensions? I joked in the December issue that I wished I knew some super-secret MDX command to help solve the problem of creating virtual dimensions on the fly. Well, believe it or not, an MDX function that was undocumented in the initial release of Analysis Services provides a great solution to this problem.

The MDX function is CreatePropertySet()—you use it to create a group of calculated members, one for each member-property value. The following query, which creates a group of calculated members that are children of the All Store Type member in the FoodMart Sales cube, is a simple example of how to use this function:

WITH SET CardTypes AS 'CreatePropertySet([Store Type].[All Store Type], [Customers].[Name].Members,  [Customers].CurrentMember.Properties  ("Member Card"))'SELECT {[Unit Sales]} ON COLUMNS, CardTypes ON ROWSFROM Sales

The query creates one calculated member for each unique Member Card property value for the members of the Customers Name level. The query creates a new set, CardTypes, with the new calculated members and displays it on the rows of the result. Figure 1 shows the query's result set.

Listing 1 contains an equivalent query that doesn't use CreatePropertySet(). A close look at this MDX query reveals that the query uses a calculated member for each type of member card. Each of these calculated members filters the list of customer names by the member card type. The query cross-joins the resulting list of customers with All Store Type and aggregates the result to determine the new member's value. I included the cross-join with All Store Type because the calculated members are defined in the Store Type dimension. If you used one of these members without the cross-join, you wouldn't have a member from the Store Type dimension selected, and the query would result in an error. The query then defines a named set that includes all the new calculated members.

Remember that to write this query, you need to know which member-property values exist. The ability to discover unique member-property values is the real power of CreatePropertySet(): It not only creates the new calculated members for you, but it searches the member set to determine the complete list of unique member properties for those members.

The CreatePropertySet() function syntax looks like this:

CreatePropertySet( <>, <>, <> )

The function accepts three parameters. The Parent Member parameter specifies which dimension member you want to use as the parent for the new calculated members. To create the set of calculated members for the Set parameter, Analysis Services uses the unique values resulting from evaluating the third parameter, Expression. The Expression parameter should evaluate to a string-typed result.

In "Member Properties Revisited," the solution that the development team chose for analyzing a cube that had many member properties included creating an empty dimension in advance, then filling that dimension with calculated members representing each property value. One additional complexity of that cube was that each member-property had many dissimilar member-property values. For example, the member-property Size for the Product dimension had sizes for film, batteries, tires, and so on. And each product type had a different group of member-property values. So, for example, an end user who wanted to analyze batteries would expect to see only sizes for batteries (e.g., AA, AAA, C, D), not tire sizes (60R14, P215/65R16, and so on).

The CreatePropertySet() function works perfectly for such a large, complex cube because it lets you determine the unique set of property values for a subset of the products. You simply limit the members that the query passes as the second parameter to CreatePropertySet(). So, for example, if the Name level of the Product dimension had a property called Size, you could execute the following query:

CreatePropertySet( [EmptyDim1].[All], Descendants( [Tire], [Product].[Name] ), Product.CurrentMember.Properties("Size") )

This example uses all the descendant members of the Tire product at the Name level to determine the unique values of the Size property for all tire products. This function is just what the doctor ordered!

How It Works

As I said, the real power of CreatePropertySet() is its ability to determine the unique values of a member property within a group of dimension members. If you needed to determine the unique member-property values and you didn't have the CreatePropertySet() function, you would have to retrieve the member-property values for all the relevant dimension members and eliminate all the duplicates. This process can be very slow if you have many dimension members. CreatePropertySet() quickly determines this list of unique properties, but the rest of the execution can be time-consuming. To take advantage of the function's speed, you need to understand how Analysis Services processes MDX queries.

When you execute an MDX query, Analysis Services performs the work in two distinct steps. The first step is to evaluate the contents of the resulting axes (rows and columns); the second step is to calculate the values in the resulting cell grid. To see a vivid example of this two-step process, use the MDX Sample Application that comes with Analysis Services to execute the first sample query in this article. You'll see that Analysis Services fills in the member names on the rows and columns almost instantaneously, but then there's a noticeable delay before it fills in the cell values.

So, if you're developing an analysis application, your query might execute more quickly if you perform the first step of discovering the possible member-property values and skip the second step of determining cell values. For example, when a user wants to filter a list of dimension members by using a member-property value, you might want to display only a list of the possible member-property values—not any cell values—when the user configures this filter.

When you're writing a program that uses either ADO MD or OLE DB for OLAP to execute an MDX query, you first provide the MDX query as a string to Analysis Services to initiate execution. When Analysis Services returns to your program with either a pass or a fail code, the first step of execution is complete. You can then request the list of dimension members on the rows or columns, or you can start retrieving cell values at the various row and column coordinates. Because the first step of query execution resolves which dimension members reside on the rows and columns, retrieving the list of dimension members has no additional time cost. However, if you retrieve the list of cell values, Analysis Services must calculate those cell values. Depending on the query's contents, this process might take a long time. When you use the CreatePropertySet() function as I did in the query at the beginning of this article, the second step of execution requires Analysis Services to add up all the unit sales for each member card type. On my laptop, the first step of this query takes about 1 second, and the second step takes about 1¹Ž₂ minutes.

Now that I've described all the great things about CreatePropertySet(), let me warn you about one limitation: CreatePropertySet() can create only 759 members. Usually, this limitation isn't a problem, but if you have a member property whose value changes for almost every dimension member, you might not want to use CreatePropertySet() to determine the unique values. In such a case, letting your application scan the dimension members for the unique property values might be just as fast.

An analysis application that is flexible about how it handles member properties offers the ability to search, filter, and reorganize dimension members by using member properties. An application with this capability offers many more choices in structuring multidimensional cubes because a DBA can store attributes of the basic dimension entities as member properties that end users can use only if the properties are relevant to the current analysis. If an analysis application doesn't support member-property features, you must either create virtual dimensions or offer multiple alternative hierarchical organizations of the dimension members to give end users access to member properties during analysis. In a situation where you would otherwise create a relatively large number of member properties (because each entity has so many attributes), not having access to the member properties is a serious limitation. Cubes with large numbers of dimensions or hierarchies are unwieldy because only a few of the dimensions are relevant at any given time.

A great analysis application is simple and intuitive but powerful enough to get the answers everyone needs. Creating such an application is easier said than done, of course. Part of meeting the "simple and intuitive" objective is to reduce the amount of information the analyst must wade through to only the information that is relevant to the current analysis. Member properties can help significantly in achieving this objective because they don't complicate the basic navigation of a cube (e.g., drilling down, pivoting) but they're available when they're relevant.

The CreatePropertySet() function is crucial in implementing member-property features in an analysis application because the function performs the scanning and filtering of dimension members by member property for you. Until next time, you can reach me at [email protected].