How To Integrate Hana Database with Excel

[SumitPatel11]

 

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 .

Regards,

Sumit