PerformancePoint - It's all about MDX (baby)

"MDX is too hard, I just can't get my head around it"

"Oh, f*** it, I'll just write it in T-SQL; I'm good at T-SQL. Besides, the database isn't very big anyway, the performance difference won't be noticeable"

The above quotes, and others like them, are common. I've heard them uttered by both colleagues I've worked with and clients I've worked for. I'm not saying that MDX is easy, it takes time and patience to really get into it, especially if you've been immersed in relational DBs for half a lifetime. Nonetheless, if you're serious about BI and you're looking to do some PPS work in the near future then you need to develop a mastery of MDX. Period.

By mastery I'm not taking about [Measures].[Budget] - [Measures].[Sales] calculated members, that kind of MDX doesn't count in this context. I talking about the ability to write a complete, complex MDX SELECT statement freehand as you would T-SQL, understanding things like where it would be useful to use recursion, the GENERATE() function, create SCOPEing statements in a cube's MDX script etc.

I've been doing quite a lot of work with both PerformancePoint M&A and Planning in recent times and can say that if you don't feel comfortable in the MDX world then you'd better run. I'm not saying that if you don't know MDX then all is lost, not at all. You'll certainly be able to get by on all aspects of PPS without any real MDX knowledge. That is very much thanks to the MS developers and some intuitive UIs. If you don't "do" MDX you'll be absolutely fine with achieving the 80 part of the well-known 80:20 rule. However, MDX skills will account for a sizeable chunk of that remaining 20. And we all know how significant that 20 can be.

M&A MDX
A customer may want functionality to filter a dashboard by years. Based on the selected year a grid is displayed that contains the top 3 products sold by each of the top 10 stores based on the average sales amount for the previous 5 quarters from the 2nd quarter of the selected year. You'll need MDX skills for that.

Planning MDX
Budget figures need to be automatically apportioned down a hierarchy based on previous years' sales figures and their achievement of budget coupled with average sales numbers from the last 3 months. You'll need MDX skills for that too.

MDX cannot continue to be quietly swept under the carpet as many have done in the past. From a PPS perspective you'll get a long way without it but certainly not all the way.

Where to learn all this MDX goodness? Books, blogs & experience.

Books - Naturally, you should buy a copy of the MDX bible - MDX Solutions Second Edition. This is the MDX book, a must have (read my review). You can also try any of the other Analysis Services books on the market; they all cover MDX in a chapter or two, which should help to make a good start.

Blogs - Chris Webb and Mosha Pasumansky are without question the authorities here. Reading their blogs will also provide links to all sorts of other MDX stuff outside of what they write about. Don't just subscribe to their feeds, go to the blog sites and read all their posts from the beginning - tons of good stuff.

Experience - Write MDX, then write some more, don't stop. Once you think you're getting a handle on it here's a good test. Read this post that Chris wrote several months back on calculating debtor days. Pull the code into an MDX query in SSMS and pull it apart, take note of all that is happening. If you can understand all that goes on in there and why then I would say your MDX mastery is at a pretty high level.

UPDATE: Darren Gosbell reminds us about two more excellent MDX resources in the MSDN SSAS forum http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1 and the SSAS newsgroup: microsoft.public.sqlserver.msolap. I should have included these in the original post. Thanks Darren.

So, if you have been quietly avoiding MDX up until now then the time has come to come out of hiding and tackle it head on. There's only a few months before PPS hits the streets, there's also more performance improvements and other goodness upcoming in SQL Server 2008 SSAS. Carpe diem!