Reporting From HANA Using Excel

As SAP HANA continues to grow people are connecting to HANA for reporting in many different ways.  One of them includes using Microsoft Excel to report from HANA.  To do this, there are some simple steps that must be followed to establish connectivity between these two applications.  Also, as with any tool, there have been some issues found along the way which will be described in this article.

Setting up HANA Connectivity to Excel

The first step is to create a connection between Excel and HANA is to Download SAP HANA Client for Excel from the SAP Service Marketplace.  This can be found by going to Products – Software Downloads – Support Packages & Patches – Alphabetical List of Products – SAP HANA Platform Edition. From here, you can search based on Entry by Component and select SAP HANA Client for Excel. The next step is to Install the SAP HANA Client for Excel by opening the EXE file that you downloaded in the steps above.  After this, you will only need to follow the simple steps in the Installation Wizard. Once the Installation is complete open a Blank Workbook in Excel.  If you previously had Excel open during the Installation Wizard please close and reopen.  Next, go to the Data Tab, click the From Other Sources dropdown and select From Data Connection Wizard.  After that is done, select Other/Advanced and click Next.

The next screen will bring up the Data Link Properties Window, and on the Provider Tab you will select SAP HANA MDX Provider and click Next. The next screen is the Connection Tab where you will need to enter your SAP HANA Host, Instance Number, and Login Credentials.  After you have entered these details, click Test Connection to make sure the Test Connection has succeeded.  Once this is done, click OK.

The next screen allows you to select from the dropdown which Package the HANA Model you want to report from exists in.  After selecting the Package, click the Analytic or Calculation View that you want to report from and click next.  On the next screen, you enter the details for a connection file that can be used later to connect to the same data source without having to go through the Data Connection Wizard again.  Once a Connection File has been saved, you can open this connection anytime you want by going to the Data tab and clicking Existing Connections. Once the Wizard has completed, you can select how you would like to Import the Data.  From here you can create Pivot Tables, Pivot Charts etc. to analyze the data based on the HANA Model you have selected.

Benefits of using Excel on HANA

Many times, with there being so many reporting alternatives, Excel has been overlooked as a valuable reporting tool to HANA.  One of the big benefits is that Excel can connect directly to HANA without requiring anything else.

Many business users are already comfortable with Excel and giving them the ability to use Excel Pivot Tables to directly analyze their data in HANA can be a very valuable asset.  The ease of use makes it a very effective solution that can utilize the power of HANA. Below is an example of using Excel to create a Pivot Table and Chart based off of a HANA Model.

Known issues when using Excel on HANA

As stated above, Excel can be a very valuable way to analyze your data but it also has its limitations. Excel only provides basic analysis and reporting capabilities compared to other reporting tools such as Web Intelligence, Crystal Reports, and Lumira.  So, depending on your needs Excel may not have the functionality to fulfill your requirements. Below are some of the most common issues found when using Excel on HANA:

  • Excel cannot consume HANA Models that have Input Parameters or Variables defined.
  • The use of nonnumeric data types as measures is not allowed.
  • Attributes with the data types of VARBINARY, DOUBLE, and DECIMAL are not allowed.
  • Calculated Attributes that refer to measure values are not allowed.
  • Analytic Privileges are not currently supported by Excel.
  • Excel only supports fixed currency fields.

SAP HANA is a very powerful Platform and can be used in many ways depending on your business needs.  This is just one example of how you can use Excel to view your data from HANA.  Please tune in for future blogs on how to view your HANA data in other ways such as Analysis for Office, OData Data Feeds and much more!

See more Business Intelligence insights or get future articles sent right to your inbox

increase your knowledge
increase your knowledge
By |2018-09-02T17:39:47+00:00May 2nd, 2017|BI, Business Intelligence, HANA, Kingfisher|0 Comments

About the Author:

Andrew has more than 7 years of experience and specializes in the EIM field, with a solid background in front end as well. He has strong experience with SAP HANA, Data Services, Information Design Tool, Lumira, Crystal Reports and Web Intelligence. He has worked in a number of industries such as Retail, Energy, Manufacturing, and Healthcare with direct experience working with two Fortune 100 companies. Andrew enjoys playing and watching sports, being outdoors, and traveling in his free time. When he’s not busy pulling for the Atlanta Sports teams you can reach him at Andrew.Johnson@kingfisherinc.com.