Use Excel to query and analyze HANA data
/Background
For decades, Microsoft Excel remains as the tool of choice for data analysis and reporting. 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. Although there exist multiple ways of connecting Excel to HANA, they are still not intuitive, productive, and performing enough,
AecorSoft recently released AecorSoft Reporting HANA Edition (ARHE) as a FREE standalone product, to bridge Excel and HANA together. In this blog, we will review the functionality and limitations of existing Excel/HANA connection options, and have a detailed walk-through of ARHE with examples, to illustrate the key features which make ARHE stand out.
Let's take a look at the summary of option comparison first, and dive into details next.
Review of Existing Options
Currently, there are three popular methods to connect Excel to HANA: 1) the ODBC way, 2) the MDX way, 3) the ODATA way.
Let's have a quick look at these approaches especially their limitations. The detailed walk-through steps can be found in SAP guide, thus they won't be repeated here.
ODBC Way
The ODBC way requires setting up the ODBC DataSource leveraging HDBODBC driver from HANA Client. 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.
This could a problem from IT security compliance standpoint. By the way, the user name and password information is saved in the "<connection_name>.dsn files.
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). More investigation is required to identify the root cause, but for now, let's accept the fact 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. Robustness wise, it is a solid data provider which does not have those weird user experience issues encountered in the MSQuery/ODBC way, but by design it is for dimensional data analysis only, not for querying on catalog objects like tables/views/synonyms.
Function wise, this provider seems missing dimensions when fetching analytical views. Here is an example of an analytical view from demo content:
However, in Excel with MDX provider, we see this in pivot table:
It is puzzling where the dimensions go. Based upon such observation, we have to consider this as partially working only, not a mature solution.
Another issue is also the lack of password encryption. Here is the warning message:
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 engineering life cycle.
ARHE Way to Connect Excel and HANA
ARHE is a small foot-print installation for Excel. There is no pre-requirement for HDBStudio or HANA Client installation.
Once ARHE is installed, 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 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. There is no limit how many filter criteria can be defined.
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.
Choose a New Object in Current Task
To change to another object in same Task pane, click on the binocular button
to bring up the object selection dialog again
Recent Tasks
The shortcut to access the saved tasks is to use the "Recent Tasks" button on the ribbon.
Local storage of task and connection information
In ARHE which is a free version software, there are two ini files under %appdata%\Roaming\AecorSoftReporting folder:
- AecorSoftHANATasks.ini
- AecorSoftHANASourceConnections.ini
The passwords are encrypted.
One thing to keep in mind is that AecorSoft Reporting commercial version does not use ini config files, but uses encrypted local database to store the Task and Connection information, for extra level of security.
Privacy
User privacy is completely protected. The software itself does not have any contact with Internet: no matter uploading or downloading. The only "ping" of Internet is when user checks for update by clicking the "About" - "Check for update" button.
Conclusion
Here, we have reviewed the Excel/HANA integration feature from the AecorSoft Reporting HANA Edition software, and its advantage over other comparable options. ARHE can be downloaded for free from here.
In next blog, we will have a review of ARHE's future roadmap, including the HANA Content object (attribute, analytical and calculated views) support and direct SQL statement support.
Please share feedback or feature request in comments, through "Contact Us" link, or via direct email to info@aecorsoft.com.
