This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

July 2000

Database Design

Write Useful and Complex Queries With MDX

Learn advanced MDX query functions to solve your important business questions in record time.

by Andrew Brust

Reprinted with permission from Visual Basic Programmer's Journal, July 2000, Volume 10, Issue 7, Copyright 2000, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange.

The MDX query language, an SQL-like language, can be tough to learn. However, MDX offers powerful functionality you can use to write more effective and advanced queries, and get information from your OLAP Services cubes that many front-end tools would have trouble fulfilling in a single step (see the sidebar "What Is MDX?").

In this article, I'll discuss three sample queries that demonstrate some advanced MDX functions and properties, such as the YTD(), SUM, and Descendants() functions and the Lag(), Lead(), and Children properties (see my previous article "Put OLAP and ADO MD to Work," VBPJ August 1999). I've translated each query into pseudo-code that looks like Visual Basic syntax. You can run these queries in the MDX Sample Application that comes with SQL Server 7 OLAP Services (see Figure 1). The MDX Sample App and its VB source code are normally installed whenever you run the client tools setup. The application can run on the same box as the OLAP server, or on another PC on the same Windows network, running Windows 95, 98, or NT.

You should begin by becoming familiar with two key terms-tuple and set. These are the major syntactical elements of MDX queries and will be referred to in the tables accompanying this article. The OLAP Services documentation defines a tuple as "an ordered collection of members from different dimensions or a single member from one dimension." So, ([Time].[1997], [Measures].[Unit Sales]) is a tuple and so is [Measures].[Unit Sales]. A tuple that specifies members on only one or only a few dimensions refers implicitly to the default member (usually the [All] member) for all the others.

A set is a collection of tuples. In MDX queries, each axis specification-the code preceding On Columns, On Rows, and other axis specifications-is a set. {([Time].[1997], [Measures].[Unit Sales]), [Measures].[Store Sales]} is a set, and so is this one-member specification: {[Measures].[Unit Sales]}. (Note the braces in both examples.) MDX has certain functions that generate sets, and you can use these functions to specify a set without braces.

You can begin exploring the sample queries now that you know some MDX programming terminology. The first query shows how you can write calculated members using the WITH clause, aggregation functions, and so-called xTD functions:

WITH MEMBER Measures.[YTD Store Sales] 
   AS 
   'SUM(YTD(),[Measures].[Store 
      Sales])'
SELECT 
   {[YTD Store Sales]} ON COLUMNS,
   Time.Month.MEMBERS ON ROWS
FROM Sales

This looks like a simple MDX query. You request a measure on the Columns axis (allowing you to skip the WHERE clause), and the members of a specific dimension and level on the Rows axis. Time.Month.Members is not surrounded by braces because the Members function in the On Rows axis specification generates a set. Look closer and you'll see that [YTD Store Sales] is not a measure defined in the cube, but is actually defined in the WITH clause that precedes the Select. We define [YTD Store Sales] as a member of the Measures dimension, and by the formula that appears in single quotes at the end of the WITH clause.

Note the use of the YTD() function. When this function is expressed without any arguments in its parentheses, it generates a set of members, starting at the beginning of the year and ending with the member currently being processed. The YTD() function generates a set of months, starting in January and ending with the current member, because the Rows axis of our query enumerates the members of the Month level of the Time dimension. The SUM function then adds the value of [Store Sales] for each month in the set, and reports that total in the cell for each given month. The pseudo-code below shows how this query might be fulfilled in a procedural manner:

For Each month in Time.Months
[YTD Store Sales] = 0
For i = 1 to month.Ordinal
[YTD Store Sales] = [YTD Store _
   Sales] (Measures.[Store Sales], _
   Time.Month(i))
   Next i
   Output month.Name, [YTD Store Sales]
Next month

You can substitute other aggregate functions for SUM, including AVG, MIN, MAX, and a slew of statistical functions (see Table 1). Other xTD functions that can be substituted for YTD() include QTD() (quarter-to-date), MTD() (month-to-date), and WTD() (week-to-date). The MTD() and WTD() functions don't make sense for the Sales cube, whose Time dimension has only Year, Quarter, and Month levels. In addition, a generic PeriodsToDate() function takes a level name as an argument. If you want the xTD set's membership to end at a specific member, rather than the current one, you can supply all xTD functions with a particular terminating member.

Up the Ante With Peer Level Functions

The second query demonstrates a couple of peer level functions:

WITH MEMBER [Measures].[Sales Last 
   Month] AS
   '([Time].CURRENTMEMBER.LAG(1), 
      [Store Sales])'
SELECT
   {[Store Sales], [Sales Last 
      Month]} ON COLUMNS,
   Time.Month.MEMBERS ON ROWS
FROM Sales

This query features the same set of elements on its Rows axis as the first query and defines a calculated member of the measures dimension using the WITH clause, also like the first query. However, in this second query, you use a peer property called Lag, rather than an aggregate function in your calculated member formula. Specifically, you're asking that [Sales Last Month] be calculated as the [Store Sales] for the member that "lags" one behind the current member of the Time dimension. Given your Rows axis expression of Time.Month.Members, the member that "lags" one behind the current member of the Time dimension equates to what you might colloquially call "last month." This pseudo-code illustrates how you calculate [Sales Last Month] as [Store Sales]:

For i = 1 to Time.Months.Count _
   [Sales Last Month] = ([Store _
   Sales], Time.Month(i-1))
   Output Time.Month(i).Name, _
      ([Store Sales], Time.Month(i)), _
      [Sales Last Month]
Next i

The Lag() property lets you specify a member as a negative offset from the current member. The Lead() property works similarly, but with a positive offset, so member.Lead(n)= member.Lag(-n). Also useful are the PreviousMember and NextMember properties, which are equivalent to Lag(1) and Lead(1) respectively. I call all of these properties peer properties because they identify members within the same level of a dimension as the object of which they are properties. Other peer functions are available for you to use as well (see Table 2).

MDX provides a host of properties and functions to identify sets of, or individual members that are, children (or descendants) of the object that invokes them. The third query uses two of these: the Descendants() function and the Children property. MDX also provides functions for taking members of a set and drilling down or rolling up some of them. This query uses one of these: the DrillDownMember function. Finally, the third query illustrates how to use the WHERE clause to analyze a certain set of members within a dimension, rather than simply specifying a measure to report:

WITH 
   MEMBER [Measures].[Sales Last 
      Month] AS 
   '([Time].CURRENTMEMBER.LAG(1), 
      [Store Sales])'
SELECT
   {[Store Sales], [Sales Last _
      Month]} ON COLUMNS,
   DRILLDOWNMEMBER(
      DESCENDANTS([Product].Food, 
         [Product Category], 
            SELF_AND_BEFORE), 
      [Frozen Foods].CHILDREN
   ) ON ROWS
FROM Sales
WHERE ([Time].[1997].[Q2])

Analyze the Query

This query is complex, as is typical of powerful MDX queries. However, learning to parse and expect complex queries makes them less intimidating. You can take the third query apart bit by bit. The first part is easy: The WITH clause and the Columns section of the Select are the same as in the previous query. Next, the Rows axis consists of an invocation of the DrillDownMember function, and the Descendants function within it. From here, you can work inside out.

The Descendants function takes a member (the first argument) and returns all of its descendants at a certain level (the second argument). An optional third argument specifies whether only those members should be shown, or if their parents, up to the level of the member itself, and/or their children should be supplied as well. In our query, the constant Self_and_Before indicates that you want the members at the requested level ([Product Category]) and the [Product Department] level. Note the [Product Department] level is immediately below the [Product Family] level, of which [Food] is a member. Other choices for this function include Self_and_After, Before_and_After, Self_Before_After, After, Before, and Self, which is the default. Other functions, such as Ancestor(), Cousin(), FirstChild, and LastChild, provide convenient ways to specify members on levels other than the current member's level (see Table 3). This pseudo-code shows how to write the query's Descendant's function call:

For Each dept In Food.Children 
   ' [Product Department] level
   Output dept
For Each cat In dept.Children 
   ' [Product Category] level
   Output cat
Next cat
Next dept

The set generated by the Descendants function becomes the first argument for the DrillDownMember function call that precedes it. DrillDownMember takes two sets, and generates a new set consisting of the first one, plus all immediate children of any members of this set that are identified in the second one. In the query, the products and departments are generated, as well as the children (at the [Product Subcategory] level) of all the product categories subsidiary to [Frozen Foods]. The generic pseudo-code for the DrillDownMember function looks like this:

For Each mbr in FirstSet.Members
   Output mbr
If mbr in SecondSet.Members Then
   For Each child In mbr.Children
      Output child
   Next child
End if
Next mbr

DrillDownMember is only one of several drill up/drill down MDX functions (see Table 4).

Figure 1.Size Up the Data. The MDX Sample application is pictured here with our third sample query displayed in the upper pane. The lower pane displays the query results in a scrollable grid. The middle pane displays the various dimensions, levels, and members each cube in the database contains. The [Food] Member of the [Product Family] level of the [Product] dimension of the Sales cube is drilled down.

Finally, don't forget to analyze the WHERE clause. This one looks a lot more like an SQL WHERE clause than you might be used to because it specifies a particular dimensional member-[Time].[1997].[Q2]. The SQL equivalent might look something like this:

WHERE [Time].[Year] = 1997 AND 
   [Time].[Quarter] = Q2

So, the WHERE clause in the third query is substantially different from one that specifies a measure only:

WHERE Measures.[Store Sales]

MDX treats measures as if they comprise a dimension unto themselves, so you can see how the latter WHERE clause is comparable to the one in the third query.

. You can experiment with MDX functions and properties on you own now that you've seen how they work in the sample queries. Download the MDX file, load it in the MDX Sample App, and run each query. Once you've gone through the queries a few times, try modifying them slightly and seeing if you can get the new versions to run. Don't be surprised if you get a lot of errors at first. Keep trying until you get the queries to run, then you'll start learning MDX, and you'll be able to get extremely valuable answers to important business questions in record time.ν