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...

The MDX Query Language

I came across an interesting blog by Dejan Sarka http://blogs.solidq.com/EN/dsarka/Lists/Posts/Post.aspx?List=871e778d%2D7617%2D45b7%2Da6ce%2D7b8215ed5aea&ID=111, where he talks about the MDX Language. Dejan’s theme is that the “MDX Language is Underestimated” and that “it is time for advanced BI implementers to learn MDX.” I agree with Dejan in that there is a lot of potential for people to write their own MDX queries directly against products like Microsoft Analysis Services, or SAP NetWeaver BI (SAP BW). I especially think that there is an opportunity for SAP customers to do more with BW. SAP seems to be making it easier to do this, and it has been investing heavily in its MDX capabilities within BW. SAP’s SDN www.sdn.sap.com is a good source of information and a place to ask for assistance. Also, George Spofford’s book “MDX Solutions” http://www.amazon.com/MDX-Solutions-Microsoft-Analysis-Services/dp/0471748080/ref=sr_1_1?ie=UTF8&s=books&qid=1204305023&sr=1-1 is probably the best source of MDX information out there today.

Microsoft Excel 2007 works natively with SAP BW

Here is a link to a blog post from Thomas Zurek at SAP: https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/8483 Thomas briefly talks about some MDX work that was done in SAP BW to better support Microsoft’s Excel 2007. Note that Thomas is a Development Manager at SAP working in the BI area on the SAP NetWeaver platform, so he definitely has the inside track.

2007 Reflection and Industry / BI Predictions For The Year Ahead

When I reflect back on 2007, it was certainly a year of change in the BI industry. The three largest BI vendors – Business Objects, Cognos and Hyperion – were all acquired by some of the largest software companies in the world. Additionally, Microsoft shipped its new performance management product – PerformancePoint Server. IBM also entered the MDX space with the latest release of its Cubing Services product.

So, what will happen in 2008? 2008 will most likely see further M&A activity. However, the biggest story of 2008 in the BI space should be integration. The SAP acquisition of Business Objects and the IBM acquisition of Cognos will complete in Q1, and I am eagerly waiting to see each company’s new product roadmap.

In the OLAP space, Oracle acquired Hyperion, including its Essbase product. While Hyperion applications work with multiple data sources, they’ve tended to be better tuned for Essbase. Essbase is a major component of Hyperion’s “secret sauce”. Hyperion  controls Essbase’s development direction and tuned its applications to really perform with Essbase. A tightly integrated solution that worked very well was the obvious result.

I expect to see similar tuning of Business Objects on SAP and Cognos on IBM. In the roadmaps that these companies will soon produce, other than branding, I initially expect to see Business Objects applications work better on SAP BW and Cognos applications work better on IBM DB2. This will be a big win for Business Objects customers that have SAP backend systems and Cognos customers that have IBM backend systems.

I also see Business Objects, Cognos, Hyperion and Microsoft applications continuing to work well in heterogeneous environments. Virtually every enterprise customer has multiple systems from different vendors. Any company selling applications must guarantee that those applications will work across multiple data sources. For example, if you are using Crystal Reports, you do not need to fear in the future that Crystal Reports will only work against SAP BW or R/3. Rather, Crystal Reports will continue to work against Oracle, SQL Server, DB2, etc. The advantage will be most apparent if you are a SAP customer. In this case, I expect Crystal Reports will work even better against data sources like BW because now the Crystal Reports developers will be able to talk directly to the BW developers, due to the groups working for the same company. I believe that this will be the story of 2008 – how will these vendors’ products better integrate?

I also see 2008 as being a good year for the MDX Query Language. With all the industry consolidation going on, every software vendor in the BI space will need to maintain that its products are open. As such, open standards will be key, and in the OLAP space, the MDX Query Language is the de facto standard. Oracle/Hyperion, IBM/Cognos and SAP/Business Objects will all need to continue to support this OLAP API standard, and each will need to ensure that its future MDX support is addressed in its roadmap.

