Skip to Content
Technical Articles
Author's profile photo Maxime Simon

Access SAP HANA Cloud via Excel

Microsoft Excel offers several methods to directly access data on SAP HANA Cloud.
In this blog I will introduce 3 methods : Import data from HANA Database, Import data from ODBC and OData.

Import data from HANA Database

Prerequisites

Steps to connect

HANA Platform (on-premise installation) allows Excel users to connect to HANA via ODBO and execute MDX statements. This is not supported in HANA Cloud as described in note 2980910.
Therefore we will use SQL statements. Learn more about the HANA Client in How to Use the Client Interfaces with SAP HANA Cloud

 

Open an Excel workbook.
Within the Data tab, select Get data > From Database > From SAP HANA Database. 

This will open the connection wizard.
Insert your server, port as 443, and your SQL query.

If you don’t know the server, you can find your HANA Cloud server on the SAP BTP Cockpit.
Click on “Copy SQL Endpoint”. This will copy your HANA Cloud server and port.

Then, the wizard will prompt you for your SAP HANA Cloud user and password in the database tab. (Do not input a password for the windows tab)

Once you input the credentials, a data preview will show up.
See the preview below with dummy data. You can then transform or load the data.

On my laptop, the data was downloaded at a pace of ~ 2000 rows per seconds, for a total of 180.000 rows.  You have the option to manually or automatically refresh the data at any time. Now that the data is loaded into Excel, you can use all Excel tools, such as formulas and pivot tables.

Within the queries&connections menu, you can check the data source and selected columns. In my case column names are in Japanese.

Within the query properties, you can set up data refresh.

 

OData

Prerequisites

Steps to connect

Once you have created an OData service on the data you want to expose from SAP HANA Cloud, open an Excel workbook.
Within the Data tab, select Get data > From Other sources > From OData Feed.

Type the URL of the OData service you created on SAP HANA Cloud.

If you set up authentication, insert the credentials.

Then, you get a preview of the exposed data, which can be transformed or loaded directly.

The data gets downloaded to your local Excel client. You have the option to manually or automatically refresh the data at any time. Now that the data is loaded into Excel, you can use all Excel tools, such as formulas and pivot tables.

Import data from ODBC Source

Prerequisites

Steps to connect

The HANA Client also includes an ODBC driver which can be used to connect from Microsoft Excel and Microsoft Power BI.

First, open “ODBC Data Source Administrator” on your Windows client. Add a new Data source.

The “Create New Data Source” wizard will prompt you to choose a driver. Choose “HDBODBC”.

In the ODBC Configuration for SAP HANA, set a name for your data source.
Insert the Host (which can be found on the SAP BTP Cockpit) without port.
Insert the port 443.
Do not select “Multitenant”.
Select “TLS/SSL”.

Once your ODBC source is created, open an Excel workbook.
Within the Data tab, select Get data > From Other sources > From ODBC

In the wizard, select the ODBC source you created. Mine is called “HANA Cloud”.

Insert your HANA Cloud user credentials.

Then, you see a list of all schemas your user can access. Select the schema and table/view which you want to access. Click on “Load” to start loading data to your worksheet.

The data gets downloaded to your local Excel client. You have the option to manually or automatically refresh the data at any time. Now that the data is loaded into Excel, you can use all Excel tools, such as formulas and pivot tables.

 

There are other ways to access data in SAP HANA Cloud from Microsoft Office tools, which I did not introduce in this blog.
– If you want to use Analytics features and establish data access control, you can use the Analysis for Office plug-in to access SAP HANA Cloud via Excel. With this method, you can set-up row-level control on which data can be accessed by users.
– Partners also offer plug-ins to access SAP HANA data via Excel such as the one offered by Aecorsoft.

Thank you for reading, happy data exploration !

Maxime SIMON

Assigned tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jigang Zhang 张吉刚
      Jigang Zhang 张吉刚

      Thanks for sharing this, just realize that Excel already provide those options : D

      Author's profile photo Gregory Misiorek
      Gregory Misiorek

      Or dump the table into a CSV file, if live connection is not available for whatever reason.

      Question: how many ways are there of using a spreadsheet to populate a HANA table? a kind of roundtrip, if you like.