Skip to Content

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…”.

/wp-content/uploads/2016/06/2016_05_12_11_40_01_968484.png

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

/wp-content/uploads/2016/06/2016_05_12_11_41_25_968485.png

/wp-content/uploads/2016/06/2016_05_12_12_24_57_968517.png

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”.

/wp-content/uploads/2016/06/2016_05_12_12_41_14_968518.png

/wp-content/uploads/2016/06/2016_05_12_12_42_15_968520.png

/wp-content/uploads/2016/06/2016_05_12_12_44_41_968521.png

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

/wp-content/uploads/2016/06/2016_05_12_12_45_09_968525.png

Give it an appropriate name and finish

/wp-content/uploads/2016/06/2016_05_12_12_47_02_968526.png

Create 2 new Joins “Join_iOS” & “Join_Android”

/wp-content/uploads/2016/06/2016_05_18_15_10_36_968527.png

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.

/wp-content/uploads/2016/06/2016_05_18_15_11_24_968528.png

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.

/wp-content/uploads/2016/06/2016_05_18_15_16_23_968529.png

Create a union based on Join_iOS & Join_Android

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

/wp-content/uploads/2016/06/2016_05_18_15_21_56_968530.png

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

/wp-content/uploads/2016/06/2016_05_18_15_29_10_968537.png

/wp-content/uploads/2016/06/2016_05_18_15_32_35_968538.png

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

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply