In this blog post, I will share how to transform and store spatial data in SAP HANA Cloud and directly expose into SAP Analytics Cloud using Calculation Views created within SAP Business Application Studio.
This implementation was based on a scenario where spatial data was collected from 3rd party APIs from IoT devices and sent through SAP Cloud Platform Integration and Node js applications into SAP HANA Cloud.
Once the spatial data was stored in SAP HANA Cloud, it was transformed into meaningful insights.
Refer to blog Creating an SAC Geo Map from WebIDE (HDI) based Calculation Views by Ian Henry for setting up a SAP Business Application Studio project with the correct namespace and making sure the system has spatial reference system ID 3857. This is required for SAP Analytics Cloud reporting.
- Transform Location Data
- Create Geo Cube/Dimensional Calculation Views with Shapes
- Create a Live Connection Data model in SAP Analytics Cloud based on Calculation Views
- Create a Geo Map Story
1. Transform Location Data
The spatial data in the SAP HANA Cloud table exists as two separate columns: longitude and latitude.
The table contains data about truck trips. A truck trip forms a route. In this scenario, we are going to make polygons out of the route. In order to create a polygon the first and last point on the trip have to be identical.
We will be creating a new table to hold the polygon shape data for each truck trip using the datatype ST_GEOMETRY(3857).
CREATE COLUMN TABLE "SPATIAL_PRACTICE"."POLYGONS" ( "TRIP_ID" NVARCHAR(150) NOT NULL , "POLYGON" ST_GEOMETRY(3857) INTERNAL LAYOUT PLAIN SPATIAL INDEX PREFERENCE DEFAULT VALIDATION BASIC BOUNDARY CHECK OFF) UNLOAD PRIORITY 5 AUTO MERGE;
We will use a SELECT statement to get the trip data from the table and using the ST_GeomFromTxt spatial function to string together the longitude and latitude columns to form the polygon shape. The shape will then be inserted into the new table.
- Longitude and latitude points are in SRID 4326. The ST_Transform function converts the points to SRID 3857.
- We added a new column NEWID to the table based off of the original ID column. This column is used in determining the order of points.
INSERT INTO SPATIAL_PRACTICE.POLYGONS (TRIP_ID, POLYGON) select TRIP_ID, ST_GeomFromText('Polygon((' || STRING_AGG(LONGITUDE || ' ' || LATITUDE, ',' ORDER BY NEWID) || '))', 4326).ST_TRANSFORM(3857) from SPATIAL_PRACTICE.TRUCK_TRIPS GROUP BY TRIP_ID;
2. Create Geo Cube/Dimensional Calculation Views with Shapes
In the SAP Business Application Studio, create two new calculation views using the new polygon table. One will be a cube and one will be a dimensional view.
2a. Create a Cube Calculation View
This calculation view can be created under the src/Models folder within the database module. A cube view is needed for SAP Analytics Cloud reporting.
Since it is a cube, I have added a count measure and used the spatial function ST_AREA() to determine the area of each polygon as well as ST_BUFFER() to add additional area onto the polygon within a calculated column. These spatial functions do not need to be included.
2b. Create a Dimensional Calculation View
This calculation view needs to be created under the src/SAP_BOC_SPATIAL folder for SAP Analytics Cloud reporting (check pre-requisites).
It is important to change the names and labels of the columns so that they do not match the previous calculation view. This is a limitation when creating a location dimension in SAP Analytics Cloud. No additional columns were added here.
3. Create a Live Connection Data model in SAP Analytics Cloud based on Calculation Views
Make sure a role has been created in the HDI container for calculation view consumption and assign it to the SAP Analytics Cloud user which is used to create the connection from SAP HANA Cloud to SAP Analytics Cloud.
In SAP Analytics Cloud data modeler, create a live data model and select the cube calculation view.
In the modeler, you can see the measures from the view and all the dimensions.
Add a location dimension by selecting the trip id from the cube view and choosing the dimensional calculation view. You will notice the Location Dimension Name is the polygon shape. The Location Identifier and the Identifier for Mapping need to be the same column from both calculation views which is used for joining in the model.
4. Create a Geo Map Story
In SAP Analytics Cloud, create a responsive story based on the presentation template. Click the + sign on the map.
In Edit and Designer mode, add a layer and choose the live data model we just created.
Choose the Choropleth/Drill layer with Bubbles (remove the hierarchies of the layer).
Under the Location Dimension, add the polygon shape.
You can add measures like POLYGON_AREA to change the colors of the polygons.
Using the Spatial engine and functions, you now have transformed spatial data into shapes and stored the shapes directly in SAP HANA Cloud. With SAP Business Application Studio you created calculation views that are used in SAP Analytics Cloud for reporting.
Try it out yourself!
If you would like to set up the same in your own environment, refer to the mission “Visualize Truck Routes & Hazards Using Geo Spatial Processing” in the SAP Discovery Center. Instead of direct shape consuming in SAP Analytics Cloud, the mission shows how to create linestrings for truck routes that are not complete polygons. Also, see more about the mission in the blog post Discovery Mission: Visualize Hazards & Truck Routes using SAP HANA Cloud – The Engine for Spatial Analytics by Vivek RR which highlights another spatial transformation scenario.