SAP HANA Integration with Microsoft Excel

Reporting in SAP HANA can be done in most of SAP’s BusinessObjects suite of applications, or in tools that can create and consume MDX QUERIES and data.

The simplest of these tools to start with is probably Microsoft Excel. Excel can connect to SAP HANA using the MDX language (a kind of multidimensional SQL) in the form of pivot tables. These in turn allow users to “slice and dice: data as they require, to extract the metrics that they need.

There are (at the time of writing) limitations to the integration with SAP HANA and external reporting tools. These limitations are due to the relative youth of the HANA product, and are being addressed with each successive update to the software. Those listed here are valid for SAP HANA SP04; they may or may not be valid for your version.

Hierarchies (not covered in this post) can only be visualized in Microsoft Excel, not in BusinessObjects.

Prompts can only be used in BusinessObjects BI$. Views that use variables can be used in other tools, but only if the variable has a default value (if you don’t have a default value on the variable, then Excel, notably, will complain that the view has been changed on the server – if your SALES_INCREASE_PERCENT prompt doesn’t have a default value and if we didn’t initially give it one – you might like to edit it now, and enter one if you wish to visualize your simulator in Excel).

In order to make MDX connections to SAP HANA, the SAP HANA client software is needed. This is separate from the Studio, and must be installed on the client workstation.

Like the Studio itself, it can be found on the SAP HANA DVD set, or in the SWDC. Additionally, like the Studio, SAP provides a developer download of the client software on SDN, at the following link:

http:/www.sdn.sap.com/irj/scn/go/portal/prtroot/d-ocs/webcontent/uuid/402aa158-6a7a-2f10-0195-f43595f6fe5f.

Just download the appropriate version for your Microsoft Office installation.
The version is specified on the right-hand side of the page, as shown in the following screenshot:

Just install the client software like you installed the Studio, usually to the default location.

Once the software is installed, there is no shortcut created on your desktop, and no entry will be created in your Start menu, so don’t be surprised not to see anything to run.

Go to the Data tab, and click on From Other Sources, then From Data Connection Wizard, as shown in the following screenshot:

Next, select Other/Advanced, then select SAP HANA MDX Provider, and then click Next.

The SAP HANA login dialog will appear, so enter your host, instance, and login information (the information you needed to connect to SAP HANA with the Studo, back in the Installation section).

Click on Test Connection to validate the connection. If the test succeeds, click on OK to choose the cube to which you want to connect. In Excel, all our analytic and calculation views are considered to the cubes. Choose SIMULATOR and click Next, shown in the following screenshot:

On this screen ther’s a Save password in file checkbox – this will avoid having to enter the SAP HANA password every time the Excel file is opened, but the password is stored in the Excel file, which is a little less secure.

Checkout SAP HANA Interview Questions

Click on the Finish button to create the connection to SAP HANA, and our SIMULATOR view.

On the next screen, you’ll be asked where you want to insert the pivot table; just click on OK, to see the following results:

We now have our reporting application available n Microsoft Excel, showing the same information that we could see using the Data Preview feature of the SAP HANA Studio.

In this section, we built upon the attribute and analytic views created previously. We created a second analytic view by copying the first, and added a filter, an input variable (prompt), calculated attribute, and a measure, to enable our users to project sales for 2012 based upon real sales in 2011. In the Union block to the calculation view to join the data from the two projections together. In the Union block, we used the ORDER_YEAR field from the REAL data, mapped with the NEXT_YEAR field from the PROJECTED data, showing that we can aggregate data from different views if the field names are not the same.

Finally, we called up our simulator application in Microsoft Excel using a pivot table to report on our data, showing the integration between SAP HANA and reporting tools, using MDX.

Using the techniques found in this post, you are now in a position to build your own reporting applications using your own data and the possibilities SAP HANA provides.