Connecting MS Excel to SAP HANA using MDX
“I can do that with Excel”. If I had been given a dollar for every time I heard that phrase back when I was implementing SAP solutions, I would be close to millionaire. Sure, MS excel is a powerful tool for small datasets and there are many users out there who can make wonderful things with it. What if those users could leverage the power of SAP HANA in their spreadsheets? I think magic could happen.
We can quickly enable the magic with Multi-Dimensional Expressions (MDX). The SAP HANA MDX Provider for MS Excel was actually released some years ago and is included in the SAP HANA Client (which can be downloaded from SAP Software Download Center).
It allows us to connect to our SAP HANA instance, query an existing Calculation (or Analytical) view and then display it in a Pivot Table or Chart:
The prerequisite for this is, of course, having an available Calculation View. In case this is not already available with, for example, SAP HANA Live views, I have built a quick tutorial to go from importing data from an MS Excel spreadsheet using SAP HANA Studio, creating a very simple Calculation View and then consuming it from MS Excel.
In the process, the tutorial shows to how to create two simple tables in a Core data Services file, so I am leaving the reference to primitive types from the help site so you can create your own entities and types.
Last but not least, a small warning for importing data into SAP HANA from an Excel spreadsheet: Sometimes, MS Excel shows a format but is internally saving something different. I generally convert the file to CSV and open it in a plain-text editor to verify the date formats are correct and there are no additional hidden decimals in my IDs, for example.
As always, looking forward to your feedback and hoping this will help you make your day easier!
Dear Subatin,
I have also many a time said, 'I can do this in Excel' & pulled the legs of Developers.
I will go through the tutorial over the week end.
From next week onwards, I will refrain from pulling their legs.
Thank you,
Thanks! Let me know how it goes 😉
Hi,
We are unable to find out the installation dvd's at sap download center.
Kindly check & help us to get the same.
Thanks
Hi Lucia,
Thank you for this great post!
Does it also work for calculation view with parameters? Have you had a chance to try?
Thanks,
Ceyhun
Hi, Ceyhun,
As Excel does not have the option for input parameters, it does not work (so it won't work with mandatory input parameters).
Cheers,
Lucia.
Hi Lucia,
Thank you very much for your quick response. By the time I’ve tried as well, unfortunately it doesnt work as you say.
I’d like to use Analysis for Office to connect directly to HANA calculation views with parameters.
Http connection to HANA works with Analysis Office but I think ODBC or JDBC connection could be faster than HTTP. Do you know any option other than the http connection to connect to HANA from Analysis Office?
Thanks a lot once again,
Ceyhun