Saturday, 21 April 2018

How To Integrate Hana Database with Excel

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

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

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.

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

As shown below, Select other/advanced and click on next.

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

Under Data Link Properties,Select SAP HANA MDX Provider and click on next

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

Provide the connection details like hostname, instance number, user name, password ,language type

click on test Connection and Ok

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

Once the test connection is successful, click ok and proceed further

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

Here we see different packages, these packages can be seen in sap Hana. database

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

Here we see different packages, these packages can be seen in sap Hana. database

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

Now go back to SAP Hana database.

->Content->select any one package->expand that package

select one view

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

Right click on this analytic view and click on data preview

click on tab raw data.

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

Now, access this package in excel

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

Go back to excel.

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

As shown below Open Data Connection wizard ,Select the package-> analytic view

click on next.

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

Click on finish.

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

Click on OK

Here we see values and attributes.

Double click on company code to see it.

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

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.

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

Here we see gross revenue based on different company codes.

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Studio, SAP HANA Certifications

No comments:

Post a Comment