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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
21 | |
16 | |
13 | |
12 | |
11 | |
10 | |
7 | |
7 | |
7 | |
7 |