MDX is MDX…, isn’t it?

November 5, 2010 facebooktwittergoogle_plusredditpinterestlinkedinmail by in Business Intelligence   Interoperability   MDX   Multi-Dimensional Data Connectivity   OLAP   SQL  

“The wonderful thing about standards is that there are so many to choose from.”   You’d think that if everyone at least agreed to use the same standard, everything would be copacetic and “just work,” right?

If you really thought that, you clearly are not familiar with the software industry.

Let’s take SQL, for example.   The SQL language was adopted as a standard by ANSI and ISO over 20 years ago, yet even today SQL code can rarely be ported between database systems without modifications since implementations of SQL often don’t support the same features – even some basic features of standard SQL!

Popular opinion often accuses vendors of doing this to lock their customers into their particular solution.  More generally this happens because either the standard is very complex and not all parts are needed, or the standard is not specific enough in certain areas leaving vendors to their own interpretation – or both!

MDX is a newer database query and calculation language designed specifically for multidimensional databases and OLAP cubes, created by Microsoft in the mid-1990s.  It too has undergone a number of revisions over the years, coinciding with new versions of Microsoft Analysis Services, the largest revision being MDX2005.

Even though MDX is not an open standard (it’s a Microsoft defined specification), it has been adopted by a wide range of OLAP vendors to the point where it is now the defacto standard for OLAP systems.  But, just like SQL, not every vendor implements it the same way.

Outside of Microsoft, Simba is the leader in MDX connectivity knowledge.  We are routinely connecting diverse MDX emitting clients to various multi-dimensional data sources.  The issues we need to overcome include determining what functions the MDX client uses, what information the client expects back, what protocols the data source supports, and how to extract the requested data.

In addition to Excel, we recently previewed SAP BusinessObjects Enterprise XI Voyager 3.1  connecting to an Oracle 11g OLAP database, as well as Cognos Analysis Studio 8 connecting to an Oracle 11g OLAP database.   As I mentioned above, when using MDX and SQL from multiple vendors, those clients and that database do not natively communicate to each other.  We have been able to accomplish this with Simba MDX Provider for Oracle OLAP, and if you are curious to see it in action, I have put together a brief demo.

I don’t think the issue of standards is going to get better any time soon.   New standards will continue to emerge, existing standards will continue to evolve, vendors will continue to implement the standards as they interpret them or just the parts that they need to accomplish their task… and end users will continue to choose solutions from multiple vendors and expect them all to work together seamlessly.   I know I do.

That means it will continue to be fun times ahead for those of us who like to roll up our sleeves, poke around under the surface, and see how things work.  Where else can we add connectivity between SQL and MDX applications so that they come together in a useful way, where the whole is greater than the sum of its parts?  

m4s0n501