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.
Implementation Scenarios
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.
Pre-requisites
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.
Required Steps
- 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.
Figure:1 SAP HANA Cloud Table
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.
Key Points:
- 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;
Figure 2: Example Polygon Shapes
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.
Figure 3: Database Module Structure
Figure 4: Polygon Table Added to Calculation View
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.
Figure 5: Calculation View with Measures
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).
Figure 6: Polygon Table Added to Calculation View
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.
Figure 7: Dimensional Calculation View Output Columns
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.
Figure 8: Live Data Model Connection
In the modeler, you can see the measures from the view and all the dimensions.
Figure 9: Measures from Cube Calculation View
Figure 10: Dimensions from Cube Calculation View
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.
Figure 11: Create a Location Dimension 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.
Figure 12: SAP Analytics Cloud Responsive Story Template
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).
Figure 13: Remove Default Hierarchies
Under the Location Dimension, add the polygon shape.
Figure 14: Select Polygon Shape for Location Dimension
You can add measures like POLYGON_AREA to change the colors of the polygons.
Figure 15: Add a Measure to Change Bubble Color
Conclusion
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 An... by vivek.rr which highlights another spatial transformation scenario.
Questions?
Please feel free to reach out to me or vivek.rr for any questions related to SAP HANA Spatial topics. You can see additional topics and post questions in the community page, SAP HANA Spatial.
- SAP Managed Tags:
1 Comment
