Connecting To SAP HANA via PHP and ODBC

Connecting To SAP HANA via PHP and ODBC: We’ve recently been so busy in the office with all things BI on HANA that it’s been a while since I had an opportunity to blog. Wow, how the world is changing, and how quickly too!

I was messing about last week with some custom views to support a direct Lumira connection and thought it would be interesting to see how difficult it would be to connect to SAP HANA via PHP and ODBC.

Those who have seen my earlier posts will know that I love building visualisations using the D3.js library, and acquiring a dataset is the foundation step of all good visualisations.

It struck me that connecting directly to SAP HANA from my own web server might open up a few interesting opportunities. Also, since the advent of B4HANA and SAP’s stated objective to remove all barriers to the Enterprise BI datasets held in SAP BW systems it seems we are all now becoming DBAs (its views all the way down). And what better way to prove your HANA modelling concepts than messing about with your own custom UX.

(Note: this is just a PoC and not intended to usurp the Fiori UX development approach – I just wanted to see if I could connect and what the effort would be).

Well, probably not surprisingly, it was pretty easy (once I had configured the correct ODBC drivers!)

Here is an image of a subset of one of our Product Hierarchies to illustrate what I was able to achieve:

Here’s what you need to do:

1. Install the SAP HANA ODBC Drivers:

• The best way (and only way I could find) to do this is to install the SAP HANA Client. I found this here: https://launchpad.support.sap.com/#/softwarecenter (search for hana client)
• Be sure to choose the appropriate configuration for your application. I’m running a 64bit Windows machine so I chose the following file:
  • IMDB_CLIENT100_112_7-10009664.SAR
  • For 32bit Windows choose:  IMDB_CLIENT100_112_7-10009665.SAR
  • Note: You might also need to download and install the SAPCAR zip tool to unpack the archive.
• Then install the SAP HANA Client, this will install the relevant HANA ODBC driver, see next step.

2. Create an ODBC Datasource

• Search for the ODBC Data Source Administrator on your machine (I searched my machine for “ODBC” and then selected the 64bit version of the ODBC admin tool)
• In the ODBC Data Source Administrator go to the System DSN page and add a new Data Source.
• Use the HDBODBC (64bit) or HDBODBC32 (32 bit) driver that was been installed when you installed the SAP HANA Client, see above.
• Configure your connection with the IP address and the Port (Note: the port number is 3++15: 30015.
• You might also find the following SAP Help page useful.

3. Create a PHP file to execute a query on the SAP HANA database

• I used the PHP PDO protocol to make the connection. The main PHP syntax is as follows:

4. Write a Web Application to visualise the data

• Finally I adapted the D3 Radial Tree example from Mike Bostock into an HTML, JS, CSS solution and hosted it on my local webserver.
• Instead of using a static .csv or .json file I pointed my javascript at the .php file I created in the above step to query the HANA database in realtime.
• All-in-all quite a satisfying PoC.

Not sure if this post is useful to anyone else out there, but I found the exercise informative.

Later,
Andrew