FREE email newsletters 
Confessions of an MDX Junkie
InfoManagement Direct, September 2007
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.
Advertisement
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.
Another terrifically valuable resource is the early columns written by Russ Whitney for SQL Magazine.
William Pearson has written dozens of insightful articles about MDX in his "MDX Essentials" column for the Database Journal.
Last, but by far not least, is Teo Lachev's Applied Microsoft Analysis Services 2005, published by Prologika Press. This is the definitive guide to building Analysis Services cubes. Teo runs a wonderful Web site, a great source of information about MDX, Analysis Services and Microsoft Reporting Services.
I hope that my examples have provided a basic understanding of how powerful the MDX language is, and how it can be used to add a wealth of intelligence to an analysis services cube and a BI front-end tool such as Panorama NovaView.
My last confession is obvious from the article: I'm a big fan of Panorama NovaView. Because it was developed by the same company that originated MDX, it uses MDX in dozens of ways behind the scenes to facilitate self-service reporting and analytics for casual users. And for analysts, Panorama NovaView unleashes the full power of MDX to address the most complex and thorny business problems. You can test drive Panorama NovaView and MDX with a free download of the system from Panorama's Web site.
Learning MDX and Analysis Services can be daunting, but it's really the only way to get the most value from your data warehouse. I encourage you to get started.
Larry Sackett, president and CEO of E.J. Barry Company, has more than twenty years of experience designing and implementing database marketing and business intelligence systems. He is a specialist in the use of data, technology and marketing best practices to increase revenues and profits. He may be reached at lsackett@ejbarry.com.
For more information on related topics, visit the following channels:
