Skip to Content
Author's profile photo Andreas Kuhn

Howto connect HANA to MSSQL Afaria Server and create a simple view


This guide describes how to connect HANA to MS SQL Server and create a simple basic view to show all registered Devices in the connected database of the Afaria Server.

Used Versions:

Microsoft SQL Server 2014 ; 12.0.4213.0

SAP Afaria 7.0 Service Pack 14

SAP HANA 1.0 SPS12 (Rev. 120) on SUSE Linux Enterprise Server 11.4 for SAP

Microsoft ODBC Driver 11 for SQL Server ; 11.0.2260.0

unixODBC Manager 2.3.0


Installing ODBC on the HANA Server via this Blog Post:

Follow Steps 1.0 – 2.4

Next Steps in HANA Studio

  1. Connect SAP HANA to MS SQL Server
  2. Launch SAP HANA Studio
  3. Log in to your Databse
  4. Expand “Provisioning”.
  5. Right click “Remote Sources” and select “New Remote Source…”.


Enter the required Fields:

Source Name: Should match your DSN, but can be different if your like


Data Source Name: Has to match your DSN, the value in the [] in your odbc.ini. Below you will find a sample of my ODBC.ini file

User Name / Password: Here you should use SQL Credentials and not Windows Credentials

After filling in all values save the Source. It will be tested automatically.

Sample ODBC.ini:



Description=HBGLAB MSSQL Server






Description=HBGLAB MSSQL Server






Import a Table

  1. Expand your new Remote Source
  2. In the “dbo” Schema all tables can be found
  3. Perform a right click on a table and “Add as Virtual Table”.




For our sample here do this for the tables:




Create calculation view

Perform a right click on your content package and select new -> Calculation view


Give it an appropriate name and finish


Create 2 new Joins “Join_iOS” & “Join_Android”


In the “Join_iOS” you need to join the A_CLIENT & A_IPHONE_DEVICE table as shown in the Screenshot.

We join them via the ClientUID value in both tables and using a Left Outer join here.


The next join is “Join_Android” for the tables A_CLIENT & A_ANDROID_DEVICE

We use ClientUID again here for the Left Outer join.

For this join we only add values from A_ANDROID_DEVICE as output.


Create a union based on Join_iOS & Join_Android

Drag and Drop the Sources to the Target column as shown below


In the Semantics set the Types as shown below, then save and view the data




Installing the Driver Manager:

Installing the Microsoft ODBC Driver for SQL Server on Linux:

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.