Skip to Content
Author's profile photo Former Member

How to access HANA using Excel?

Dear Friends,

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://99.99.100.100:8010   here instance is 10 and host is 99.99.100.100

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

        HANA ODBC 1.png

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

        HANA ODBC2.png     HANA ODBC 3.png

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.

              Value: %SystemRoot%\system32

          HANA ODBC 4.png

          

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

             HANA ODBC 5.png

          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”

       HANA ODBC 6.png

Step 9: Open excel and go to DATA ribbon and click on From Other Sources -> From Data Connection Wizard –>Others/Advanced ->

             HANA ODBC 7.png

Step 10: Connect to HANA  : Enter all the system details and login credentials and access your artifacts.

             

              HANA ODBC 8.png

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.

Sincerely,

Shreyansh

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Lars Breddemann
      Lars Breddemann

      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.

      Author's profile photo Thorsten Niehues
      Thorsten Niehues

      What is the proceedure then?

      Author's profile photo Thorsten Niehues
      Thorsten Niehues

      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.

      Author's profile photo Weiwei Weng
      Weiwei Weng

      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?

      Author's profile photo Chunhui Zhu
      Chunhui Zhu

      the ODBC way has some issues. I described the detail in my blog.