Freelancers
Cancel
 Sign In
Join Now

SAP HANA Cloud enables your data-driven solutions to leverage cloud-native scalability, speed, and performance. SAP HANA Cloud also appeals to businesses extending their SAP applications with adjacent non-SAP scenarios. A great example of extending transactional applications is the use of spatial data. Companies that utilize both transactional and spatial data can gain more insights from the geographic understanding of their data.

SAP enjoys a longtime partnership with ‘Esri’ – A worldwide leader in GIS system. Since 2018, SAP HANA has been a certified Esri geodatabase, taking advantage of SAP HANA’s native in-memory spatial engine. With the addition of SAP HANA Cloud, joint customers now have the option to utilize the first in-memory fully managed database as a service(DBaaS) as a supported geodatabase.

As an example, customers that implement Esri along with SAP HANA cloud might consider an architecture that looks like below:

Moving forward with this blog, I would like to demonstrate how the above components work together to deliver a spatially enhanced business solution.

Tutorial

Goal: Learn the steps to integrate SAP’s ERP like business data with GIS data using a simple scenario

This tutorial takes you through the following steps

  1. Set-up Free Trial and provision an SAP HANA Cloud instance.
  2. Download the trial version of Esri’s ArcGIS Pro
  3. Configure and Install SAP HANA ODBC Driver
  4. Connect SAP HANA Cloud instance to ArcGIS Pro
  5. Combine GIS and ERP like business data

Step 1: Set-up free trial and provision an SAP HANA Cloud instance

The free trial allows you to test SAP HANA Cloud for 30 days and renew it every month until you reach 360 days. SAP HANA Cloud is a service available in the SAP Business Technology Platform (BTP) and is based on SAP HANA.

Step 2: Install the trial version of Esri’s ArcGIS Pro

  • Download the trial version of ArcGIS Pro by filling out the trial form on Esri’s ArcGIS Pro Free Trial Site: Sign up for your 21-day ArcGIS Pro trial
  • Once you have filled out the form, check your email with subject line – “Activate Your ArcGIS Pro Trial”
  • Click on the “Activate your Trial” hyperlink
  • Follow along the steps from the activate link to install ArcGIS Pro

Note: You can also refer to steps mentioned in the video: Getting Started with the ArcGIS Trial

Step 3: Configure and Install SAP HANA ODBC Driver

To be able to connect from an ArcGIS Pro project to SAP HANA Cloud, the prerequisite is to install the SAP HANA client ODBC driver on your local machine. If you already have this installed, then you may skip the below steps.

Refer to the tutorial: Install from SAP Development Tools to install SAP HANA client ODBC component

