Skip to Content

Background

For decades, Microsoft Excel remains as the tool of choice to many users for data analysis. Even In the modern BI era, the “Export to Excel” is a must-have feature to many BI tools. In HANA world, we could hear the question from time to time inquiring how to connect Excel to HANA. This blog aims at introducing a new way, which is also completely free, to connect Excel to HANA. It overcomes the limitations of existing methods, and can be a great companion to HDBStudio to improve the interactive HANA data query and analysis through Excel.

 

Review of existing options

Currently, there are these popular methods to connect Excel to HANA, all with help of HANA Client: 1) the ODBC way, 2) the MDX way, 3) the ODATA way.

Let’s have a quick look at these approaches and their limitations. The detailed walk-through steps can be found in plenty of guides online, and will not be repeated here.

The assumption here is that HANA Client is installed on Windows already.

ODBC way

The ODBC way requires setting up the ODBC DataSource leveraging HDBODBC driver. Then, Excel uses Microsoft Query to communicate with HANA through the ODBC driver. The data flow is

    [HANA] -> [ODBC driver] -> [Microsoft Query] -> Excel

The use experience is not ideal. First, we encounter such warning message from Microsoft Query. 

The user name and password information is saved in the “<connection_name>.dsn” files. The lack of encryption of passwords could a problem from IT security compliance standpoint.

Next, once we proceed, the “Table Options” dialog is rather difficult to navigate with all the unresizeble tiny areas, especially when there are a lot of schema to select:

Once we are at the next screen to define filters, we will notice there are fixed space for up to 3 filter criteria only.

Once we are done with the configuration and proceed to the last step to run the query, many times we could encounter such error message, which makes us wonder if Unicode can be handled properly here.

Another issue we noticed is that Microsoft Query complains with error “Data Truncated” when making ODBC connection to certain HANA revisions (including latest HANA Express), but some older revisions are fine. More investigation is required to identify the root cause, but for now, the fact is that there are cases the connection cannot even be established.

When connection and object configuration are defined successfully, the HANA data querying functionality feels OK. The challenge is mostly with the user experience, efficiency, and bugs like those shown above.

MDX Provider way

This approach utilizes Excel’s Data Connection Wizard to connect to HANA cubes e.g. calculation views and analytical views. It is a solid data provider which does not have those disruptive user experience issues encountered in the MSQuery/ODBC way, but by design it is for dimensional data analysis only, not for querying on content objects like tables/views/synonyms.

ODATA way

The ODATA way requires the setup of an ODATA project in HDBStudio and activate the ODATA service endpoint. Then, Excel can connect to the ODATA URI. By design, this is a development effort which runs like a small project, If there is requirement to access another object in HANA, we will go through this development and release cycle again. So, by design this is not meant to be a self-service style data exploration, but a project oriented delivery which requires delivery cycle. For the requirement of limited data exposure through managed interfaces, this is the best choice.

A New Way to connect Excel and HANA

There is another way to bridge Excel and HANA, using AecorSoft Reporting HANA Edition which is a free software, functioning as an Excel Add-In.

It is a small foot-print installation for Excel. Under the hood ,it utilizes HANA Client’s ADO.NET data provider to communicate with HANA system.

After installation, Excel will have a new ribbon menu

 

Define Connection

First step is to define the HANA system connection, by clicking the “Connection Manager” button.

Define Task

Next, use “Report Task Manager” button to bring up the dialog for Task, proceed with “New” button to start defining a new task. Right now, all three catalog objects (Table, View, Synonym) are supported.

Select the connection just created, choose the object type (table or view or synonym), specify the object name (use wildcard if needed), and click “Search”.

Here, we can browse the basic metadata information of the object. Highlight the object we want to work with and click “Finish”

Task configuration and definition in Excel pane

Now the object metadata is brought to the Excel pane like this. It is also a great way to inspect the object column type and length information.

If the HANA object columns has Comments defined in its metadata table, then they will show up here as descriptions. The columns in the “Column Order” section can be re-arranged through drag-n-drop.

The task name can be renamed by double-clicking on the “Task Name” text box.

Filter

Filters can be defined through right clicking on the field either in the “Metadata” section or the “Column Order” section.

