How to Integrate Microsoft Excel with SAP HANA
Microsoft Excel is measured as one of the most popular Business Intelligence (BI) reporting and analysis tools by several establishments. Business Executives and Specialists can associate it to SAP HANA database to create Pivot tables and charts for analysis. Microsoft Excel can connect to SAP HANA using the MDX language which is a type of multidimensional SQL which enables business professionals to manipulate and utilize the data the way they need to.
There are certain limitations to the incorporation of external reporting tools with SAP HANA. These restrictions are because of the relatively recentness of the SAP HANA products, and are being catered to with each sequential update to the software.
The limitations listed below are strictly for SAP HANA SP04 and may or may not apply to other versions of SAP HANA:
Hierarchies: These can only be created in Microsoft Excel and not in BusinessObjects.
Prompts: These can only be used in Business Objects BI4. Modeling views which utilize variables can be used in other tools; however, it is possible only if the variable has a default value failing which Microsoft Excel will report that the view has been “changed on the server”.
Steps to Integrate Microsoft Excel with SAP HANA:
- In order to integrate Microsoft Excel with SAP HANA using the MDX language, the user needs to first install the SAP HANA Client software. This is different than the Studio, and must be installed on the workstation where it is intended to be used by the client.
- Next, the user needs to install the appropriate version of Microsoft Office. They need to install the SAP HANA client software just like they installed the SAP HANA Studio, typically to the workstation’s default location. There won’t be any shortcut created on the desktop once the software download is finished, neither will there be a special entry in the “Start” menu.
- Next, The user needs to launch Microsoft Excel and click on the “Data” tab from the top of the excel sheet, then click on “From Other Sources” and then click on “From Data Connection Wizard”. A “Data Connection Wizard” window will pop-up where the user needs to double-click on Other/Advanced, then select “SAP HANA MDX provider” and select “Next”.
- Next, the SAP HANA Logon dialogue box will appear on the screen where the user needs to enter the host name, Instance, username, and password just like the ones they entered with the SAP HANA Studio.
- Next, the user needs to click on “Test Connection” to ensure that the connection is functional. If the test is successful, the user then needs to click on “OK” to select the “CUBE” to which he wants to connect. In Microsoft Excel, all Analytic and Calculation Views are measured to the cubes. The user then needs to select their Analytic or Calculation view and press “Next”.
- On the next screen that appears, there will be a checkbox that reads as “Save password in file” which the user can check if he wants to escape needing to enter in the SAP HANA password on each occasion that the Microsoft Excel file is opened. However, he must be aware that by doing this the password will be saved in the Microsoft Excel file, which is may be a little less protected.
- Next, the user needs to press the “Finish” button which will then create the link between the Microsoft Excel file and SAP HANA.
- Finally, on the next screen, the user will be prompted where he wants to insert the pivot table, so he should simply press on “OK” and the results will be presented.
By following the above-mentioned steps and guidelines, users will be able to configure Microsoft Excel with SAP HANA properly.