Connecting to SAP HANA with Microsoft Excel
Microsoft excel is most widely used BI tool across the globe, with excel we can explore well versed SAP Hana data.SAP HANA supports the query languages SQL and MDX where as JDBC and ODBC are used for SQL-based access. ODBO is used for MDX-based access. MDX (Multi-Dimensional Expressions) is a query language for OLAP databases. It has been defined as part
of the ODBO (OLE DB for OLAP) specification from Microsoft.
Hana MDX provider is nothing but installing Hana client as per the operating system version.
One unique benefit of SAP HANA MDX is the native support of hierarchies defined for
Attribute Views. There are two types of hierarchies in SAP HANA: level-based- and parent-child-hierarchies,both types of hierarchies are accessible via MDX.
SAP Hana MDX is able to consume models defined n Hana Studio.This design time environment allows you to define logical models on top of physical tables. The existing physical tables represent the data foundation for the logical model.
Please Note that all simple steps presented here are for Excel 2013 connecting to SAP HANA 1.0.
In order to create connection, first specify data source connection, then decide what you want to create with connection and finally use the data source to populate a table or chart report.
Click on blank workbook
It all begins with data connection wizard,to start the Data Connection Wizard, select the Data tab
Click data tab->From other sources->From data connection wizard.
As shown below, Select other/advanced and click on next.
Under Data Link Properties,Select SAP HANA MDX Provider and click on next
Provide the connection details like hostname, instance number, user name, password ,language type
click on test Connection and Ok
Once the test connection is successful, click ok and proceed further
Here we see different packages, these packages can be seen in sap Hana. database
Now go back to SAP Hana database.
->Content->select any one package->expand that package
select one view
Select one analytic view.
Right click on this analytic view and click on data preview
click on tab raw data.
Now, access this package in excel
Go back to excel.
As shown below Open Data Connection wizard ,Select the package-> analytic view
click on next.
Click on finish.
Click on OK
Here we see values and attributes.
Double click on company code to see it.
Double click on customer, we can also see customer along with the company codes.
In this way, select which ever you want to see those fields like gross revenue.
Here we see gross revenue based on different company codes.
Hope this will help you as always .