I think it is evident that Microsoft Excel will continue to be the most used application in BI. Excel is the most prevalent MDX client in the market today, and it will continue to be so for the foreseeable future. Microsoft made many enhancements to Excel Pivot Tables in Excel 2007, and as Excel 2007 adoption continues to increase this year, all of the OLAP server vendors will need to keep up. Many companies, such as Hyperion, have Excel add-ins for their OLAP servers. Historically, the add-ins provided functionality that these companies felt was better than what Excel provided natively. Similarly, the add-ins also provided Excel connectivity to the companies’ proprietary OLAP interfaces. However, as the OLAP functionality within Excel matures (we saw a big jump in Excel 2007, and I expect that the next version of Excel will raise the bar even higher), the need for an add-in will drop. As such, more users will want to be able to use Excel’s native Pivot Tables functionality against products like Essbase, and as a result, this will push the OLAP server vendors to improve their MDX support.

Business Objects Insight 2007

I just got back from the Business Objects Insight Conference and wanted to talk about their new OLAP product, Voyager. Before I go on, I want to disclose that my company, Simba Technologies, does work for BOBJ and we are a BOBJ Technology Partner. Also, what is written here is only my opinion.

A side comment: The big question hanging over everyone at the BOBJ conference was “What does the acquisition by SAP mean?” It was interesting that the BOBJ people were told not to comment on this. So, even though I know a lot of people at BOBJ, no one was talking. What was interesting was at the keynote, they had a video from Henning Kagermann, the CEO of SAP. Basically, Henning’s message was that SAP was happy to acquire BOBJ and would be running it as a separate group within SAP. BOBJ’s CEO, John Schwarz and BOBJ’s Founder, Bernard Liautaud, both also spoke in the keynote and also said the same thing. They said they are excited to be part of the SAP Group. The reality is that this deal has not yet closed at this time, so the two companies must still operate as independent entities. Everyone is figuring this deal will close in Q1 of 2008; my guess is don’t expect a lot till then.

Many of you know that BOBJ has a new OLAP product called Voyager - http://www.businessobjects.com/pdf/products/queryanalysis/ds_businessobjects_voyager.pdf. Voyager has been around for about a year now and it is an interesting product. It is a good offering from BOBJ in the OLAP space. BOBJ has had a lot of success in SQL reporting and if they keep investing solidly in Voyager, I expect they will have a lot more success in OLAP/MDX reporting and analysis. Previous to Voyager, BOBJ’s OLAP solutions were based on Crystal Reports and Web Intelligence with OLAP Universes. Voyager does more – it is an AJAX client and it also allows you to access and analyze multiple OLAP sources.

The Voyager session that I attended was titled “OLAP Analysis with Voyager – What’s New, What’s Planned”. It was presented by John MacGregor (Product Manager) and Ian McAlpine (Program Manager). They talked about the product and gave a very nice demo. They explained a lot of the features of the current product and talked about futures. It is interesting that they plan on supporting AIX and Linux. What is also interesting in this product is that “Supported OLAP databases include those from Microsoft, SAP, IBM, and Oracle (including Hyperion Essbase).” Being a connectivity person, I find this interesting. Voyager is an OLAP client from the largest BI products company that will support all of the major MDX OLAP servers. If you read my previous October 24th blog posting about MDX being the de facto standard for OLAP and multi-dimensional servers, you will understand why I find Voyager’s connectivity story interesting. Voyager will have native MDX connectivity to more MDX OLAP servers than even Microsoft Excel. Anyone wanting to have an OLAP client that works in a heterogeneous MDX server world will find Voyager to be something that they should definitely consider.

Yes, there are other products out there like Panorama that support MDX across all the major OLAP servers (http://www.panorama.com/news/news/archives/2007/aug-20-2007.html), but having a company the size of Business Objects doing this definitely is a game changer. If SAP continues to support Business Objects as an independent division and keeps investing in Voyager having open connectivity to all MDX data sources, I believe we will have achieved with MDX in the multi-dimensional world what we have with SQL in the relational world – true standards-based interoperability between clients and servers from different vendors.

One other thing that I also enjoyed during John and Ian's session is that they asked the audience for feedback on what additional features and functionality customers were looking for from Voyager. A number of people threw out ideas, and the discussions about Voyager futures continued after the session with a few people for a good 15-20 minutes. It is nice to see people who enjoy what they do and are so willing to listen to what their users and customers think.

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.