As SAP HANA gains more and more traction in the marketplace, a lot of people have questions about the MDX query language capability in HANA.
First, some background on the MDX query language. The MDX query language was part of the OLE DB for OLAP (ODBO) specification that Microsoft introduced in 1997. MDX was supposed to be a SQL like language for multidimensional or OLAP data. OLE DB for OLAP was supposed to be the OLAP extension for the OLE DB specification that Microsoft had introduced a few years earlier. At the time, there was no clear standards for OLAP access to data so Microsoft was trying to unify things like they did with ODBC. Microsoft continued to update the ODBO specification until 1999. Then, in 2001, Microsoft introduced XML for Analysis (XMLA). XMLA was supposed to be the web services and XML type interface to succeed ODBO. The XMLA specification did not include an update to the MDX query language specification. Rather, it referred back to the ODBO specification of 1999. Therefore, the last time that any specification of the MDX query language was ever published was 1999. Many of us call this MDX-1999. However, Microsoft continued to innovate and extend the MDX query language. Microsoft made a major leap in 2005 when they introduced SQL Server Analysis Services 2005. The MDX query language capability was significantly improved. Many of us call this MDX-2005. There have been further enhancements to the MDX query language since, but nothing as major as MDX-1999 and MDX-2005.
Back to SAP HANA and the MDX query language. SAP HANA supports the MDX-2005 version of the language. This is important. The major importance here is that Excel Pivot Tables has evolved over the years and to fully support Excel Pivot Tables, the data source must support MDX-2005. You can see this difference in products like Mondrian and Oracle Essbase. They both support MDX but they support MDX-1999. Therefore, Excel Pivot Tables does not work directly with Mondrian or Essbase and you need some sort of Excel add-in or adapter. SAP HANA is different. SAP HANA supports MDX-2005 and so Excel can connect directly to SAP HANA and you can use the full set of features in Excel Pivot Tables.
Christoph Morgen at SAP wrote a very good blog post about SAP HANA and the MDX query language entitled “Connect your MDX speaking BI Client to SAP HANA now!” In his post, Christoph provides some background and information on the MDX support within SAP HANA. He also announces the MDX query language certification for SAP HANA as well as arcplan Enterprise as the first product to receive this certification.
Some additional background on SAP HANA’s MDX query language capability is that it is based on the SimbaProvider SDK. Simba analyzed the MDX capabilities of Excel. Using this analysis, we understood how Excel connects using the MDX query language. We codified this into a specification for MDX-2005 and then used this as the core of the MDX query language capabilities in the SimbaProvider SDK. Therefore, any product built using the SimbaProvider SDK fully supports Excel Pivot Tables. As Excel has evolved over the years, Simba continues to be the expert on connectivity from Excel Pivot Tables to multidimensional or OLAP data sources. Products like SAP HANA and SAP BW have greatly benefitted from the work done by Simba and the leverage of using the SimbaProvider SDK.