If you have some spatial shapes in HANA Cloud, that you would like to visualise with SAP Analytics Cloud (SAC) you are in the right place.
To use the SAC Geospatial choropleth layer, you need a couple of helper delivery units that are supplied as Multi Target Archives (MTARs). First you must deploy the .mtar files into your HANA Cloud environment. Once installed you can use both the standard choropleth shapes and hierarchies and custom choropleth shapes and hierarchies.
The Steps described in this blog are
This capability was released with the SAP Analytics Cloud Q4 2020 Update.
Requirements
You can find the official Analytics Cloud documentation for Creating Geo Spatial Models from HANA Calculation Views.here.
Search for ANALYTICS_CLOUD in Software Downloads
https://launchpad.support.sap.com/#/softwarecenter/search/analytics_cloud
Business Application Studio, provides a pre-configured environment for development and deployment. It can be used to deploy the two provided SAC spatial .mtar files. SAP Business Application Studio can be found in the SAP Cloud Platform Cockpit (aka BTP Cockpit) at the sub account level, navigate to Service Marketplace.
Download and extract the latest spatial content ANALYTICS_CLOUDXX_X-XXXX.zip file.
You should have four files. you require the two mtar files similar those below.
FPA_SPATIAL_DATA_1.1.24.mtar
FPA_SPATIAL_CUSTOM_REGIONS_1.0.0.mtar
Upload the two mtars containing the SAC geospatial choropleth layers into your Business Application Studio workspace.
If you only have one HANA Cloud instance you can directly deploy the .mtar. by selecting the .mtar and choosing Deploy MTA Archive from the right click context menu.
If you have multiple HANA Cloud instances within the Cloud Foundry sub-account, space, you need to tell the deployer which instance to target. This is done using a MTA Deployment Extension Descriptor file .mtaext file. You will need to create a .mtaext for each mtar before deploying. In this extension file you will specify the database id to target the correct HANA instance.
You can now deploy the mtars, it will target the sub account and space that you are currently logged into.
Predefined HANA Roles are included with the mtars. These need to be granted to your HANA Cloud users, that will be used within SAC.
The editor role is only required for developers to insert, update, delete new layers in the CUSTOM_HIERARCHY table.
If you are using the HANA Cloud Cockpit, deselect the "HDI Container API" button
The spatial content is deployed as HDI containers on the HANA Cloud system when the MTARs are deployed. SAC uses Public Synonyms so that it doesn't matter where the data is actually deployed on your HANA Cloud system.
Within SAC, go to System Administration and enable "Use Public Synonyms for Choropleth Layer".
You can search for this setting. To enable the switch, press edit and then save.
The installation and configuration of the SAC spatial content for the choropleth drill layer is now completed.
To use the choropleth layer in SAC you need to follow a few steps.
Previously I have described how to upload shapefiles to HANA Cloud.
For the SAC Geo Map choropleth layer you need the shape data as ST_GEOMETRY(3857). Currently my data is using spatial reference 4326, it therefore needs to be transformed.
If you really want to see what the SHAPE data looks like I would recommend using DBeaver.
SAC can only access calculation views, you therefore need to expose this table as a dimension calculation view.
In SAP Business Application Studio, go to View -> Find Command -> "hana" ->
SAP HANA: Create SAP HANA Database Artifact -> Artifact Type -> Calculation View
Data Category - > Dimension
The only configuration required is to specify the ID as a Key in the semantics node.
SAC has a metadata table in HANA that stores the available choropleth layers.
This is held in the table "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY"
By default the table should have the Default Geographical Data as below
You need to tell SAC about the new shape data. You can do that with the following SQL statement
In SAC, I have already have a location dimension, I can now change the "Layer Type" to choropleth / drill layer and select the "English Regions" hierarchy.
If you don't see the location options go to the model and press Save to update the metadata.
Using Business Application Studio, I can add further choropleth layers to my custom hierarchy using additional dimensional calculation views. These can be in the same project (HDI container) or from different HDI containers. Below I have added the required fields and logic within a calculation view.
I need to tell SAC about this new choropleth layer by adding to to the customer metadata.
Moving back to SAC, you now see an additional level in the hierarchy for drill down possible.
Extending the hierarchies further, you can combine default hierarchies with your custom hierarchies. The default (SAP supplied) SAC hierarchies include country and region. These default hierarchies can be included to improve your custom hierarchies.
When reusing the standard hierarchy levels in your mixed custom hierarchy, the following standard levels are defined:
If you want to use any of the standard hierarchy levels, the level values must be defined as above. For example, if you want to reuse SUB-REGION1 shapes, it must always be defined as Level 3 in your custom hierarchy.
If you change the metadata, this can be cached by the MDS (MultiDimensional Services).
To force the MDS cache to be updated, go to your model and press Save.
If your geo maps are still not working, check the location dimension is still valid and defined as expected.
To use the SAC Geospatial choropleth layer, you need a couple of helper delivery units that are supplied as Multi Target Archives (MTARs). First you must deploy the .mtar files into your HANA Cloud environment. Once installed you can use both the standard choropleth shapes and hierarchies and custom choropleth shapes and hierarchies.
Figure 1: Example Choropleth
The Steps described in this blog are
- Deploy and configure the Spatial Content (.mtar)
- Create Choropleth Custom Hierarchies
- Add Additional Choropleth Layers
- Combine default hierarchies with custom hierarchies
- Troubleshooting
This capability was released with the SAP Analytics Cloud Q4 2020 Update.
Requirements
- Spatial shape data imported in HANA Cloud with ST_GEOMETRY or similar datatypes
- SAC Location Dimension
- SAP Analytics Cloud Spatial MTARs
- Business Application Studio or Cloud Foundry (cf) command line
You can find the official Analytics Cloud documentation for Creating Geo Spatial Models from HANA Calculation Views.here.
1. Deploy and configure the Spatial Content (.mtar)
1.0 Download the GEO CONTENT
Search for ANALYTICS_CLOUD in Software Downloads
https://launchpad.support.sap.com/#/softwarecenter/search/analytics_cloud
Figure 1.0: Download the latest GEO CONTENT ANALYTICS CLD
1.1 Upload the mtar files
Business Application Studio, provides a pre-configured environment for development and deployment. It can be used to deploy the two provided SAC spatial .mtar files. SAP Business Application Studio can be found in the SAP Cloud Platform Cockpit (aka BTP Cockpit) at the sub account level, navigate to Service Marketplace.
Download and extract the latest spatial content ANALYTICS_CLOUDXX_X-XXXX.zip file.
You should have four files. you require the two mtar files similar those below.
FPA_SPATIAL_DATA_1.1.24.mtar
FPA_SPATIAL_CUSTOM_REGIONS_1.0.0.mtar
Upload the two mtars containing the SAC geospatial choropleth layers into your Business Application Studio workspace.
Figure 1.1: Upload Files
Figure 1.2: SAP Supplied Spatial MTARs
If you only have one HANA Cloud instance you can directly deploy the .mtar. by selecting the .mtar and choosing Deploy MTA Archive from the right click context menu.
Figure 1.3: Deploy MTA Archive
1.2 Specify HANA Cloud Database ID
If you have multiple HANA Cloud instances within the Cloud Foundry sub-account, space, you need to tell the deployer which instance to target. This is done using a MTA Deployment Extension Descriptor file .mtaext file. You will need to create a .mtaext for each mtar before deploying. In this extension file you will specify the database id to target the correct HANA instance.
Figure 1.4: FPA_SPATIAL_DATA.mtaext
_schema-version: "3.0.0"
ID: MY_DATABASE_ID_FOR_FPA_SPATIAL_DATA
description: Provides the HANA Cloud Database ID to deploy the shape data for SAC choropleth layer
extends: FPA_SPATIAL_DATA
resources:
- name: FPA_SPATIAL_DATA-hdi
parameters:
config:
database_id: <insert your database ID here>Figure 1.5: SAP_FPA_SPATIAL_CUSTOM_REGIONS.mtaext
_schema-version: "3.0.0"
ID: MY_DATABASE_ID_FOR_SPATIAL_CUSTOM_REGION
description: Provides the HANA Cloud Database ID to deploy the custom hierarchy table for use with SAP Analytics Cloud
extends: SAP_FPA_SPATIAL_CUSTOM_REGIONS
resources:
- name: SAP_FPA_SPATIAL_CUSTOM_REGIONS-hdi
parameters:
config:
database_id: <insert your database ID here>1.4 Deploy the mtars
You can now deploy the mtars, it will target the sub account and space that you are currently logged into.
cd mta_archives
mta_archives $ cf deploy FPA_SPATIAL_DATA_1.1.24.mtar -e FPA_SPATIAL_DATA.mtaext
mta_archives $ cf deploy FPA_SPATIAL_CUSTOM_REGIONS_1.0.0.mtar -e FPA_SPATIAL_CUSTOM_REGIONS.mtaext Figure 1.6: cf deploy command FPA_SPATIAL_DATA_1.1.24.mtar
Figure 1.7: Deployment in-progress
Figure 1.8: Deployment Finished
1.5 Grant Roles to Users
Predefined HANA Roles are included with the mtars. These need to be granted to your HANA Cloud users, that will be used within SAC.
The editor role is only required for developers to insert, update, delete new layers in the CUSTOM_HIERARCHY table.
# Viewing Spatial choropleth
GRANT FPA_SPATIAL_DATA."FPA_SPATIAL_DATA::PublicAccessObjects" TO I049374;
GRANT SAP_FPA_SPATIAL_CUSTOM_REGIONS."sap.fpa.services.spatial::ViewerRole" TO I049374;
# Editing & Creating custom choropleth layers
GRANT SAP_FPA_SPATIAL_CUSTOM_REGIONS."sap.fpa.services.spatial::EditorRole" TO I049374;If you are using the HANA Cloud Cockpit, deselect the "HDI Container API" button
1.6 Create Public Synonyms
The spatial content is deployed as HDI containers on the HANA Cloud system when the MTARs are deployed. SAC uses Public Synonyms so that it doesn't matter where the data is actually deployed on your HANA Cloud system.
create PUBLIC synonym SAC_CHOROPLETH_DATA for "FPA_SPATIAL_DATA"."FPA_SPATIAL_DATA.choropleth::CHOROPLETH" ;
create PUBLIC synonym SAC_CHOROPLETH_HIER for "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial.choropleth::CHOROPLETH_CUSTOM_HIERARCHY" ;1.7 Enable Public Synonyms SAC Toggle in System Configuration
Within SAC, go to System Administration and enable "Use Public Synonyms for Choropleth Layer".
You can search for this setting. To enable the switch, press edit and then save.
Figure 1.9: Enable Choropleth Layer
The installation and configuration of the SAC spatial content for the choropleth drill layer is now completed.
2. Create Choropleth Custom Hierarchies
To use the choropleth layer in SAC you need to follow a few steps.
2.1 Prepare shape data
2.2 Expose shape data as dimension calculation view
2.3 Update SAC Metadata with details of new dimension calculation view
2.4 Create Choropleth Geo Map in SAC
2.1 Prepare Shape Data
Previously I have described how to upload shapefiles to HANA Cloud.
| Column Name | Data Type | Description |
| ID | INT | Primary Key |
| NAME | VARCHAR(100) | SAC Display Name for Shape/Region |
| SHAPE | ST_GEOMETRY(3857) | This the choropleth shape |
| SHAPEPOINT | ST_POINT(3857) | Centre of the Shape, can be calculated with ST_Centroid() see SQL below. |
| IS_LEAF | BOOLEAN | Usually false. True/false flag to specify whether or not to drill directly to the data points. |
For the SAC Geo Map choropleth layer you need the shape data as ST_GEOMETRY(3857). Currently my data is using spatial reference 4326, it therefore needs to be transformed.
CREATE COLUMN TABLE ENGLAND_L1 (ID INT PRIMARY KEY, NAME VARCHAR(100), SHAPE ST_GEOMETRY(3857), SHAPEPOINT ST_POINT(3857), IS_LEAF BOOLEAN);
INSERT INTO ENGLAND_L1
SELECT ROW_NUMBER() Over () as "ID" ,"rgn19nm" as "NAME", SHAPE.ST_Transform(3857) as SHAPE, SHAPE.ST_Transform(3857).ST_Centroid() as SHAPEPOINT, false AS IS_LEAF FROM "England-Regions";
Figure 2.1: Data Preview of SHAPE table
If you really want to see what the SHAPE data looks like I would recommend using DBeaver.
2.2 Expose shape data as dimension calculation view
SAC can only access calculation views, you therefore need to expose this table as a dimension calculation view.
In SAP Business Application Studio, go to View -> Find Command -> "hana" ->
SAP HANA: Create SAP HANA Database Artifact -> Artifact Type -> Calculation View
Figure 2.2.1: Create SAP HANA Database Artifact
Data Category - > Dimension
The only configuration required is to specify the ID as a Key in the semantics node.
Figure 2.2.2: Dimension Calculation View
2.3 Update SAC Metadata
SAC has a metadata table in HANA that stores the available choropleth layers.
This is held in the table "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY"
By default the table should have the Default Geographical Data as below
SELECT * FROM "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY";Figure 2.3.1: "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY"
You need to tell SAC about the new shape data. You can do that with the following SQL statement
INSERT INTO "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY" VALUES(
'English Regions' /* NAME: Hierarchy display name shown in SAC */,
2 /* HIERARCHYID: Hierarchy ID */,
'NAME' /* COLUMN: Column storing the shape label name to show in SAC */,
'name' /* COLUMNLABEL: use text 'name' */,
6 /* ID: Unique ID for for Shape or layer */,
1 /* LEVEL: Level in Hierarchy, can use 1, 2, 3 or 1.1, 1.2 */,
'English Region' /* LNAME: Layer display name shown in SAC */,
'SHAPE' /* LOCATION: Column storing the Shape ST_GEOMETRY Label */,
'ENGLAND_REGIONS_L1' /* OBJECT:, Dimensional Calculation View Name */,
'' /* PACKAGE: Repository Package, Not used in HANA Cloud */,
'GCOE' /* SCHEMA: SCHEMA/HDI Container storing the Calculation View */,
'0' /* DEFAULT: 0 for custom regions, 1 for SAP supplied conent */
);
Figure 2.3.2: Check the updated SAC Custom Choropleth Metadata
2.4 Create Choropleth Geo Map in SAC
In SAC, I have already have a location dimension, I can now change the "Layer Type" to choropleth / drill layer and select the "English Regions" hierarchy.
If you don't see the location options go to the model and press Save to update the metadata.
Figure 2.4: SAC Choropleth Visual
3.1 Add Additional Choropleth Layers
Using Business Application Studio, I can add further choropleth layers to my custom hierarchy using additional dimensional calculation views. These can be in the same project (HDI container) or from different HDI containers. Below I have added the required fields and logic within a calculation view.
Figure 3.1: Dimensional Calc View
Figure 3.2: Calculated Columns
# Calculated Column Definitions
# SHAPE, ST_GEOMETRY
ST_Transform(ST_GeomFromWKT("SHAPE_WKT",4326),3857)
# SHAPEPOINT, ST_POINT
ST_Centroid("SHAPE")
# IS_LEAF, BOOLEAN
false
I need to tell SAC about this new choropleth layer by adding to to the customer metadata.
INSERT INTO "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY" VALUES(
'English Regions', 2, 'NAME', 'name', 7, 2, 'Local Authority', 'SHAPE', 'cv::LOCAL_AUTHORITY_DCV', '', 'COVID', '0');Figure 3.3: sap.fpa.services.spatial.choropleth::CHOROPLETH_CUSTOM_HIERARCHY
Moving back to SAC, you now see an additional level in the hierarchy for drill down possible.
Figure 3.4: Geo Map, Local Authority
4.1 Combine default hierarchies with custom hierarchies
Extending the hierarchies further, you can combine default hierarchies with your custom hierarchies. The default (SAP supplied) SAC hierarchies include country and region. These default hierarchies can be included to improve your custom hierarchies.
When reusing the standard hierarchy levels in your mixed custom hierarchy, the following standard levels are defined:
| Level | Name | Description |
| 1 | COUNTRY | |
| 2 | REGION | |
| 3 | SUB-REGION1 | Province, State |
| 4 | SUB-REGION2 | County, District |
| 5 | SUB-REGION3 | City, Town |
If you want to use any of the standard hierarchy levels, the level values must be defined as above. For example, if you want to reuse SUB-REGION1 shapes, it must always be defined as Level 3 in your custom hierarchy.
INSERT INTO "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY" VALUES(
'Country - English Regions', 3, 'AREA_NAME', 'name', 8, 1, 'Country', 'SHAPE', 'SAC_CHOROPLETH_DATA', '', 'PUBLIC', '0');
INSERT INTO "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY" VALUES(
'Country - English Regions', 3, 'AREA_NAME', 'name', 9, 2, 'Region', 'SHAPE', 'SAC_CHOROPLETH_DATA', '', 'PUBLIC', '0');
INSERT INTO "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY" VALUES(
'Country - English Regions', 3, 'NAME', 'name', 10, 3, 'English Region', 'SHAPE', 'ENGLAND_REGIONS_L1', '', 'GCOE', '0');
INSERT INTO "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY" VALUES(
'Country - English Regions', 3, 'NAME', 'name', 11, 4, 'Local Authority', 'SHAPE', 'cv::LOCAL_AUTHORITY_DCV', '', 'COVID', '0');
Figure 4.1: Data Preview of Custom Hierarchies
Figure 4.2: Select your custom hierarchy for Choropleth Drill Layer
Figure 4.3: Choropleth Hierarchy now shows four levels and the data points
Figure 4.4: Example of Choropleth drill down
5. Troubleshooting
If you change the metadata, this can be cached by the MDS (MultiDimensional Services).
To force the MDS cache to be updated, go to your model and press Save.
If your geo maps are still not working, check the location dimension is still valid and defined as expected.
Figure: 5.1 Click Save, Check Location Dimension
- SAP Managed Tags:
18 Comments
