Using Choropleth Layers with HANA Cloud and SAP An...

Ian_Henry · ‎03-04-2021

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.

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
- See my blog Importing shapefiles into SAP HANA Cloud using the Wizard

SAC Location Dimension
- Another blog Creating an SAC Geo Map from WebIDE (HDI) based Calculation Views

SAP Analytics Cloud Spatial MTARs
- See SAP Note 2395407 - Spatial Delivery Units for SAP Analytics Cloud

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

yannmiquel · ‎03-08-2021

Hi Ian,

First of all, thank you very much for this ! Wonderful work

In my project, in SAC, the area adds the value of the neighbor shapes too. Do you see any reason for this behavior ?

Here, Henegouwen has in fact a value of 0 but it sums the 3 adjacent regions (382 = 281+26+75)

I'm using the shapes provided by SAP

Yann

Ian_Henry · ‎03-11-2021

Hi Yann,

I have not come across this type of issue. I would expect the shapes to aggregate the points but not those of their neighbours. Is it the shape or the point that displays the wrong value?

It would make sense to log a support ticket so that this can be investigated further.

yannmiquel · ‎03-11-2021

Hi Ian,

Thanks for your feedback

[update] found the issue: one must use SHAPEPOINT instead of SHAPE in SAC. Else it aggregates the neighbors

yannmiquel · ‎03-18-2021

Hi Ian,

Did you manage to use the drill up/down button in SAC with your custom hierarchies ?

It only works with the "Default hierarchical data" here.

If not, do you see an option to disable this feature ?

Thanks

Yann

Ian_Henry · ‎03-18-2021

Hi Yann,

Yes, drill up and down work for me. I have created an animated gif (Figure 4.4) that shows this.

The issue you have is likely caused by your shapes not fitting precisely within each other.

The older documentation for this feature states.

The shapes at level N must fit under shapes specified in level N – 1. 

For example, if level 1 is USA, that means all level 2 shapes must fit 

inside USA. If the shapes are outside or even partially outside USA, 

SAP Analytics Cloud may not be able to properly navigate up and down 

your custom hiearchy. Also, your model’s measures values will not be 

aggregated correctly across the hierarchy.

yannmiquel · ‎03-18-2021

Thank you !!! Your the cause for renewed hope

I will try asap

liliana_cantero2 · ‎04-21-2021

Hello Ian,
Thanks for remembering to share the link for the choropleths.
I wanted to ask you if geospatial shape modeling is possible with HANA Services instead of HANA Cloud? Do you know or do you have any experience or knowledge that someone has done it? Currently I am working with Hana Services and SAC Cloud Foundry.
Thank you !

celo_berger · ‎04-29-2021

Hi Ian,

Thank you so much for this. I'm in the process of a cutover from a Neo HANA DB and Neo SAC to HANA Cloud DB and SAC CF, and these steps were what I was looking for.

I do have a question and an extra step I had to add in order for the choropleth to work in SAC:

Question: when creating the public synonyms, do those statements need to be run from the SAP_FPA_SPATIAL_CUSTOM_REGIONS container or can the SQL be executed from the main DB?

Extra Step: I have a container (C_GEOSPATIAL) where I created the views in your step 2.2. I had to create a role with SELECT and EXECUTE privileges, and then grant this role to the technical user used in the SAC connection to HANA Cloud, in my case HANA_SAC:

Role below:

{
"role": {
"name": "P_Geospatial.db.Roles::CC_GEOSPATIAL_USER",
"schema_privileges": [{
"privileges": ["SELECT", "EXECUTE"]
}]
}
}

SQL statement run from main DB:

grant "C_GEOSPATIAL"."P_Geospatial.db.Roles::CC_GEOSPATIAL_USER" to HANA_SAC

After granting the role, I was able to display the choropleth layers in SAC. Just checking if this was missed on your end, or if I possibly did something wrong/different from your instructions.

Or possibly you simply created the views in step 2.2 in the same container as the SAP_FPA_SPATIAL_CUSTOM_REGIONS ?

Anyway, thanks for this informative blog!

Marcelo!

Ian_Henry · ‎04-29-2021

