On MDX, Briefly

I've worked with MSAS a bit and I've had engineers who worked for me tell me things that confirm my brief experiences as I got further from the technology. 

Most people who get up the rather steep curve for MDX admire it for its elegance and prefer it to SQL on that basis. Nevertheless the experience is that it is generally not worth it to learn the language if you have appreciable experience in SQL. 

If you have become something of an expert in tuning databases in the generations of products before the Greenplum and Vertica days, say with Essbase, Microstrategy, Teradata, Oracle Express or Sybase ASE, then you will have some familiarity with arcane 4G languages and dialects of SQL without many cross-product similarities. MDX was the language destined to solve all that, a sort of OLAP Esperanto. Unfortunately, the number of programmers deciding to hack SQL to perform against this and that sort of schema tended to dominate the few cross-platform specialists. In the end, it is my opinion that the lack of a predominating visualization stack obviated the need for widespread MDX adoption. As fat visual programming OLAP clients matured, enterprise customers began demanding thin clients. As full featured stacks became available, developers wanted LAMP, and so on. Now we are met on a battlefield testing whether PHP and MySQL will long endure, with people like me wondering how they ever got started considering the maturity and performance of products like Sybase and Essbase.

My direct experience is that flatly, for applications of any sophistication, on MSAS, stored procedures with T-SQL always performed better than their functional equivalents in MDX. In their aborted product stack, Performance Point, Microsoft engineers created a middle language PEL(?) that would supposedly choose which language was best suited for a task and then generate that code. All jokes about code-generators aside, my engineers always had to second guess PEL and ended up writing it all in T-SQL. So as a practical matter, there was no sense in learning MDX if you had already mastered T-SQL on the platform for which MDX was specifically designed. It would have been nice if MDX performed with speed commensurate with its elegance, but it simply didn't. This was two years ago. 

I know MDX lives on in Essbase but that Essbase expresses it differently than does MSAS. It does rather boil down to 'it depends', because language to language different platforms have different strengths, etc. 

I expect that we will not learn the definitive answer to this question until there is a shakeout of DBs that survive the transition to cloud infrastructure. And in that regard I think a Greenplum or a Vertica or a Hadoop-based solution will win out. In other words it won't come down to the semantic layer. The market never forced it to because there is no real OLAP standard (chicken or egg?). 

In the end I say MDX iff you love MDX.

Comments

[Chris Webb]

While I agree with your comments on the difficulty of MDX and people's preference for SQL, I would like to make one point: when you say "It would have been nice if MDX performed with speed commensurate with its elegance" you're confusing the language (MDX) with the performance of the platform it's running on (SSAS). To say MDX is 'fast' or 'slow' is just wrong, it's like saying SQL is a 'fast' or 'slow' language - it's the database, not the query language, that's the problem.

The sad thing is that from what I saw, the PerformancePoint team and users of that product never knew enough about SSAS cube design and MDX to be able to get the best possible performance. And certainly the SSAS engine has not been, historically, tuned for financial applications but more the non-financial applications that SSAS is generally used for (which do involve large amounts of data, and are complex, but just different from financial apps). That said, with SSAS 2008 I do think the platform is now tuned for financial apps, and I've seen some very successful and fast financial apps implemented on SSAS 2008; unfortunately PerformancePoint is not around any more to take advantage of it.

[Cubegeek]

I agree that there were a handful at best of people who might have made MDX perform better in finapps on the Microsoft platform. It's not entirely fair to say that MDX was designed for SSAS - it is the standard OLAP language, but I'm not sure whether it has been adopted to the next generation of multidimensional databases. You're right, MDX isn't really the issue - it's database design at the heart of performance.

I'm of the opinion, of late, that the differences between the various 'single server' technologies are not going to add up to much. There will be an ample supply of sharp people to make these technologies perform. My question is whether or not finapps will scale to the next level - if we will be able to conceptualize applications that take advantage of the new scalability of multidimensional tech.

[George Spofford]

To amplify the point- you need to marry execution engine, compiler/optimizer, and storage model to get good performance. MDX succeeds in part because MS can apply it to its specialized storage or its own ROLAP form, Oracle/Hyperion to its own specialized forms (block-storage, aggregate storage, hybrid ROLAP), and other vendors to their own forms. MSAS performance goodness and badness is linked to how they set things up for the use cases they were interested in, Essbase the same. As with RDBMS products and SQL, you design a database differently and write your SQL differently based on the actual product/storage model you're using.

@Cubegeek: Part of the difference is that Microsoft has taken a big step beyond the semantic model of the original MDX, even within the same syntax, and gets great gains from it. There are many different semantic expansions that could be undertaken, but so far it seems like there is Microsoft's direction and the original form. Hyperion chose not to do something like that, nor to really expose much of their distinctive OLAP benefits through MDX. When I worked there I put a bunch of thought into the topic, and I'm sure it comes up today here and there too.

[Cubegeek]

It's extraordinary that you posted here today, because I have *just* been thinking about what it might take to develop a generic multitouch thick client for multidimensional database queries and navigation. In consideration of that, why reinvent a semantic layer? So I've actually got a good reason to understand the ways and means of MDX once again. I'm at a conceptual stage right now, just starting to put some ideas on paper, but I like the idea that there is a language out there that I can learn in order to begin thinking about a new navigation paradigm.