Simba Technologies' CEO Corner

SAP NetWeaver BI (SAP BW) and Microsoft Excel 2007 – MDX Connectivity

I was poking around the SAP Developer Network (http://www.sdn.sap.com) website this weekend and found a presentation entitled "SAP NetWeaver BI 7.0 Native Microsoft Excel 2007 Integration"
(https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/7083c7d3-d1ab-2a10-08ae-c8470148c7c2). Although these slides are dated January 2008, they are worth looking at. Slides 3 and 4 talk about the competitive positioning of Excel 2007 and the business case for Excel vs BEx Analyzer. Slides 5 and 6 show some architecture. In slide 6, I would like to point out that irrespective of how you access NetWeaver BI – ODBO, XMLA, or BAPI – you will hit the same MDX Processor. Many people have asked me if there are advantages of one API vs another and my answer has been that if it is just about the MDX, there is no difference because it is the same MDX Processor that underlies all the different APIs. ODBO and XMLA are industry standard APIs whereas BAPI is an SAP specific API. BAPI does have some slight advantages in that it exposes things that are SAP specific but again, these are minor. When it comes to supporting the MDX Query Language, the SAP MDX Processor has been upgraded to better work with Excel 2007. Slide 9 tells you more about what SAP Support Packages are needed to enable the Excel 2007 ODBO/MDX connectivity.

IBM Cubing Services - MDX and ODBO - Excel Pivot Tables

In case you missed it, on May 6, IBM announced support for OLE DB for OLAP (ODBO) in their Cubing Services product.  You can read the official notice at http://www-01.ibm.com/common/ssi/rep_ca/3/897/ENUS208-113/ENUS208-113.PDF.  DB2 Warehouse edition is now called InfoSphere Warehouse.

In October 2007, IBM shipped MDX Query Language support in this product.  However, you had to access the MDX functionality via a proprietary interface.  At the Information OnDemand conference in October 2007, IBM had a demo of Business Objects Voyager working with Cubing Services.  Voyager is an OLAP reporting tool from Business Objects which connects to MDX data sources.  Voyager works with Microsoft Analysis Services, SAP BW, and some of the Business Objects Performance Management applications.  It was nice to see Voyager working with Cubing Services, albeit using a proprietary API.  Now Cubing Services supports ODBO so it is possible to connect any ODBO client to Cubing Services.  I have not yet played with this new version of Cubing Services.  I hope to soon.

One thing I am told is that Microsoft Excel works with Cubing Services via ODBO and MDX.  However, IBM's MDX Query Language implementation is what I call the MDX 1999 variant.  The MDX Language Specification is part of the OLE DB for OLAP Specification which Microsoft last published in 1999, therefore I call it MDX 1999.  Microsoft has of course added a lot of extensions to the MDX Query Language and the MDX supported by Microsoft Analysis Services 2005 is what I call MDX 2005.

Excel 2007 uses the MDX Query Language to connect to OLAP data sources.  Excel 2007 is an adaptive product - it will adapt to the MDX supported by the underlying data source.  Therefore, if you connect Excel 2007 to Microsoft Analysis Services 2005, it will function differently than if you connect it to Microsoft Analysis Services 2000.  Excel 2007 is able to determine what MDX Query Language features are supported by the underlying data source and expose functionality accordingly.

Since Cubing Services supports MDX and ODBO, you can connect Excel 2007 to Cubing Services.  However, only the MDX 1999 variant is supported and Excel 2007 on Cubing Services will function like Excel 2007 on Microsoft Analysis Services 2000.  So far, only SAP BW 7.0 and Microsoft Analysis Services 2005 support the MDX 2005 variant.  I am not sure of IBM's timeline for supporting MDX 2005.  Also, Cubing Services does not yet support XMLA.  I expect XMLA support to come relatively soon as this is technically easier to develop than ODBO and much easier to develop than MDX 2005 functionality.  If I was a betting man, I would expect IBM to announce XMLA at their next Information OnDemand conference.

Whither Essbase?

I always enjoyed the Hyperion Solutions Conference. One of the things I enjoyed at Solutions was R&D Central. This was an opportunity to meet the Hyperion engineers and see what they were working on. After Oracle acquired Hyperion, I was wondering what they would do to replace Solutions. I attended Oracle OpenWorld in November, but this was such a huge conference it was hard to really see much. The upcoming ODTUG Kaleidoscope 2008 Conference looks to have some interesting sessions on the Hyperion Products and especially Essbase. (http://www.odtugkaleidoscope.com/hyperion.html) Essbase is a strong product, backed by a strong engineering team. The Hyperion Essbase track at the Kaleidoscope Conference looks very good. There are keynotes from people like Robert Gersten and John Kopcke, where they will talk about the Future of Essbase and Where Essbase Fits within Oracle respectively. Sessions on the Aggregate Storage Options (ASO) and MDX also look good. I keep prodding the folks at Microsoft and Hyperion/Oracle about the XMLA Council. When Bill Baker at Microsoft and Robert Gersten at Oracle made the effort to work together on XMLA and MDX as an open standard, that was a huge step forward. It would be a shame if the new Hyperion/Oracle and Microsoft did not continue that effort. From the agenda of the Kaleidoscope Conference, MDX is mentioned, so it looks like Hyperion/Oracle is still supporting the MDX Language. I am curious to know what more Hyperion/Oracle plans...

MDX - History and Evolution

I'd like to highlight some MDX history, specifically how it has been adopted by many companies on the server side as an access means to OLAP/multi-dimensional data and has become the de facto query language for OLAP/multi-dimensional servers today. Having had an opportunity to collaborate with many companies over the years, I've seen some very interesting trends in MDX support.

Initially, server-side MDX was supported by companies like Microsoft, SAP, SAS and Applix. After which you had a lot of other companies support MDX, such as ALG Software, INEA Corporation and MIS. This was all good. However, for MDX to become a standard, it needed even broader support.

A big turning point for MDX was when Hyperion partnered with Microsoft on MDX and formed the XMLA Council. This was significant because Essbase was (and still is) a major name in the OLAP server market. IBM used to resell Hyperion’s Essbase as DB2 OLAP, and when Essbase added MDX support, IBM customers got it as well. This brought MDX to yet another level in terms of becoming a standard.

By around 2002, when Hyperion shipped a version of Essbase that supported MDX, on the server side, MDX was now supported by pretty much everyone except for Oracle. Without Oracle, there was a big hole, but we were close to broad support. Oracle, of course, is a huge player in the database market.

With all the industry consolidation, a lot has changed. Cognos has acquired Applix. Systems Union acquired MIS, and then Systems Union was acquired by Infor. Oracle acquired Hyperion. Business Objects acquired ALG Software, as well as Cartesis, which had previously acquired INEA Corporation. Also, IBM stopped reselling Essbase a few years ago.

So, we add Oracle (through their Hyperion acquisition), but we lose IBM because they no longer resell Essbase. The interesting news is that IBM is about to ship their DB2 Viper 2 product at the end of October, and what did you expect? IBM supports MDX in the product.

So, where does that leave MDX on the server side? From what I can see today, everyone has it:

• Microsoft Analysis Services
• SAP BW (also called SAP NetWeaver BI)
• Oracle Essbase
• Business Objects
• Cognos Applix
• IBM DB2
• Infor MIS Alea

There are also a lot of smaller players in the OLAP space, such as Panoratio and Descisys, and they also support MDX on the server side.

Therefore, MDX is the de facto query language standard for OLAP/multi-dimensional servers today.

Java and XMLA? You Bet!

I have had a number of questions recently about Java and XMLA.  People have asked if you need to know .NET to work with XMLA.  The answer is that XMLA works in both the Java and the .NET worlds.

We have seen XMLA Providers built using .NET and IIS and we have seen XMLA Providers built using Java and JBoss.  The nice part about XMLA and MDX is that they are not platform dependent.  So, on the server side, yes, you can implement XMLA in both Java and .NET.

What about XMLA consumers?  Again, the answer is that Java works well for XMLA consumers.  In the .NET world, Microsoft has implemented ADOMD.NET which is an object framework to connect to Microsoft Analysis Services.  Microsoft could just as easily have created ADOMD.J – i.e. a Java based object framework to connect to Microsoft Analysis Services.  In fact, if you are interested in something like ADOMD.J, send me a comment, I would love to discuss further.

What's Happening with XML for Analysis (XMLA)

Recently a lot of people have asked me about XML for Analysis (XMLA) and what is happening with it.  While I do not speak for Microsoft or Hyperion (now Oracle), I can give you some background to help you make sense of what is happening.  First, here is some history about XMLA:

• In December 2000, Microsoft issued a Beta specification for XMLA
• In April 2001, Hyperion and Microsoft jointly issued the XMLA 1.0 specification
• In September 2001, Hyperion and Microsoft initiated the XMLA Council, of which there were ultimately about 30 member companies including my company, Simba.  Other notable companies involved in the XMLA Council were SAP AG, SAS, Business Objects, and Cognos.
• In November 2002, the XMLA Council has their first interoperability session where the server and client vendors came together and tried to connect their various products using XMLA
• Also in November, 2002, Microsoft and Hyperion jointly issues the XMLA 1.1 specification
• A lot happened around XMLA in 2002 and 2003 and things look positive
• Around 2004, the energy around the XMLA Council seemed to fizzle out and not much has happened since

Not much is happening with the XMLA Council.  What does that mean?  Well, in my opinion, that does not mean that XMLA is dead.  In fact, there seems to still be a lot of innovation around XMLA and MDX.  You just need to look for it a little bit harder.

Second, you should know that Multi-Dimensional Expressions (MDX) is the language used by XMLA and OLE DB for OLAP (ODBO).  XMLA and ODBO are the protocols generally used to make MDX queries.  For those of you familiar with ODBC and SQL, consider that MDX is like SQL, ODBO is like ODBC, and XMLA is like JDBC.  I know, the purists out there will disagree with me, but my intent here is to help you understand XMLA and so I think this analogy helps.

OK, so MDX is the query language and XMLA and ODBO are interfaces by which you make your MDX queries to a multidimensional or OLAP data source.

Now, if you read my statement above, you will understand that XMLA and ODBO are just the interfaces by which you make your MDX queries.  Therefore, when people talk about interoperability and standards, the real interoperability comes from MDX.  XMLA and ODBO are simple enough that they are not the differentiators around interoperability and standardization.  MDX is the key.

So, let’s go back to the XMLA specification.  If you read the XMLA specification, you will see that it does not give you a specification for MDX but rather it refers you to the ODBO specification.  The ODBO specification was last published by Microsoft in 1999 and it is this document that gives you a specification for MDX.  MDX is the key to interoperability and the last time an MDX specification was published was 1999.

Now, back to the question, what is happening with XMLA?  The answer is that XMLA is pretty much standardized and not a lot is likely to change.  The question you should ask is, what is happening with MDX?  The answer to that is, lots!  Microsoft has made a very large number of extensions to the MDX language, which they always said they were allowed to do.  This is the key.  If you look at the latest version of Microsoft Analysis Services (version 2005), you will see that the MDX extensions that Microsoft has made are very extensive.  I personally refer to the MDX from the last ODBO specification as MDX 1999 and the MDX from Microsoft Analysis Services 2005 as MDX 2005.  Microsoft says MDX is MDX and that the extensions that they have made are theirs.  And, of course, Microsoft has every right to innovate and I commend them for doing so.  I do want to point out that for interoperability and standardization and since Microsoft Analysis Services is such a dominant product in the marketplace, you really need to support MDX 2005 in order to be a player in this space.

Consequently, MDX is alive and well and continues to evolve.  If you are trying to understand MDX, of course read the ODBO specification and then also look at Microsoft Analysis Services 2005.