BI 4 on HANA-Part 3: Creating shared relational connections to SAP HANA
After the two introductory articles let's now dive a bit into the technicalities of using SAP HANA as a datasource for your BI 4 deployment. In this article we see how you can create reusable relational connections to SAP HANA.
Creating shared secured relational connections to SAP HANA
Some BI client tools are able to retrieve a relational connection to SAP HANA already defined in the BI platform. The following sections describe how to create such a connection with Information Design Tool and make it available for the client tools.
The SAP HANA relational connection are either based on ODBC or on JDBC.
Connections shared on the BI platform just provide a standard entry point to connect to HANA; each client tools uses them for its specific needs (e.g. connecting to tables or views for universes, connecting to views for other tools.
Relational connections are used to build universes (both JDBC and ODBC) and can be directly accessed by Analysis Office, Explorer (JDBC only) to connect to SAP HANA.
Relational connections are created using the Information Design Tool and you need a BI platform to share them for other applicatios.
As a prerequisite you must have installed the HANA Client (JDBC and/or ODBC) on the Information Design Tool machine and the BI platform machine which you are going to use.
Creating a shared secured relational connection from the Information Design Tool via JDBC
After launching the Information Design Tool, you should open a project and in a folder create a new relational connection
and provide a name for it
You have to choose the appropriate connection middleware, in this case an SAP HANA connection using a JDBC driver
Finally you should provide the user name, password and the server name with its port. You can test the validity of the connection directly from this page
|Technical information: how to calculate the port number from the HANA instance number?|
When connecting to a HANA machine you need to know either the ‘instance number’ or the ‘port number’. There is a simple relationship between the two: if the instance number is XX, the port number is 3XX15. E.g. an instance number 01 is on port 30115.
The connection is now saved in the local Information Design Tool project.
From the same interface, in the Show Values tab, it is possible to immediately run SQL queries onto the HANA database to check if you can see data correctly.
In order to share the connection for all client tools, you have to publish it to a CMS server.
To do so, right click on the connection in the local project and select to publish it to a repository.
Then you select a BI platform server and publish the connection in one folder.
The connection is now available for all tools enabled to browse the BI platform for it (Universes, Explorer, Analysis Office)
Creating a shared secured relational connection from the Information Design Tool via ODBC
The steps to create a shared relational connection using ODBC are the same as for the JDBC connection but require, beforehand, that you install the HANA Client ODBC driver and correctly configure the middleware on the machines of Information Design Tool and of the BI platform.
On the machine where you have Information Design Tool you need to define a new entry in the Windows 32 bit ODBC manager. Make sure that you open the 32 bit version of ODBC manager, this application is normally stored in this location: C:\Windows\SysWOW64\odbcad32.exe
In the System DSN page you Add a new connection and select the HDBODBC32 driver (this is the SAP HANA 32 bit ODBC driver).
You now have to fill in the connection information by providing the name of the data source the server address and the port number.
After saving the connection you have to make sure that the same information is available on the server onto which you are going to publish it. This guarantees that your connection will work for both the local authoring workflow in Information Design Tool and for all the consumption workflows performed against the Server.
You now have to go on the machine where the BI platform server is installed and there open the Windows 64bit ODBC manager. It is very important to make sure you open the 64bit version on the server: IDT uses a 32 bit connection but the server is using the 64bit version.
The Windows 64bit ODBC manager is normally located here: C:\Windows\System32\odbcad32.exe .
On the server ODBC manager you perform the same workflow as above selecting the HDBODBC driver (this is the 64bit version of the SAP HANA ODBC driver).
Make sure that the “Data Source Name” and the “Server:Port” information are exactly the same on the two machines.
Now that the Windows part is correctly setup you can go back to Information Design Tool and build a new relational connectivity as shown in the JDBC driver workflow. The only difference is that you have to choose the ODBC driver in the Database Middleware Driver Selection page.
After creating the connection you publish it to the BI platform server where you previously configured the ODBC manager.
Summary & tips
You have seen in this article how to define shared relational connections to SAP HANA for the BI 4 applications. As a rule of thumb, JDBC connections provide more functionality than ODBC ones: you have seen that they can be used by Explorer and Analysis Office and you will see in the future article about universes that they present a few benefits with regards to ODBC.
When you configure ODBC make sure you use the correct 64bit or 32bit middleware. You should know that using the Information Design Tool on the same machine where the BI platform server is installed is not supported (because of possible interactions between the 32bit and 64bit ODBC DSN you would need).
I hope this article was useful!
You can go back to the list of available articles here: SAP BusinessObjects BI 4 on SAP HANA