Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
andreas_kuhn
Advisor
Advisor

Introduction:

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

Prerequisites:

Installing ODBC on the HANA Server via this Blog Post: http://scn.sap.com/docs/DOC-68640

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

Adapter Name: MSSQL (GENERIC ODBC)

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:

[HBGLAB_afa_latest]

Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0

Description=HBGLAB MSSQL Server

Server=10.29.78.111,1433

Port=1433

Database=HBGLAB_afa_latest

[HBGLAB_Afaria05]

Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0

Description=HBGLAB MSSQL Server

Server=10.29.78.111,1433

Port=1433

Database=HBGLAB_Afaria05

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:

A_CLIENT

A_IPHONE_DEVICE

A_ANDROID_DEVICES

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

Links:

Installing the Driver Manager: https://msdn.microsoft.com/en-us/library/hh568449(v=sql.110).aspx

Installing the Microsoft ODBC Driver for SQL Server on Linux: https://msdn.microsoft.com/en-us/library/hh568454(v=sql.110).aspx