Connect MS-Excel to SAP HANA Database/Analysis in excel with HANA data
SAP HANA is a high-performance in-memory database that accelerates data-driven, real-time decision-making and actions and supports all workloads with the broadest advanced analytics on multi-model data, on premise and in the cloud. We all know that the HANA is a database technology which provides data to the users very fast.
On the other hand, MS Excel is one of the most widely used data representation, analysis and reporting software of all time and still it is one of the most important software used throughout the world.
We can extend the capabilities of Excel’s by connecting it to SAP HANA which provides real-time data access. Here in this post we are going to discuss how we can utilize the capabilities of MS Excel with HANA.
We will be using MDX Provider to bridge the connection between HANA Database and Excel.
Step 1: Download and install the MDX Provider from SAP Support Portal.
Before downloading confirm your version of the Excel 32 bit/64 bit.
Step 2: Select the connection type in MS-Excel.
Open the MS Excel . Go to the Data tab. Then select From Other Sources option. From the drop-down list, select From Data Connection Wizard.
Step 3: Select the data provider type
Select Other/Advanced option from the Data Connection Wizard dialog and click Next.
Step 4: Select the data provider
Select the data provider as SAP HANA MDX Provider from the given list. Then, click on Next>>.
Step 5: Enter connection properties
Enter connection parameters specific to SAP HANA user account like Host name, Instance number, User name, Password, and Preferred language. You can instantly test a connection from this dialog by clicking on the Test Connection option.
A message will prompt confirming a successful connection that says, “Test Connection Succeeded”. Click on OK to continue.
Note: Make sure the checkbox multi database and user database is ticked.
Step 6: Select view and create chart
Once the connection established you will be able to see the package and there views built through the HANA Studio.
Choose the object which is required for your analysis and use chart or any other excel features to represent your data.
Note: Slow internet connection may throw connection error.
There is no doubt SAP has provided us lots of resources and features to make our business easy and simplified.
I will be here to answer all your questions in case you find any issue.