Using Excel on HANA
From the very beginning, HANA has been shipped with an OLE DB for OLAP (ODBO) driver that allows ODBO enabled clients to connect and analyze analytic views in the HANA repository. Those analytic views are cubes that have been modeled with the HANA modeler (which is part of HANA studio).This blog provides some background and points to a nice demo video that has been created by our partners from Simba.
Excel is an attractive vehicle to take the power of HANA’s calculation engine to the end user for …
- it is ubiquitous on desktop computers, and
- it provides an analytic tool – called PivotTable – that allows simple slicing and dicing and as such some decent analysis capabilities that are available right away due to the ubiquity of Excel.
Technically, Excel generates and issues MDX statements that are submitted via the ODBO protocol to HANA – see figure 1. Native Excel 2007 On Top Of Netweaver BI 7.0 that has provided an ODBO driver for more than a decade.
Figure 1: Excel connecting via ODBO to HANA.
The demo video created by our Simba colleagues shows
- how Excel can be connected to a HANA instance,
- how to pick one of the existing multi-dimensional models (aka analytic views, cubes),
- a simple, albeit powerful example of an analysis on top of that analytic view.
Demo video
Soon, the white paper that is mentioned at the end of the video will be published in SDN. I will update this blog accordingly.
Thanks for sharing...it's a really nice video, an it's great that Excel integration is provided by default...but...what about Scripting Languages? Does SAP thinking on providing access for let's say Ruby, Python, PHP? Or the standard MDX libraries (if any...need to check) can be used for that matter?
Going to do some researching on my own, but would like to hear your thoughts -:)
Greetings,
Blag.
MDX is simply one of many APIs that HANA provides. W/o knowing too much about it I guess that MDX statements can be easily issued from PHP and the like. This should work similarly to SQL. In other words: MDX can be incorporated into some host language. So I suggest that you do some research in the internet on how to embed MDX into such scripting environments.
This blog is meant to focus on the bread-and-butter usage of HANA's MDX capabilities, namely the consumption via Excel.
Regards
Thomas
Thanks -:) Will check about MDX...not sure if Python have something like this, but if PHP or Ruby have them, then it could be ported (not easily, but feasible) to Python...will investigate...
Greetings,
Blag.
Thanks for the tip...having a Python API would mean that there's an entry point...so that's cool...but sure, documentation is kind of lacking these days...
Greetings,
Blag.
great overview of Excel Pivots. it's a great tool and hard to beat with dashboards, cockpits, cubes, etc. i'm really looking forward to get some details on the HANA connector part and would be delighted if i could point my humble Excel to any HANA SID out there in the cyberspace.
Best regards,
greg