Iterating Through Member Sets

If you're a database programmer, you know the value of a cursor. When you need to perform a complex operation with a collection of records, a cursor applies that operation to each record. A cursor is a type of iterator. In the world of object-oriented programming, iterator is the generic term for any object that facilitates stepping through a collection of other objects.

MDX uses the Generate() function and named sets to support iteration. The power of these methods is mostly invisible to end users, but using them lets you apply operations to each dimension member in a set, just as a cursor lets you apply operations to each record in a table. Let's look at how these iterators work and how you can use them to create useful solutions to your organization's data-analysis problems.

If you spend a lot of time with MDX, the query language of SQL Server 2000 Analysis Services and SQL Server 7.0 OLAP Services, you know that you can solve certain types of problems by applying an MDX formula to each member in a set. For example, if you want to find out which geographic regions contain your top 10 customers, you can make a set of your top 10 customers, then apply an MDX formula that returns each customer's region.

SQL Server Books Online (BOL) describes how the Generate() function steps through items in a set. For each item, Generate() evaluates an MDX formula, the results of which combine to form a new set. In the following example, Generate() steps through each item in the set called Top 10 Customers:

Generate( [Top 10 Customers], { Customers.CurrentMember.Parent } )

For each of these 10 customers, Generate() evaluates the formula Customers.CurrentMember.Parent and replaces Customers.CurrentMember with the current item in the set so that the formula returns the parent of the current customer. The result of the Generate() function is a set containing the parents of the top 10 customers.

The following example shows another way to express the Generate() function:

Generate(  [Top 10 Customers] , {  [Top 10 Customers].Current.item(0).Parent } )

This example demonstrates how to use the Current property of a set. This property is similar to the CurrentMember property of a dimension hierarchy except that when Current operates on a set, the property returns a tuple instead of a member. Current returns a tuple because sets aren't limited to members from one dimension but instead each item in a set can include a member from each dimension. The example above uses the item property of a tuple to return the first member in the tuple. In most situations, a set contains members from only one dimension, so item(0) is a good way to convert a tuple to a member.

If you compare the two examples above, the first example, which uses CurrentMember, is easier to read. Both expressions are functionally equivalent—so why would you bother to use the Current property? The CurrentMember property is limited to maintaining one position within a dimension, so you can't reference two positions from different MDX functions independently. By using the Current property, you can step through more than one set on the same dimension. In other words, the Current property lets you nest MDX functions that step through the same dimension and still reference the current item in each function.

The example that Listing 1 shows illustrates my point. This nested MDX formula performs an intersection between the Top 10 BY Profit set and the Top 10 BY Sales set. You can't use the CurrentMember property to perform this intersection because both the Generate() and Filter() functions are stepping through a set of members from the same dimension.

Now let's further complicate the problem: What happens if you don't have a name for the set that you want to step through? For example, let's change the code in Listing 1 so that it finds the top 10 customers by sales who live in Washington, as the code in Listing 2 shows. I could have defined WaCustomers as a set in the query's WITH clause or by using a CREATE statement before running the query, but putting the definition directly in the query was more convenient. I used the AS keyword to give the Descendants operation the name WaCustomers so that I could refer to it later. I haven't found any documentation about the AS keyword in BOL, but since I first encountered AS in MDX code, I've used it frequently.