- SAP Managed Tags:
"I can do that with Excel". If I had been given a dollar for every time I heard that phrase back when I was implementing SAP solutions, I would be close to millionaire. Sure, MS excel is a powerful tool for small datasets and there are many users out there who can make wonderful things with it. What if those users could leverage the power of SAP HANA in their spreadsheets? I think magic could happen.
We can quickly enable the magic with Multi-Dimensional Expressions (MDX). The SAP HANA MDX Provider for MS Excel was actually released some years ago and is included in the SAP HANA Client (which can be downloaded from SAP Software Download Center).
It allows us to connect to our SAP HANA instance, query an existing Calculation (or Analytical) view and then display it in a Pivot Table or Chart:
The prerequisite for this is, of course, having an available Calculation View. In case this is not already available with, for example, SAP HANA Live views, I have built a quick tutorial to go from importing data from an MS Excel spreadsheet using SAP HANA Studio, creating a very simple Calculation View and then consuming it from MS Excel.
In the process, the tutorial shows to how to create two simple tables in a Core data Services file, so I am leaving the reference to primitive types from the help site so you can create your own entities and types.
Last but not least, a small warning for importing data into SAP HANA from an Excel spreadsheet: Sometimes, MS Excel shows a format but is internally saving something different. I generally convert the file to CSV and open it in a plain-text editor to verify the date formats are correct and there are no additional hidden decimals in my IDs, for example.
As always, looking forward to your feedback and hoping this will help you make your day easier!
We can quickly enable the magic with Multi-Dimensional Expressions (MDX). The SAP HANA MDX Provider for MS Excel was actually released some years ago and is included in the SAP HANA Client (which can be downloaded from SAP Software Download Center).
It allows us to connect to our SAP HANA instance, query an existing Calculation (or Analytical) view and then display it in a Pivot Table or Chart:
The prerequisite for this is, of course, having an available Calculation View. In case this is not already available with, for example, SAP HANA Live views, I have built a quick tutorial to go from importing data from an MS Excel spreadsheet using SAP HANA Studio, creating a very simple Calculation View and then consuming it from MS Excel.
In the process, the tutorial shows to how to create two simple tables in a Core data Services file, so I am leaving the reference to primitive types from the help site so you can create your own entities and types.
Last but not least, a small warning for importing data into SAP HANA from an Excel spreadsheet: Sometimes, MS Excel shows a format but is internally saving something different. I generally convert the file to CSV and open it in a plain-text editor to verify the date formats are correct and there are no additional hidden decimals in my IDs, for example.
As always, looking forward to your feedback and hoping this will help you make your day easier!
7 Comments
