SAP BTP Data & Analytics Showcase – How to prepare geospatial data in SAP Data Warehouse Cloud
This blog post refers to a series of blogs around SAP’s Business Technology Platform which aim to demonstrate an end-to-end scenario across the SAP HANA Database & Analytics Portfolio. It is part three out of five and it aims to demonstrate how to model geospatial data coming from SAP HANA Cloud in SAP Data Warehouse Cloud. In part one, data which shows real-time prices of different gas stations in Germany was loaded to SAP HANA Cloud via Rest APIs using SAP Data Intelligence.
While the previous part of our blog series is rather targeting IT users, this blogs post addresses business users with a basic understanding in Data Modeling. In our case, a BI modeler named Daniel is experienced in data modeling in SAP Data Warehouse Cloud and has knowledge in creating analytical dashboards in SAP Analytics Cloud.
Before he can start, Daniel needs to create a space in SAP Data Warehouse Cloud and connect to his SAP HANA Cloud instance. After that, he can model his data accordingly in SAP Data Warehouse Cloud’s Data Builder.
Section 1: Create a space in SAP Data Warehouse Cloud and connect to SAP HANA Cloud
Spaces are a feature in SAP Data Warehouse Cloud and represent virtual workspaces for an individual or a group of users which empower business users to model and integrate data in a governed environment. The system administrator can assign quotas for available disc space, CPU usage, runtime hours and memory usage. By selecting the space management section on the taskbar on the right, Daniel will receive an overview about all his spaces.
Figure 1: SAP Data Warehouse Cloud home screen
To create a new space, Daniel needs to click on the “plus” (+) icon on the top right and enter a space name. In a next step, he can assign storage and in-memory acceleration to his space.
Figure 2: Space Management home screen
The “connection” section provides the opportunity to connect the SAP Data Warehouse Cloud instance to a variety of SAP and Non-SAP as well as Cloud and On-Premise sources. By clicking on the “plus” (+) icon on the connection section, a connection management window will open.
Figure 3: Connection Management in SAP Data Warehouse Cloud
He can connect to his SAP HANA Cloud instance by selecting the SAP HANA pane, adding his connection details (Cloud/On-Premise, Host, Port) and entering his credential details.
Figure 4: Connection to SAP HANA Cloud
After connecting to his SAP HANA Cloud instance, he can access his SAP HANA Tables in SAP Data Warehouse Cloud.
Section 2: Prepare SAP HANA Tables for Consumption with SAP Data Warehouse Cloud’s Data Builder
Since he would like to visualize his data as a GeoMap, Daniel needs to prepare his data before he can consume it. This can be easily done by creating a dimension view with a new Geo Enrichment feature in SAP Data Warehouse Cloud.
Step 1: Creation of Dimension View
First, Daniel creates a new graphical view and drags and drops the previously created station master data file with the longitude and latitude information to the canvas. In a next step, he defines a business and technical name and sets the semantic usage to dimension:
After that, he selects the Station Master Data node and clicks on the node “fx” to add a new calculated column:
After navigating to the details on the right side of his screen, he can click on the “plus” (+) icon on to add a new “Geo-Coordinates Column”:
Now, he only needs to enter a name for his column and map the longitude and latitude columns to create the Location Dimension. To finalize these steps, Daniel saves and deploys this dimension:
Step 2: Creation of the Analytical Dataset
After bringing the geospatial data in the master data file into the right format, he needs to join this data with his prices dataset.
Therefore, Daniel needs to create another graphical view. He drags and drops the prices dataset as well as the master data file which were created in our previous blog to the canvas and maps the respective identifier columns to join the two datasets. Additionally, he adds the newly created Dimension View as an association to the joined dataset:
To visualize this data in SAP Analytics Cloud, this dataset needs to be defined as an analytical dataset. Therefore, Daniel selects “Analytical Dataset” as Semantic Usage and defines the prices of our different gas types (Diesel, E5, E10) as measures. Finally, he exposes the data for consumption:
After conducting those steps, Daniel has joined the gas prices on different days with the station master data. In addition, the data is now in the right format to be visualized as a GeoMap by SAP Analytics Cloud.
The following video will give you a more detailed impression of how those steps do look like in SAP Data Warehouse Cloud:
Please note that the blog post has been updated to show the new Geo Enrichment Feature. The video does not include this feature but it shows how to prepare geospatial data with multiple SQL statements. Please see also this blog for more information about the new Geo Enrichment feature in SAP Data Warehouse Cloud.
In this blog post you have learned how to create a space in SAP Data Warehouse Cloud as well as how to connect to an SAP HANA Cloud instance. Further, you could learn how to prepare geospatial data to make it consumable by SAP Analytics Cloud. This data is now ready to be visualized by business users, which is demonstrated in this part of our SAP BTP Data & Analytics blog series:
- SAP BTP Data & Analytics – How to visualize geospatial data from SAP Data Warehouse Cloud in SAP Analytics Cloud
Additionally, I would like to thank my colleagues Wei Han, Lukas Schoemig, Axel Meier and Abassin Sidiq for the great collaboration on this blog series!
This presentation, or any related document and SAP’s strategy and possible future developments, products and or platforms directions and functionality are all subject to change and may be changed by SAP at any time for any reason without notice.