Once defined, it looks like this in the Filter section:.

Load Data to Excel

Once everything is defined, the last step is to simply to click the “Load to sheet” button.

During data loading, the progress is shown in the bottom of the pane .

If users don’t have authorization to view data, then there is error message stating the insufficient privilege. The security and authorization depend on the actual security model defined in HANA.

 

Local storage of task and connection information

There are two ini files under %appdata%\Roaming\AecorSoftReporting folder:

  • AecorSoftHANATasks.ini
  • AecorSoftHANASourceConnections.ini

The passwords are encrypted.

 

Conclusion

Here, we have reviewed the existing Excel/HANA integration options and.a new way of connecting Excel to HANA. Please share feedback or feature request in comments.

To report this post you need to login first.

19 Comments

You must be Logged on to comment or reply to a post.

    1. Chun-Hui Zhu Post author

      Hi thanks for the inquiry and suggestion. Yes the software (AecorSoft Reporting HANA Edition) mentioned here is from my company AecorSoft. It is a completely free software to share with the community. Thanks!

      (2) 
  1. Michelle Crapo

    I really liked this blog. “We” have done some work where the users open an Excel spreadsheet and it is populated by the SAP database. They have to log on and then the data is there. “We” as in not  me, but a co-worker.

    This will come in handy when we move to HANA.

    Thank you!

    Michelle

    (0) 
    1. Chun-Hui Zhu Post author

      Thanks, Michelle! My company also developed the Excel and ECC/BW integration solution (certified by SAP). It is great to see the common interest in simplifying Excel and SAP integration!

      (1) 
    1. Chun-Hui Zhu Post author

      Can you please let me know your Excel version and message me or email me the screenshot of the error? If you use email, please send to info at aecorsoft dot com.

      (0) 
  2. Chun-Hui Zhu Post author

    For those who encountered “version mismatch” error, it is because of the existence of old HANA client 1.0. Please upgrade your HANA Client to 2.0.

    (0) 
  3. Ricky Chen

    Hi  Chun-Hui  Zhu,

    I’m Ricky from Taiwan. Should I use HANA Client for 2.0 or use HANA Client for excel to access hana, what’s different between Client for 2.0 and Client for excel.  Because I use Client for 2.0
    I can’t access hana from Excel
    Thank you

    (0) 
    1. Chun-Hui Zhu Post author

      Ricky, please use HANA Client 2.0. Please don’t use HANA Client for Excel because essentially the “HANA client for Excel” is only an MDX query support, which is one of the features from the full HANA Client,

      (0) 
      1. Ricky Chen

        Dear Chun-Hui  Zhu:

        I connect to hana from excel successfully, i use microsoft query to do some setting.
        Thanks.

        By the way, How can I contact you? do you have email? maybe i have the other question to ask you Sincerely.

        Ricky

        (0) 
  4. Former Member

    Hi  Chun-Hui  Zhu.

    I´m getting this error after upgrade the client to 2.0, “Cannot find libADONETHDB.dll.”, can you please help me?

    Thank you in advance.

    (0) 
    1. Chun-Hui Zhu Post author

      hi Monica, one possible reason could be that 32bit vs. 64bit thing. Your Windows is most likely 64bit, but your Excel could be 32bit. In this case, you will need to install all 32bit software, including the 32bit AecorSoft Excel Add-in tool introduced in this blog, and the 32bit HANA Client. We need to do more test in order to pinpoint the root cause. You can contact me directly via email: info at aecorsoft dot com . I can help you resolve this issue.

      (0) 
  5. Markus Alterauge

    Hi Chun-Hui Zhu,

    thanks for this post. And for the recommended tool. I spent various days trying to get the MS Query running always running into the “Data Truncated” error. This is more than anoying especially because there is no solution, not even a recommendation from SAP available. I found some posts on this topic that had not been answered but were silently archived, something I would not have expected from SAP, very very poor SAP!!!

    Greetings

    Markus

    (0) 
    1. Chun-Hui Zhu Post author

      I don’t have IBP cloud to test this. But if the firewall is configured properly (e.g. default port 39015) to allow external application to access, then it should work. In other words, if you can use HDBStudio to access that particular HANA, then this tool will work too.

      (0) 

Leave a Reply