SAP HANA on a whim: A Bing map in MS Excel from an anonymous OData service
Some time ago, I published a blog about connecting MS Excel to an SAP HANA calculation view using the MDX client. This time, I wanted to add a Map and answer to the question “What If I cannot install the MDX plugin in Excel?”.
Our integration is done with an anonymous OData service. The steps to configure and publish an anonymous OData service are covered extensively in this blog series about exposing predictive analytics into a Google Spreadsheet and the tutorial referenced within those series. So that piece is covered.
In MS Excel, it’s as simple as going to the Data ribbon, then From other sources and then choosing From OData feed:
Note: If you have the Power Query add-in, the wizard from the “New Query” menu is a bit more friendly and allows for basic authentication. The steps are pretty much the same.
Paste the URL to your .xsodata service (you can leave the sign-in options as they are, since your service is anonymous):
And choose from the tables you want:
Fill in the name and description of your feed in the next step and let the data in:
Now I have a table with cities, that can be mapped into the Bing Maps add-in from MS Excel:
Select the data from your table and click on the “pin” icon on the top right corner of the map add-in for it to include it as a source… Ta-da!
Wasn’t that easy?
As always, let’s stay in touch on Twitter or on LinkedIn
Also, thanks Gregor for tipping me into the anonymous piece.
thank you very much for this nice impression of Odata in case we want to use it with Excel.
Did you try the the Odata Service also with XSA? I tried it, but it doesn't work.
(The Odata Service is working.)
I just did a quick test and it is apparently failing because of the certificate, but could not find a way to tell Excel to skip ssl validation, are you hitting the same issue?
I also think that this connection needs the XML full format and that is not implemented in XSA. I have not had time to test any further. Would you please let me know if you figure it out? 🙂
was trying it more often in different ways, but it fails everytime.
Have really not a clue, what else I could do. I would open an incident but not sure, if this is officially supported by SAP.
But of course, this would be a very nice feature for our users.
Tried to ask the community: https://answers.sap.com/questions/326681/sap-hana-xsa-web-ide-odata-service-and-excel.html