Now to add the above installed SAP HANA Client into ODBC Data Source, follow the below steps.

  • Click Start > Control Panel > Administrative Tools
  • Right Click on ‘ODBC Data sources(64-bit)’ and select ‘Run as Administrator’
  • On the User DSN tab, click on Add button
  • On the new Create new Data Source pop up, select HDODBC and select ‘Finish’
  • On the ODBC Configuration for SAP HANA pop up, enter the Data source name (marked as 1) and Host information (marked as 2) as below. [Note: Host information is the SQL endpoint of your recently created SAP HANA Cloud Trial instance. Here is a video showing how to copy SQL endpoint of a SAP HANA Cloud instance.
  • Then click on test Connection button (marked as 3), enter the User ID and Password you specified while creating the trial instance to see if the connection to the HANA Cloud Trial instance is successful (marked as 4).
  • Once the connection is successful and you see the below message, click on Ok and finish the ODBC setup for the SAP HANA Cloud Trial instance

Here is the walkthrough video of above steps:

 

Step 4: Connect the SAP HANA Cloud instance to Esri’s ArcGIS Pro

In this step create an ArcGIS Pro project and connect it to the SAP HANA Cloud Trial instance for further data import, visualization, and analysis.

  • Open ArcGIS Pro
  • Under the ‘New’ section > ‘Blank templates’, click on Map
  • Enter the project name(‘HotelMaintenance’), set the location of where you want to place your project
  • Click on Ok
  • You are now in ’HotelMaintenance’ project in ArcGIS Pro and can see the world topographic map
  • In the Catalog section, right click on ‘Databases’ and select ‘New Database Connection’
  • On the Database Connection pop up
    • Choose the Data platform as SAP HANA from the dropdown
    • Enter the name of SAP HANA Cloud data source previously configured – ‘SAPHANACloudTrial’
    • Set the Authentication Type to ‘Database authentication’.
    • Enter the authentication username and password for the SAP HANA Cloud instance and click on Ok.

You can now see the SAP HANA Cloud Trial Instance as added database connection in ArcGIS Pro. Refer to this video for a detailed walkthrough: Connect SAP HANA Cloud database to Esri’s ArcGISPro

Optional Step: Geo-Enabling the SAP HANA Cloud Instance

SAP HANA Cloud is a certified geodatabase for Esri. In this optional step, Esri ArcGIS customers can proceed with geo enabling the SAP HANA Cloud trial instance.

Please note that Geo Enabling the SAP HANA Cloud instance (or any other supported database on ArcGIS Pro) is possible only if you hold an ArcGIS Server Basic license. Geo Enabling as a feature is not available for the Trial version of ArcGIS Pro. But this should not be a problem to us to continue on the tutorial, as ArcGIS can work with feature classes utilizing SAP HANA Cloud.

Steps to enable SAP HANA Cloud as a geodatabase

  • In the Catalog tab, right click on the database ‘SAPHANACloudTrial.sde’
  • Select Enable Enterprise Geodatabase
  • On the parameters panel in Geoprocessing tab, browse and select the Authorization keycodes file you have received when you downloaded the ArcGIS Pro Enterprise version.
  • Click on Run. Once the process is completed, your SAP HANA Cloud Trial instance is now geo enabled.

Want to know what is a geodatabase? Refer to this blog: What is a Geodatabase anyway? by my expert colleague Tom Turchioe

Also here is a video on how to geo enable a SAP HANA Cloud database on Enterprise ArcGIS Pro: Geo Enabling an SAP HANA Cloud instance on ArcGIS Pro

Step 5: Combine GIS and ERP like business data

Now we will see in detail of how we can integrate Esri’s shapefiles (GIS data) with business or enterprise data on SAP HANA Cloud.

Let’s get started and populate some data into our empty SAP HANA Cloud Trial instance.For this we will be using the SAP HANA Cloud database explorer tool.

  • Get back to the SAP BTP Cockpit page where you can see the SAP HANA Cloud Trial instance we created.
  • Click on Actions and choose ‘Open in SAP HANA Database Explorer’
  • You may be prompted for your user credentials. Use the same username and password, that you have setup while configuring the trial instance (DBADMIN)
  • Click on the SQL symbol in the upper left corner to Open a new SQL Console.
  • Copy the below SQL statements into the SQL console and execute:
CREATE SCHEMA HOTEL;
CREATE COLUMN TABLE HOTEL.HOTEL(
hno INTEGER PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address VARCHAR(40) NOT NULL,
city VARCHAR(30) NOT NULL,
state VARCHAR(2) NOT NULL,
zip VARCHAR(6),
location ST_Point(4326)
);

CREATE COLUMN TABLE HOTEL.ROOM(
hno INTEGER,
type VARCHAR(6),
free NUMERIC(3),
price NUMERIC(6, 2),
PRIMARY KEY (hno, type),
FOREIGN KEY (hno) REFERENCES HOTEL.HOTEL
);

CREATE COLUMN TABLE HOTEL.CUSTOMER(
cno INTEGER PRIMARY KEY,
title VARCHAR(7),
firstname VARCHAR(20),
name VARCHAR(40) NOT NULL,
address VARCHAR(40) NOT NULL,
zip VARCHAR(6)
);

CREATE COLUMN TABLE HOTEL.RESERVATION(
resno INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
rno INTEGER NOT NULL,
cno INTEGER,
hno INTEGER,
type VARCHAR(6),
arrival DATE NOT NULL,
departure DATE NOT NULL,
PRIMARY KEY (
"RESNO", "ARRIVAL"
),
FOREIGN KEY(cno) REFERENCES HOTEL.CUSTOMER,
FOREIGN KEY(hno) REFERENCES HOTEL.HOTEL
);

CREATE COLUMN TABLE HOTEL.MAINTENANCE(
mno INTEGER PRIMARY KEY,
hno INTEGER,
description VARCHAR(100),
date_performed DATE,
performed_by VARCHAR(40)
);
  • Now, you have created a HOTEL schema and column tables HOTEL, ROOM, CUSTOMER, RESERVATION, MAINTENANCE. Let us now populate these tables with some data.
  • Copy the below SQL script and run it in the SQL console.
INSERT INTO HOTEL.HOTEL VALUES(10, 'Congress', '155 Beechwood St.', 'Seattle', 'WA', '98121', NEW ST_POINT('POINT(-122.347340 47.610546)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(11, 'Regency', '477 17th Avenue', 'Seattle', 'WA', '98177', NEW ST_POINT('POINT(-122.371104 47.715210)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(12, 'Long Island', '1499 Grove Street', 'Long Island', 'NY', '11716', NEW ST_POINT('POINT(-73.133741 40.783602)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(13, 'Empire State', '65 Yellowstone Dr.', 'Albany', 'NY', '12203', NEW ST_POINT('POINT(-73.816182 42.670334)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(14, 'Midtown', '12 Barnard St.', 'New York', 'NY', '10019', NEW ST_POINT('POINT(-73.987388 40.766153)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(15, 'Eighth Avenue', '112 8th Avenue', 'New York', 'NY', '10019', NEW ST_POINT('POINT(-73.982495 40.767161)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(16, 'Lake Michigan', '354 OAK Terrace', 'Chicago', 'IL', '60601', NEW ST_POINT('POINT(-87.623608 41.886403)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(17, 'Airport', '650 C Parkway', 'Rosemont', 'IL', '60018', NEW ST_POINT('POINT(-87.872209 41.989378)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(18, 'Sunshine', '200 Yellowstone Dr.', 'Clearwater', 'FL', '33755', NEW ST_POINT('POINT(-82.791689 27.971218)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(19, 'Beach', '1980 34th St.', 'Daytona Beach', 'FL', '32018', NEW ST_POINT('POINT(-81.043091 29.215968)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(20, 'Atlantic', '111 78th St.', 'Deerfield Beach', 'FL', '33441', NEW ST_POINT('POINT(-80.106612 26.312141)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(21, 'Long Beach', '35 Broadway', 'Long Beach', 'CA', '90804', NEW ST_POINT('POINT(-118.158403 33.786721)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(22, 'Indian Horse', '16 MAIN STREET', 'Palm Springs', 'CA', '92262', NEW ST_POINT('POINT(-116.543342 33.877537)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(23, 'Star', '13 Beechwood Place', 'Hollywood', 'CA', '90029', NEW ST_POINT('POINT(-118.295017 34.086975)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(24, 'River Boat', '788 MAIN STREET', 'New Orleans', 'LA', '70112', NEW ST_POINT('POINT(-90.076919 29.957531)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(25, 'Ocean Star', '45 Pacific Avenue', 'Atlantic City', 'NJ', '08401', NEW ST_POINT('POINT(-74.416135 39.361078)', 4326));
INSERT INTO HOTEL.HOTEL VALUES(26, 'Bella Ciente', '1407 Marshall Ave', 'Longview', 'TX', '75601', NEW ST_POINT('POINT(-94.724051 32.514183)', 4326));

INSERT INTO HOTEL.ROOM VALUES(10, 'single', 20, 135.00);
INSERT INTO HOTEL.ROOM VALUES(10, 'double', 45, 200.00);
INSERT INTO HOTEL.ROOM VALUES(12, 'single', 10, 70.00);
INSERT INTO HOTEL.ROOM VALUES(12, 'double', 13, 100.00);
INSERT INTO HOTEL.ROOM VALUES(13, 'single', 12, 45.00);
INSERT INTO HOTEL.ROOM VALUES(13, 'double', 15, 80.00);
INSERT INTO HOTEL.ROOM VALUES(14, 'single', 20, 85.00);
INSERT INTO HOTEL.ROOM VALUES(14, 'double', 35, 140.00);
INSERT INTO HOTEL.ROOM VALUES(15, 'single', 50, 105.00);
INSERT INTO HOTEL.ROOM VALUES(15, 'double', 230, 180.00);
INSERT INTO HOTEL.ROOM VALUES(15, 'suite', 12, 500.00);
INSERT INTO HOTEL.ROOM VALUES(16, 'single', 10, 120.00);
INSERT INTO HOTEL.ROOM VALUES(16, 'double', 39, 200.00);
INSERT INTO HOTEL.ROOM VALUES(16, 'suite', 20, 500.00);
INSERT INTO HOTEL.ROOM VALUES(17, 'single', 4, 115.00);
INSERT INTO HOTEL.ROOM VALUES(17, 'double', 11, 180.00);
INSERT INTO HOTEL.ROOM VALUES(18, 'single', 15, 90.00);
INSERT INTO HOTEL.ROOM VALUES(18, 'double', 19, 150.00);
INSERT INTO HOTEL.ROOM VALUES(18, 'suite', 5, 400.00);
INSERT INTO HOTEL.ROOM VALUES(19, 'single', 45, 90.00);
INSERT INTO HOTEL.ROOM VALUES(19, 'double', 145, 150.00);
INSERT INTO HOTEL.ROOM VALUES(19, 'suite', 60, 300.00);
INSERT INTO HOTEL.ROOM VALUES(20, 'single', 11, 60.00);
INSERT INTO HOTEL.ROOM VALUES(20, 'double', 24, 100.00);
INSERT INTO HOTEL.ROOM VALUES(21, 'single', 2, 70.00);
INSERT INTO HOTEL.ROOM VALUES(21, 'double', 10, 130.00);
INSERT INTO HOTEL.ROOM VALUES(22, 'single', 34, 80.00);
INSERT INTO HOTEL.ROOM VALUES(22, 'double', 78, 140.00);
INSERT INTO HOTEL.ROOM VALUES(22, 'suite', 55, 350.00);
INSERT INTO HOTEL.ROOM VALUES(23, 'single', 89, 160.00);
INSERT INTO HOTEL.ROOM VALUES(23, 'double', 300, 270.00);
INSERT INTO HOTEL.ROOM VALUES(23, 'suite', 100, 700.00);
INSERT INTO HOTEL.ROOM VALUES(24, 'single', 10, 125.00);
INSERT INTO HOTEL.ROOM VALUES(24, 'double', 9, 200.00);
INSERT INTO HOTEL.ROOM VALUES(24, 'suite', 78, 600.00);
INSERT INTO HOTEL.ROOM VALUES(25, 'single', 44, 100.00);
INSERT INTO HOTEL.ROOM VALUES(25, 'double', 115, 190.00);
INSERT INTO HOTEL.ROOM VALUES(25, 'suite', 6, 450.00);

INSERT INTO HOTEL.CUSTOMER VALUES(1000, 'Mrs', 'Jenny', 'Porter', '1340 N. Ash Street, #3', '10580');
INSERT INTO HOTEL.CUSTOMER VALUES(1001, 'Mr', 'Peter', 'Brown', '1001 34th St., APT.3', '48226');
INSERT INTO HOTEL.CUSTOMER VALUES(1002, 'Company', NULL, 'Datasoft', '486 Maple St.', '90018');
INSERT INTO HOTEL.CUSTOMER VALUES(1003, 'Mrs', 'Rose', 'Brian', '500 Yellowstone Drive, #2', '75243');
INSERT INTO HOTEL.CUSTOMER VALUES(1004, 'Mrs', 'Mary', 'Griffith', '3401 Elder Lane', '20005');
INSERT INTO HOTEL.CUSTOMER VALUES(1005, 'Mr', 'Martin', 'Randolph', '340 MAIN STREET, #7', '60615');
INSERT INTO HOTEL.CUSTOMER VALUES(1006, 'Mrs', 'Sally', 'Smith', '250 Curtis Street', '75243');
INSERT INTO HOTEL.CUSTOMER VALUES(1007, 'Mr', 'Mike', 'Jackson', '133 BROADWAY APT. 1', '45211');
INSERT INTO HOTEL.CUSTOMER VALUES(1008, 'Mrs', 'Rita', 'Doe', '2000 Humboldt St., #6', '97213');
INSERT INTO HOTEL.CUSTOMER VALUES(1009, 'Mr', 'George', 'Howe', '111 B Parkway, #23', '75243');
INSERT INTO HOTEL.CUSTOMER VALUES(1010, 'Mr', 'Frank', 'Miller', '27 5th St., 76', '95054');
INSERT INTO HOTEL.CUSTOMER VALUES(1011, 'Mrs', 'Susan', 'Baker', '200 MAIN STREET, #94', '90018');
INSERT INTO HOTEL.CUSTOMER VALUES(1012, 'Mr', 'Joseph', 'Peters', '700 S. Ash St., APT.12', '92714');
INSERT INTO HOTEL.CUSTOMER VALUES(1013, 'Company', NULL, 'TOOLware', '410 Mariposa St., #10', '20019');
INSERT INTO HOTEL.CUSTOMER VALUES(1014, 'Mr', 'Antony', 'Jenkins', '55 A Parkway, #15', '20903');

INSERT INTO HOTEL.RESERVATION VALUES(1, 100, 1000, 11, 'single', '2020-12-24', '2020-12-27');
INSERT INTO HOTEL.RESERVATION VALUES(2, 110, 1001, 11, 'double', '2020-12-24', '2021-01-03');
INSERT INTO HOTEL.RESERVATION VALUES(3, 120, 1002, 15, 'suite', '2020-11-14', '2020-11-18');
INSERT INTO HOTEL.RESERVATION VALUES(4, 130, 1009, 21, 'single', '2019-02-01', '2019-02-03');
INSERT INTO HOTEL.RESERVATION VALUES(5, 150, 1006, 17, 'double', '2019-03-14', '2019-03-24');
INSERT INTO HOTEL.RESERVATION VALUES(6, 140, 1013, 20, 'double', '2020-04-12', '2020-04-30');
INSERT INTO HOTEL.RESERVATION VALUES(7, 160, 1011, 17, 'single', '2020-04-12', '2020-04-15');
INSERT INTO HOTEL.RESERVATION VALUES(8, 170, 1014, 25, 'suite', '2020-09-01', '2020-09-03');
INSERT INTO HOTEL.RESERVATION VALUES(9, 180, 1001, 22, 'double', '2020-12-23', '2021-01-08');
INSERT INTO HOTEL.RESERVATION VALUES(10, 190, 1013, 24, 'double', '2020-11-14', '2020-11-17');

INSERT INTO HOTEL.MAINTENANCE VALUES(10, 26, 'Replace pool liner and pump', '2019-03-21', 'Discount Pool Supplies');
INSERT INTO HOTEL.MAINTENANCE VALUES(11, 26, 'Renovate the bar area.  Replace TV and speakers', '2020-11-29', 'TV and Audio Superstore');
INSERT INTO HOTEL.MAINTENANCE VALUES(12, 26, 'Roof repair due to storm', null, null);
  • After executing the SQL, you will have tables in SAP HANA Cloud representing enterprise or business-like data.
  • Next, let us download the Shapefile (.shp.zip) for “Points of Interest in and around Longview, Texas” from ArcGIS Hub by clicking on Download icon.
  • Once the shape file is downloaded, next step is to import it into our trial instance via SAP HANA Cloud Database explorer. The steps to do this are outlined in detail by my colleague Daniel Van Leeuwenin his hands-on tutorial: Import and view spatial data. Follow through the tutorial steps to successfully import the Esri shapefile.

Here is the detailed walkthrough video for the above steps:

The next part of this step is to run spatial queries and perform analysis in Esri’s ArcGIS Pro.

Scenario: The hotel ‘Bella Cliente’ desires to list out all parks within three kilometer vicinity as a service to the hotel’s customers.

Here are the SQL queries to be incorporated as query layers:

Query Layer 1: Visualize the Hotel – “Bella Cliente”

SELECT HNO,NAME,ADDRESS,LOCATION from HOTEL.HOTEL where HNO = '26'

Query Layer 2: Visualize parks in radius of 3 Km from the hotel Bella Cliente

SELECT
P.NAME,
P.FCODE,
P.ADDRESS,
P.SHAPE,
ROUND(H.LOCATION.ST_Distance(P.SHAPE, 'kilometer'), 2) as DISTANCE,
P.LINKED_URL
FROM HOTEL.MAINTENANCE M, HOTEL.HOTEL H, HOTEL.POI_LONGVIEW P
WHERE
H.HNO=26 /*Bella Cliente */ AND
P.FCODE like '%PARK%' AND
TO_BOOLEAN(H.LOCATION.ST_WithinDistance(NEW ST_Point(SHAPE.ST_AsWKT(), 4326), 3, 'kilometer')) = TRUE
ORDER BY DISTANCE ASC

Here are the steps to accomplish our query layer implementation and data visualization:

  • Click on Add Data dropdown and choose Query Layer
  • On the new Query Layer pop up that opens, choose the SAP HANA Cloud trial instance connection that we established in step 4. Note: Once the database connection is chosen, you can see the list of tables from the trial instance on the left
  • Enter the name of query layer as ‘HotelBellaCliente’
  • Copy & paste the given SQL query 1 in the Query box and click on Validate.
  • Once the query validation is successful click on Next
  • Choose the Unique Identifier field – ‘NAME‘
  • On the Spatial Properties section, select geometry type as ‘Points’
  • Enter the SRID as ‘4326’ (if already existing, leave it as is) and click on Finish

You can now see the query layer “HotelBellaCliente” on the left-hand side and on the Map area you can see the point of interest that is the hotel itself. Now repeat the same steps as above for the next query layer – “NearbyParks” using SQL query 2.

To make your visualization more colorful and interactive you have an option to choose custom symbols to represent the points on map.

If you would like to see a data preview, right click on the query layer and select ‘Attribute Table’. The result can also be exported if needed.

Here is the detailed video of the above steps:

Conclusion

Kudos, job well done!! You have completed this tutorial working with SAP HANA Cloud on Esri’s ArcGIS Pro platform.

From here, there’s a lot you can do – you can analyze, visualize spatial data on maps and utilize the advanced ArcGIS enterprise geodatabase features – and do all of that against an enterprise geodatabase in SAP HANA Cloud.

I hope the above hands-on blog was useful, insightful and you had a great learning journey. We encourage you to now dive deeper into the spatial arena and to build more interactive maps and query layers with SAP HANA Cloud & Esri’s ArcGIS Pro together. Enjoy!!

 

Useful links:

Tags: Cloud SAP HANA Cloud SAP HANA Spatial
Sara Sampaio

Sara Sampaio

Author Since: March 10, 2022

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x