Microsoft Excel offers several methods to 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
- Create a table or view on SAP HANA Cloud
- Download and install the HANA Client
- Microsoft Office 2019 or a Microsoft 365 subscription
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.
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.
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
- Create a table or view on SAP HANA Cloud
- Create an OData service which exposes your table or view
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
- Create a table or view on SAP HANA Cloud
- Download and install the HANA Client
- Microsoft Excel or Microsoft Power BI
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.