Hi Liliana,

The steps should be identical for any HANA deployment where you are using HDI containers.
I haven't tried with HANA as a Service or HANA 2.x, but there's nothing HANA Cloud specific.
You should be able to use the WebIDE and/or the CF command line to deploy the mtars.

Cheers, Ian.

Ian_Henry · ‎04-29-2021

Thanks Marcelo,

No, I didn't deploy into the SAP_FPA_SPATIAL_CUSTOM_REGIONS container.

You're right - Yes, your SAC HANA user will of course need access to the Calc Views.

My SAC HANA user already has access to the container with my Calc Views. You could say I skipped that step, I was capturing the additional steps required for Choropleth layers.

Cheers, Ian.

celo_berger · ‎04-29-2021

Thanks Ian for confirming. Just want to ensure I'm doing this correctly for future reference.

It's quite the paradigm shift going from XSC to XSA, not necessarily on the modelling, but the environmental setup, and the pre-requisites for actually being able to model, specifically with containers and cross container access, so I just want to be certain that I'm approaching this correctly and following the appropriate steps.

Once again, thank you for the blog!

Cheers, Marcelo!

bert_baeckelmans · ‎05-08-2023

Hi Ian,

Thank you so much for this blog post!

Only thing what I'm facing is that I cannot deploy my project because my dimension calculation view requires "db://NEDERLAND_LPROVINCIE" --> Table containing geo shape information which is not provided by any file [8212001].

All stept which you mentioned were followed. Am I missing any authorizations,... Any idea what this could be?

Thanks in advance,

Bert

Ian_Henry · ‎05-09-2023

Thanks Bert,

It could be to related to the way namespaces are handled in your project.

Did you also read this blogpost https://blogs.sap.com/2020/07/21/creating-an-sac-geo-map-from-webide-hdi-based-calculation-views/ ?

Do you have a table in your project containing your custom shapes?

bert_baeckelmans · ‎05-09-2023

Hi Ian,

I prepared the shapefile as you mentioned in following blog: https://blogs.sap.com/2021/01/18/importing-shapefiles-into-sap-hana-cloud-using-the-wizard/

So the data is imported into SAP HANA Cloud DB. In a next step I created a new table to get this data in the shape as ST_GEOMETRY(3857). This was succesfull.

While creating the column table, no namespace was defined.
In Business Application Studio I don't have a table defined in my project containing the custom shapes. The table containing custom shapes is only residing in HANA DB Explorer.

Thanks!

Ian_Henry · ‎05-09-2023

Hi Bert,

I understand the issue now 🙂

With HDI containers you need a UPS (User Provided Service) to access data outside your container. There are many blogs covering process, it's often referred to as cross-schema access. Unfortunately it's quite an involved process.

I see Thomas Jung wrote a recent (2022) blogpost on this.

I also wrote one many years ago (2019) https://blogs.sap.com/2019/04/02/xsa-accessing-remote-sources-external-objects-schemas-etc/, most of it still applies but some steps may be slightly different for HANA Cloud today.

bert_baeckelmans · ‎06-06-2023

Hi Ian,

Thanks for your reply! Does this mean that in your demo scenario here, you also used a User Provided Service in order to read the table residing in HANA Database in your Calculation View?

I think I'm close to the final solution. I am able to create a model based on calculation view in 1 container and create a location dimension with the geo spatial (shapefile) data residing in other container and schema SAP_BOC_SPATIAL. Once I create the geo map, this error returns...

I'm really new to all of this, so quite overwhelming

Kind regards,

Bert

Ian_Henry · ‎06-30-2023

Hi Bert,

Yes, when accessing data outside of your HDI container then the User Provided Service is required and you must go through those steps. UPS, grants and synonyms.

Do both your views CV_FACT_GEOTEST and CV_SAP_SPATIAL work in Database Explorer with the same HANA database user that you are using in SAC?

bert_baeckelmans · ‎07-04-2023

Hi Ian,

Both views CV_FACT_GEOTEST and CV_SAP_SPATIAL work in DB Explorer with the same HANA database user which I am using in SAC.

I will try again by creating new User Provided Service.