How to access HANA using Excel?
We have seen many queries in SDN about accessing HANA using Microsoft Excel. Many of us were facing lot of issues/error while installing ODBC drivers, registering MDX library and Connecting to HANA in excel.
Scope of this blog is just to help you to setup the HANA connection through excel.
Here is my configuration which most of you will have and face many issues in creating ODBC connection.
OS: Windows 7 64 bit
Office Version: Office 2013 32 bit
HANA Studio: Version: 2.1.15
Below are some steps( Its very simple).
Step1: Download SAP HANA Client from service market place( HANA Studio do not provide any libraries to connect HANA from other clients. For example. Excel, JAVA UI, etc). It is just user interface for accessing HANA artifacts. Don’t download HANA Client 64 bit.
Installations and Upgrades – H“ –> SAP In-Memory (SAP HANA )“–> HANA Platform Edition“–> SAP HANA PLATFORM EDITION“–> SAP HANA PLATFORM EDIT. 1.0 (Support Packages and Patches)“–> Entry by Component“–> HANA client
Download latest version of below OS and install. This also works on Windows OS 32Bit
Step2: Make sure your HANA server is Up and running. Enter your URL in browser as http://184.108.40.206:8010 here instance is 10 and host is 220.127.116.11
Step3: After installing HANA Client, ODBC driver for HANA would get installed automatically. You can notice this while installation.
To make sure, please follow below steps.
–Go to Control Panel –> Administrative Tools –> Data Source(ODBC) –> This is ODBC Administation Console
Step4: Create ODBC DSN for HANA as below.
–Click on Finish after step 3 and enter Connection name, host and port details and test.
Hint: To get your hostname and port, go to Systems view -> Right Click Properties -> Database User Logon -> Additional Properties
Step 5: After step 4, you will see one new entry in Data Source list of ODBC Administration Console. We are half way now. Close all the windows 🙂
Step 6: Map your system files/libraries to local machine ( This is in lay man understanding).
Go to start and enter regedit change the directory path as shown below.
Step 7 : Now you just find the SAP HANA ODBC MDX Library which is nothing but “SAPNewDBMDXProvider.dll” . We just need to register this library so that office can access HANA DSN using this library.
Go to : C:\Program Files (x86)\SAP\SAP HANA Database Client for MS Excel\SAPNewDBMDXProvider.dll
If you do not find above file then you can re install the HANA Client and try again to find this file.
Step 8: You can register the MDX library manually without using software “HANA Client for Excel”
Go to Start and Enter CMD . Right click and run as administrator
To register HANA MDX Library you need to execute windows registry with file name as below.
C:\Windows\system32>regsvr32.exe “C:\Program Files (x86)\SAP\SAP HANA Database Client for MS Excel\SAPNewDBMDXProvider.dll”
Step 9: Open excel and go to DATA ribbon and click on From Other Sources -> From Data Connection Wizard –>Others/Advanced ->
Step 10: Connect to HANA : Enter all the system details and login credentials and access your artifacts.
I hope now you will be able to access HANA from your excel.
Thanks for visiting this blog and looking forward with your suggestions and comments.
Sorry to say this, but this is just another blog post that doesn't add much value to the installation guide documentation.
Besides: the ODBC driver is not required, when you want to use the MDX driver.
What is the proceedure then?
Thanks for the hint that the HANA Client is still available. I thought it is discontinued. Unfortunately I did not see the Option: Entry by Componen. But using the search function did finally lead to success.
i try to follow your step to do setting in ODBC. if using MDX to access HANA,this process is OK. But, in this way we can NOT using SQL language such as select. How can i using SQL script？
the ODBC way has some issues. I described the detail in my blog.