Requirement: We want to connect HANA database system from Microsoft Excel. This is one of the most common client requirement, where they want to access HANA data from Excel and generate basic reports.
- Communication User: We need an HANA DB user with CATALOG READ or DATA ADMIN assigned.
- Network connectivity: HANA system is reachable from PC running MS excel.
Download SAP HANA ODBC client for MS Excel
Go to SAP marketplace site : http://launchpad.support.sap.com
Select 'Downloads' from drop down menu and search for "SAP HANA CLIENT".
Now follow the numbered steps and download the latest client media file.
Once the file is downloaded, we need to extract it. This file has extension .SAR(SAP Archive), to extract it we need SAPCAR utility.
If you do not have SAPCAR , just search for SAPCAR as you searched for HANA client, and download the SAPCAR utility. SAPCAR is similar to Winzip or WinRar that we have on our laptops, but specific to SAP Archive SAR files.
Copy both files on one Windows folder , and from command prompt go to that folder and run the command.
Execute following command to extract the downloaded HANA client .SAR file.
replace the AnyArchiveFile.SAR file name with the SAR file name of HANA client file name.
SAPCAR.exe -xvf AnyArchiveFile.SAR
SAPCAR -xvf AnyArchiveFile.SAR
Once you have extracted it , double click hdbsetup.exe to run the installer. This will launch the following installation wizard.
Install SAP HANA client for MS Excel
Once the executable client file is downloaded from SAP Service marketplace, then double click on it to install ODBC HANA client on our PC.
Configure MS Excel to fetch data from SAP HANA
Once the HANA client for MS excel is installed, start MS excel and open new workbook.
Go to "Data" > "From Other Sources" > "From DATA Connection Wizard"
We will get following connection wizard opened , now select "Other Advanced"
Now select "SAP HANA MDX Provider" , this option will only appear if the HANA ODBC client for excel properly installed.
Enter the HANA connection parameters and press "Test Connection". If the connectivity is established you will get "Test Connection Succeeded" pop-up.
Once we press OK , it will list down all your HANA schema along with the views created in SAP HANA.
Select the schema required and then press next. We will get following screen to save the connection parameters so that we do not have to create it every time.
Congratulations, you have successfully configured MS Excel - HANA ODBC connectivity.