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

• Windows Server on IA32 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

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.

              Value: %SystemRoot%\system32

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.

Sincerely,

Shreyansh