SAP HANA integration with Microsoft Excel

Exclusive offer: get 50% off this eBook here
SAP HANA Starter [Instant]

SAP HANA Starter [Instant] — Save 50%

Everything you need to know to be able to build your first SAP HANA standalone application! with this book and ebook.

$19.99    $10.00
by Mark Walker | January 2013 | Enterprise Articles SAP

In this article by Mark Walker, author of SAP HANA Starter, you will learn how to perform the most important tasks in SAP HANA.

(For more resources related to this topic, see here.)

Once your application is finished inside SAP HANA, and you can see that it performs as expected inside the Studio, you need to be able to deploy it to your users. Asking them to use the Studio is not really practical, and you don’t necessarily want to put the modeling software in the hands of all your users.

Reporting on SAP HANA can be done in most of SAP’s Business Objects suite of applications, or in tools which 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 they need.

There are (at 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 can only be visualized in Microsoft Excel, not in BusinessObjects

Prompts can only be used in Business Objects BI4. Views which 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”)

In order to make MDX connections to SAP HANA, the SAP HANA Client software is needed. This is separate to 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/docs/webcontent/uuid/402aa158-6a7a-2f10-0195-f43595f6fe5f

Just download the appropriate version for your Microsoft Office installation.

Even if your PC has a 64-bit installation of Windows, you most likely have a 32-bit installation of Office, and you’ll need the 32-bit version of the SAP HANA Client software. If you’re not sure, you can find the information in the Help | About dialog box. In Excel 2010, for example, click on the File tab, then the Help menu entry.

The version is specified on the right of the page:

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 to not see anything to run.

We’re going to incorporate our sales simulator in Microsoft Excel, so launch Excel now.

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

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

The SAP HANA Logon dialog will appear, so enter your Host, Instance, and login information (the same information you use to connect to SAP HANA with the Studio).

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 your Analytic and Calculation Views are considered to the cubes. Choose your Analytic or Calculation view and click Next.

On this screen there’s a checkbox Save password in file – this will avoid having to type in 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.

Click on the Finish button to create the connection to SAP HANA, and your View.

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

Congratulations! You now have your reporting application available in Microsoft Excel, showing the same information you could see using the Data Preview feature of the SAP HANA Studio.

Resources for Article :


Further resources on SAP HANA Starter:


SAP HANA Starter [Instant] Everything you need to know to be able to build your first SAP HANA standalone application! with this book and ebook.
Published: November 2012
eBook Price: $19.99
See more
Select your format and quantity:

About the Author :


Mark Walker

Mark Walker has been working with computers since a very early age, starting out with programming the Sinclair ZX Spectrum. After graduating from Cardiff University, he moved to France, starting a 17-year (so far) career in SAP development, first on the SAP ERP, then moving to SAP BW in 2004. He specializes in process optimization, especially in the SAP BPS and BI-IP modules. He's been following SAP HANA since its release in 2011, and is a certified HANA Application Associate since early 2012.

Mark has been working for the last six years for Censio, a consultancy in Paris, France, which has expertise in SAP, Microstrategy, Business Objects, and Microsoft BI technologies. During that time he's worked on several end-to-end projects for some prestigious clients, and done some missions in SAP's name.

In November 2012, he published SAP HANA Starter, Packt Publishing, a small book about getting a foothold in the SAP HANA world.

Books From Packt


Mastering SQL Queries for SAP Business One
Mastering SQL Queries for SAP Business One

SAP ABAP Advanced Cookbook
SAP ABAP Advanced Cookbook

SAP Business ONE Implementation
SAP Business ONE Implementation

SAP NetWeaver MDM 7.1 Administrator's Guide
SAP NetWeaver MDM 7.1 Administrator's Guide

SAP BusinessObjects Dashboards 4.0 Cookbook
SAP BusinessObjects Dashboards 4.0 Cookbook

SAP Business ONE Implementation: LITE
SAP Business ONE Implementation: LITE

Microsoft BizTalk 2010: Line of Business Systems Integration
Microsoft BizTalk 2010: Line of Business Systems Integration

Excel 2010 Financials Cookbook
Excel 2010 Financials Cookbook


Your rating: None Average: 5 (1 vote)

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
T
P
Q
h
S
P
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software