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.
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.
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.
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.
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
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. 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.
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
- 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.
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
- SAP Managed Tags:
2 Comments
