I confess. I'm an MDX junkie. Not only that, but I've owned a BI consulting firm for the past nine years with MDX as a core competency. I also have to confess that I'm not much of a programmer. I know enough about T-SQL to be dangerous and enough about VB.Net to be sure that I'd be living on the street if I had to work as a programmer. But I love MDX for what it's been able to do for our clients.
MDX stands for multidimensional expressions. MDX is a query language for OLAP databases, much like SQL is a query language for relational databases. It's also a powerful calculation language. One of our clients referred to it as "SQL on steroids."
MDX is used by a host of OLAP-server vendors, including Microsoft, Hyperion, SAP, Applix and Microstrategy, and BI front-end providers such as Panorama Software, ProClarity, Business Objects and Cognos. MDX has become the lingua franca of multidimensional analysis.
Any database developer working with these OLAP tools needs to know MDX. I want to tell you why I think MDX is so important, and describe how we went about learning it.
At the recent Microsoft BI Conference in Seattle, I was struck by one session in particular - a dual demonstration of Analysis Services 2005 and Excel 7.0. The session was quite well done, but it left a big question in the air: How are companies going to stitch together all these pieces, now including Microsoft PerformancePoint? The thread is MDX.
MDX had its beginnings at Panorama Software. The original OLAP server (OLAP Services) in SQL Server 7.0 was acquired by Microsoft from Panorama in 1997. Mosha Pasumansky, a Panorama developer who joined Microsoft when the OLAP server was acquired, is credited with developing the MDX language.
I've built more than 30 full-scale data warehouse and BI solutions, with a focus on Microsoft SQL Server and Analysis Services, and Panorama NovaView. We're currently using Analysis Services 2005 and Panorama NovaView 5.2, the release that works with both the Analysis Services 2005 and SAP BW (NetWeaver BI) platforms.
My introduction to MDX came early. In 2000, one of our clients, a newspaper in Texas, wanted to be able to calculate average daily single-copy sales (sold in vending machines and store outlets). We could total single-copy sales for a week and then divide by seven, but then we'd have to do the same thing for months with differing day counts - not a workable solution. I decided to see if there was an answer using MDX in Panorama NovaView.
I began with an overview of MDX from the Microsoft Web site. The overview described tuples, sets, hierarchies, levels, members and calculated members. It showed examples of basic MDX functions such as Sum, Avg, Filter, LastPeriods, Descendants, Ancestor, ParallelPeriod and CurrentMember. I learned that you could combine MDX expressions in interesting ways to solve common business problems. After countless hours of experimenting, I arrived at the answer:
Avg (Descendants ([Time].[Calendar].currentmember, [Time].[Calendar].[Day]), [Measures].[Sales])
The single expression above illustrates some core MDX functionality.
First, MDX is smart about multidimensional hierarchies. For example, the [Time].[Calendar] hierarchy drills down from [All Time], to year, to quarter, to month, and then to day.
The core of the expression, Descendants ([Time].[Calendar].currentmember, [Time].[Calendar].[Day]), creates a set of all the descendants of whatever time period is selected in the report (that's the .currentmember piece of the expression) at the day level ([Time].[Calendar].[Day]).
Second, the Avg function demonstrates the calculation power of MDX. The entire expression averages all the [Measures].[Sales] for every day included in the set for whatever time period (.currentmember) you are referencing.
Our newspaper client was happy, but not quite satisfied. Now she wanted to see what average single-copy sales were a year ago. The MDX ParallelPeriod function addresses this nicely:
(ParallelPeriod([Time].[Calendar].[Year], 1, [Time].[Calendar].currentmember), [Measures].[Avg. Daily Sales])
This expression uses ParallelPeriod to refer to the [Time],[Calendar].[Year] level and go back an index of 1 to get the prior year. The use of the .currentmember function enables the expression to be evaluated for any month, week, or day selected in the query. Note that [Measures].[Avg. Daily Sales] refers to the formula I created earlier.
Almost every report or analysis needs to filter information. You want to look at specific time periods, products and customer groups (such as the top 25 by sales). MDX includes a variety of filter (set) functions. These include Filter, LastPeriods, TopCount, BottomCount, Ancestors, Descendants, PeriodsToDate, YTD and many others.
For example, the expression
LastPeriods(13, [Current Fiscal Week])
returns a set of 13 weeks up through the most current week. [Current Fiscal Week] is derived from the system clock date, which in turn is used in the Filter function to find the current date in the SQL Server date dimension table. The Ancestor function will return the Current Week based on the current date in the dimension table.
To avoid having to cut and paste MDX code every time we wanted to reuse an expression, we moved frequently used calculations, like [Avg. Daily Sales], to the Analysis Services server. We did the same for named expressions like [Last 13 Weeks]. Doing so makes these calculations and named sets available to any Panorama NovaView query.
When we started working with MDX in 1998, all we had was Microsoft documentation, which we found nearly impenetrable. Our rescue came in 2001, when Wiley published George Spofford's MDX Solutions with Microsoft SQL Server Analysis Services. The second edition of this book was published in 2006.
If Mosha Pasumansky's book, Fast Track to MDX (co-authored with Mark Whitehorn and Robert Zare), had come out before 2002, our ramp-up to MDX would have been much easier. Mosha's website www.mosha.com has a wealth of information about MDX.
