MDX is a SQL-like query language used to retrieve data from OLAP databases and to define Key Performance Indicators (KPI) in SQL Server 2005 Analysis Services.

by Kevin S. Goff

Mar 17, 2008

Page 5 of 6

Tip 9: Time, Time, Time Functions to Deal with Time Periods
Suppose you have a report that reads an OLAP database and reports on the last four quarters of available data. You certainly don't want to hard-wire the date range into the query. But how do you write an MDX query to determine the last four quarters of available data, at any one time?

You can specify the LastChild member of a Quarterly date dimension to retrieve the last date, and then use the LastPeriods function to retrieve the last four (or eight, etc.) quarters going backwards from the LastChild:

Note that ParallelPeriod receives three parameters: the level expression (which could be a month, a quarter, etc.), the number of periods to go backwards in time (12 months, 4 quarters, etc.), and then the starting point. ParallelPeriod is particularly valuable for trend analysis.

Tip 10: Maintaining Order with SOLVE_ORDER
Up until now, you haven't needed to tell MDX queries to calculate results in any specific sequence (except when you explicitly defined a set, and then created a calculated member that accessed the set). But there may be times where you are calculating variances in both dollar amounts and percentages, and therefore need to calculate the dollar variances before calculating percent variances.

You can use the MDX SOLVE_ORDER statement to specify the order in which calculations occur. For example, suppose you need to display Sales Amount,Freight Cost, and Freight Per Unit, for the third quarter and fourth quarter, and then for the difference between the two quarters. You'll need to calculate the Freight Per Unit first for each of the two quarters first, before calculating the Freight Per Unit variance between the two quarters:

Tip 11: The Family Descendants
MDX contains a DESCENDANTS function that returns some or all descendants of a member at a specific level. You can use this function whenever you need to retrieve (for example) sales data for multiple levels of a specific hierarchy.

Suppose you want to retrieve (in a single result set) sales data for a year, and then for each hierarchical representation of the year (Semester, Quarter, Month, and Day), you can use the DESCENDANTSfunction as follows:

Note the use of the SELF_AND_BEFORE parameter, which returns all the descendants of the Calendar Year hierarchy. If you don't specify this second parameter, the query will return data for the lowest level only (i.e., daily sales).

In other usage, suppose you want to retrieve sales data for the Year, Semester, Quarter, and Month (in other words, everything but daily sales). You can use the SELF_AND_BEFORE parameter against the Calendar Month level:

Besides SELF_AND_BEFORE, there are other options you can use (such as SELF, AFTER, BEFORE, and more—check SQL Server Books Online for details.

Tip 12: "Look Up, Look Around"
Once in a great while, you may find a situation where you need to query data from multiple cubes. This is a somewhat controversial subject, as some people view this as a design issue with the cubes. However, online questions on this subject indicate that some developers certainly need to be able to do this.

As I stated earlier, you can only specify one cube in the FROM clause. However, MDX provides the LOOKUPCUBE function: you simply construct a query string and pass it as a parameter.

For example, suppose you have a cube of labor data and you've constructed a basic MDX query to retrieve hours worked and total labor dollars by quarter. Now you need to add material purchase amount by quarter to the result—and the material purchases belong in a different OLAP cube. Here is a query sample to accomplish this: