How to connect your Datazen server to SAP HANA via ODBC

This is actually a pretty easy post for me to write, since I’ve done all the heavy lifting in three earlier posts.  So, things you’ll need to do as pre-requisites for this article –

Setup your HANA instance – follow the instructions in this post to provision a test/dev HANA server and deploy it to Azure if you don’t have one already setup. 

Setup your Datazen server – follow the instructions in this post if you need to set one up – http://bit.ly/1GtIuJ4.

Create a custom ODBC data connection file and add it to your Datazen server – follow the instructions in this post –  http://bit.ly/1AyJbw6

Once you’ve done those things, you’re already 90% of the way there.  Here’s what is remaining –

First, One thing I found when going through this was SAP seems to have made their native ODBC driver unavailable outside of the SAP software corner.  If you don’t have access to it, you can download one from Progress DataDirect to preview for free for 60 days.  It’s available for download at this link – http://bit.ly/1EaBsGc, and I used that for this post since that is one everyone can get at currently.

Make sure you download the 64-bit version and install it on the Datazen server.  Once you’ve installed it, you can create the system DSN file for Datazen to use.  The following steps you all do on the Datazen server.

Go to Control Panel – > Administrative Tools

Then choose ODBC Data Sources (64 bit)

Click the System DSN tab then click “Add”

Select the SAP HANA driver and click Finish

Enter a name for your data source, the description, and the host name.  If you followed the instructions in the earlier blogpost, you should be able to use the public IP address that your SAP HANA server was assigned for the host name.  Leave the port as is –

Hit the “Test Connect” button at the bottom and enter the credentials to connect to the server.  Assuming it authenticates, you should get a message showing it connected.

You can then hit OK to save your system DSN connection.  Now go to your Datazen control panel to setup the HANA connection and write your first query.

Under the BI hub you want to setup the HANA connection, select the Data Sources option.

Choose the custom ODBC provider you setup in my previous post so you can pass the username/password in the connection string to the HANA server.

Test the connection – you should get a success message if you connected properly.

Great – you’ve finished setting up your connection to the HANA server.  Now you can write a sample query against it and make sure it brings back data.  Click on the name of your data connection to go to the Data View screen

Now click the “New Data View” button to create a new view of your HANA data.  Give it a name, leave the “Allow Client Data Caching” box checked if you want to make sure folks can use the dashboards while offline, and choose a refresh frequency.  Then write your query to be used against HANA and hit Next –

Success!  I wrote my query properly, and now have this data view available for the dashboards I want to build in my Datazen Publisher App.

Congratulations – you’ve finished setting up SAP HANA as one of the data sources for your jaw-dropping Datazen KPI’s and dashboards!