Skip to Content

Ever wondered how to take your data analysis to the next level with HANA geospatial capabilities? What if your business could leverage all kind of geospatial data and easily combine it with business data to visualize it to reveal relationships previously unseen?

This is your chance to learn how to set it up.

For one of our demo’s there was a requirement to visualize detailed data, plotted on non-standard map levels using polygons. These polygons could be roads, pipelines, buildings, etc.

This blog describes the steps taken to create a custom hierarchy in SAP Analytics Cloud (SAC) for Dutch townships, districts and neighborhoods, based on the document HANA Live Custom Regions. Below is a screenshot of the map for a sample measure:

To create this example we used the following resources:

The following steps were taken:

    1. Download map data and import into HANA
    2. Prepare HANA with required delivery units
    3. Create custom hierarchy table and views in HANA
    4. Create a sample data dimension to report on
    5. Create SAP_BOC_SPATIAL views
    6. Create SAC content

 

1. Download map data and import into HANA

First of all, a file which contains areas to be added as the custom map layers of SAC is required. In this case an ESRI™-shape file is available for download at the CBS website, linked above. If the SRID (Spatial Reference System Identifier) or EPSG (European Petroleum Survey Group) is not known it can be found by searching through the content of the .shp.xml file. In this case it was determined to be 28992.

AUTHORITY["EPSG",28992]

Before this ESRI™-shape file can be loaded into HANA the SRID has to be created. The existence can be verified with the following statement:

select * from ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID = 28992;

If it doesn’t exist, it can be created using the following statement:

CREATE SPATIAL REFERENCE SYSTEM "Amersfoort / RD New" IDENTIFIED BY 28992 
TYPE PLANAR 
COORDINATE X BETWEEN 646.3608848786971 AND 284347.25011779997 
COORDINATE Y BETWEEN 308289.5575168845 AND 637111.0245778325 
ORGANIZATION "EPSG" 
IDENTIFIED BY 28992 
LINEAR UNIT OF MEASURE "metre" 
SNAP TO GRID DEFAULT 
TOLERANCE DEFAULT 
POLYGON FORMAT 'EvenOdd' 
STORAGE FORMAT 'Internal' 
DEFINITION 'PROJCS["Amersfoort / RD New",
GEOGCS["Amersfoort",
	DATUM["Amersfoort",
		SPHEROID["Bessel 1841",6377397.155,299.1528128,
			AUTHORITY["EPSG","7004"]],
		TOWGS84[565.417,50.3319,465.552,-0.398957,0.343988,-1.8774,4.0725],
		AUTHORITY["EPSG","6289"]],
	PRIMEM["Greenwich",0,
		AUTHORITY["EPSG","8901"]],
	UNIT["degree",0.0174532925199433,
		AUTHORITY["EPSG","9122"]],
	AUTHORITY["EPSG","4289"]],
PROJECTION["Oblique_Stereographic"],
PARAMETER["latitude_of_origin",52.15616055555555],
PARAMETER["central_meridian",5.38763888888889],
PARAMETER["scale_factor",0.9999079],
PARAMETER["false_easting",155000],
PARAMETER["false_northing",463000],
UNIT["metre",1,
	AUTHORITY["EPSG","9001"]],
AXIS["X",EAST],
AXIS["Y",NORTH],
AUTHORITY["EPSG","28992"]]' 
TRANSFORM DEFINITION '+proj=sterea +lat_0=52.15616055555555 +lon_0=5.38763888888889 +k=0.9999079 +x_0=155000 +y_0=463000 +ellps=bessel +towgs84=565.417,50.3319,465.552,-0.398957,0.343988,-1.8774,4.0725 +units=m +no_defs ';

After the spatial system is created, the ESRI™-shape file can be loaded through the import option in the file menu of HANA Studio.

  1. Choose ESRI Shapefiles.
  2. Specify the location of the ESRI™-shape files.
  3. Choose which ESRI™-shape files to load.
  4. Specify the HANA schema and SRID, in this case 28992.

At this point the custom spatial data is available in HANA.

 

2. Prepare HANA with required content

As a prerequisite to show the custom hierarchy map layers in SAC it is required to import delivery units and create specific content as described in the HANA Live Custom Regions document.

Make sure SRID 3857 is created in HANA. It can be created by executing the following statement:

CREATE SPATIAL REFERENCE SYSTEM "WGS 84 / Pseudo-Mercator" 
IDENTIFIED BY 3857
TYPE PLANAR 
SNAP TO GRID 1e-4 
TOLERANCE 1e-4 
COORDINATE X BETWEEN -20037508.3427892447 AND 20037508.3427892447 
COORDINATE Y BETWEEN -19929191.7668547928 AND 19929191.766854766 
ORGANIZATION "EPSG" 
IDENTIFIED BY 3857
LINEAR UNIT OF MEASURE "metre" 
ANGULAR UNIT OF MEASURE NULL POLYGON FORMAT 'EvenOdd'
STORAGE FORMAT 'Internal' 
DEFINITION 'PROJCS["Popular Visualisation CRS / Mercator",
GEOGCS["Popular Visualisation CRS",
	DATUM["Popular_Visualisation_Datum",
		SPHEROID["Popular Visualisation Sphere",6378137,0,
			AUTHORITY["EPSG","7059"]],
		TOWGS84[0,0,0,0,0,0,0],
		AUTHORITY["EPSG","6055"]],
	PRIMEM["Greenwich",0,
		AUTHORITY["EPSG","8901"]],
	UNIT["degree",0.01745329251994328,
		AUTHORITY["EPSG","9122"]],
	AUTHORITY["EPSG","4055"]],
UNIT["metre",1,
	AUTHORITY["EPSG","9001"]],
PROJECTION["Mercator_1SP"],
PARAMETER["central_meridian",0],
PARAMETER["scale_factor",1],
PARAMETER["false_easting",0],
PARAMETER["false_northing",0],
AUTHORITY["EPSG","3785"],
AXIS["X",EAST],
AXIS["Y",NORTH]]' 
TRANSFORM DEFINITION '+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs'

Import two delivery units into HANA as described in the document or SAP Note 2395407 – Spatial Delivery Units for SAP Analytics Cloud.

After loading the delivery units permissions have to be granted to a user which will be used from the SAC connection. This can be done in a number ways, in this case through a design time role in XS Classic. The role is created in a new geo_demo package and assigned to the GEO_DEMO user.

The GEO_DEMO.hdbrole contains:

role geo_demo::GEO_DEMO extends role sap.bc.ina.service.v2.userRole::INA_USER
{
catalog schema "_SYS_BIC": SELECT;
catalog sql object "SYS"."REPOSITORY_REST": //Objecttype: PROCEDURE
	EXECUTE;
catalog sql object "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY": //Objecttype: TABLE
	SELECT, INSERT, UPDATE, DELETE;

package geo_demo: REPO.READ, REPO.EDIT_NATIVE_OBJECTS, REPO.ACTIVATE_NATIVE_OBJECTS;
package SAP_BOC_SPATIAL: REPO.READ, REPO.EDIT_NATIVE_OBJECTS, REPO.ACTIVATE_NATIVE_OBJECTS;

}

This custom role can be granted using the following statement:

CALL GRANT_ACTIVATED_ROLE('geo_demo::GEO_DEMO', 'GEO_DEMO');

After this is done, log in with the user GEO_DEMO and verify the default content can be retrieved:

SELECT * FROM "_SYS_BIC"."sap.fpa.services.spatial.choropleth/CHOROPLETH_CUSTOM_HIERARCHY"

At this point the default content to make custom hierarchies is available in HANA.

 

3. Create custom hierarchy table and views in HANA

Instead of creating tables called MyCustomHier_Level_# in this case tables are created with a meaningful name. The tables are created in the user schema to show it is not an requirement to have it in SAP_FPA_SPATIAL_CUSTOM_REGIONS. The following statements can be executed to create the empty tables:

CREATE COLUMN TABLE "GEO_DEMO"."TOWNSHIPS_2018" 
( "ID"         NVARCHAR(6)         
, "NAME"       VARCHAR(60)
, "SHAPE"      ST_GEOMETRY(3857)
, "SHAPEPOINT" ST_POINT(3857)
, "IS_LEAF"    BOOLEAN

, PRIMARY KEY ("ID")
);

CREATE COLUMN TABLE "GEO_DEMO"."DISTRICTS_2018" 
( "ID"         NVARCHAR(8)         
, "NAME"       VARCHAR(60)
, "SHAPE"      ST_GEOMETRY(3857)
, "SHAPEPOINT" ST_POINT(3857)
, "IS_LEAF"    BOOLEAN

, PRIMARY KEY ("ID")
);

CREATE COLUMN TABLE "GEO_DEMO"."NEIGHBORHOODS_2018" 
( "ID"         NVARCHAR(10)         
, "NAME"       VARCHAR(60)
, "SHAPE"      ST_GEOMETRY(3857)
, "SHAPEPOINT" ST_POINT(3857)
, "IS_LEAF"    BOOLEAN

, PRIMARY KEY ("ID")
);

Next, attribute views need to be created for every table. This can be added in the geo_demo package. Keep in mind to grant _SYS_REPO permissions on the schema/objects used above before activating the views, with a statement like:

GRANT SELECT ON SCHEMA "GEO_DEMO" TO "_SYS_REPO" WITH GRANT OPTION;

In every view:

  1. Add the table
  2. Add all columns to the output
  3. Set the ID column key property to true
  4. Remove the ‘Apply Privileges’ in the properties
  5. Save and Activate

The above steps are described, including screenshots, in HANA Live Custom Regions on page 7.

The data loaded in section 1 has to be transformed into the tables created above. The guide has an sample procedure definition which can be used. The reason for this is to calculate the correct ST_Centroid value if the geometry type is ST_MultiPolygon instead of just a ST_Polygon. Below are versions of the procedure, based on the sample code in the guide, to fill the tables of this example:

CREATE PROCEDURE "GEO_DEMO"."PR_GENERATE_TOWNSHIPS"( )
LANGUAGE SQLSCRIPT
AS
BEGIN

	DECLARE CURSOR c1 FOR 
		SELECT    "GM_CODE"                  AS ID
		,         "GM_NAAM"                  AS NAME
		,         "SHAPE".ST_Transform(3857) AS SHAPE 
		FROM      "GEO_DEMO"."gem_2018"
		WHERE     "WATER" = 'NEE'; -- Dataset contains duplicate rows
                                   -- for water and land info

 	DELETE FROM "GEO_DEMO"."TOWNSHIPS_2018";
 			
	OPEN c1;

	FOR curr_row AS c1 DO
		DECLARE maxGeom ST_GEOMETRY;
		DECLARE maxArea DOUBLE := 0;
		DECLARE centroid ST_GEOMETRY;
		DECLARE i INT := 1;
		DECLARE ashape ST_GEOMETRY := curr_row.SHAPE;

		IF :ashape.ST_GeometryType() = 'ST_MultiPolygon' THEN
			FOR i IN 1.. ashape.ST_NumGeometries() DO
				DECLARE geom ST_GEOMETRY := ashape.ST_GeometryN(i);

				IF :geom.ST_Area() > maxArea THEN
					maxArea := geom.ST_Area();
					maxGeom := geom;
				END IF;
			END FOR;

			centroid := maxGeom.ST_Centroid();
		ELSE
			centroid := ashape.ST_Centroid();
		END IF;
		
		IF :ashape.ST_Intersects(:centroid) = 0 THEN
			centroid := ashape.ST_PointOnSurface();
		END IF;

 		INSERT INTO "GEO_DEMO"."TOWNSHIPS_2018"
		(      "ID"
		,	   "NAME"
		,      "SHAPE"
		,      "SHAPEPOINT"
		,      "IS_LEAF" 
		)
		VALUES
		(      curr_row.ID
		,      curr_row.NAME
		,      curr_row.SHAPE
		,      centroid
		,      FALSE -- Set to true on last level (NEIGHBORHOOD)
		);
	END FOR;

	COMMIT;

	CLOSE c1;
END;

CREATE PROCEDURE "GEO_DEMO"."PR_GENERATE_DISTRICTS"( )
LANGUAGE SQLSCRIPT
AS
BEGIN

	DECLARE CURSOR c1 FOR 
		SELECT    "WK_CODE"                  AS ID
		,         "WK_NAAM"                  AS NAME
		,         "SHAPE".ST_Transform(3857) AS SHAPE 
		FROM      "GEO_DEMO"."wijk_2018"
		WHERE     "WATER" = 'NEE'; -- Dataset contains duplicate rows
                                   -- for water and land info
	
 	DELETE FROM "GEO_DEMO"."DISTRICTS_2018";

	OPEN c1;

	FOR curr_row AS c1 DO
		DECLARE maxGeom ST_GEOMETRY;
		DECLARE maxArea DOUBLE := 0;
		DECLARE centroid ST_GEOMETRY;
		DECLARE i INT := 1;
		DECLARE ashape ST_GEOMETRY := curr_row.SHAPE;

		IF :ashape.ST_GeometryType() = 'ST_MultiPolygon' THEN
			FOR i IN 1.. ashape.ST_NumGeometries() DO
				DECLARE geom ST_GEOMETRY := ashape.ST_GeometryN(i);

				IF :geom.ST_Area() > maxArea THEN
					maxArea := geom.ST_Area();
					maxGeom := geom;
				END IF;
			END FOR;

			centroid := maxGeom.ST_Centroid();
		ELSE
			centroid := ashape.ST_Centroid();
		END IF;
		
		IF :ashape.ST_Intersects(:centroid) = 0 THEN
			centroid := ashape.ST_PointOnSurface();
		END IF;

 		INSERT INTO "GEO_DEMO"."DISTRICTS_2018"
		(      "ID"
		,	   "NAME"
		,      "SHAPE"
		,      "SHAPEPOINT"
		,      "IS_LEAF" 
		)
		VALUES
		(      curr_row.ID
		,      curr_row.NAME
		,      curr_row.SHAPE
		,      centroid
		,      FALSE -- Set to true on last level (NEIGHBORHOOD)
		);
	END FOR;

	COMMIT;

	CLOSE c1;
END;

CREATE PROCEDURE "GEO_DEMO"."PR_GENERATE_NEIGHBORHOODS"( )
LANGUAGE SQLSCRIPT
AS
BEGIN

	DECLARE CURSOR c1 FOR 
		SELECT    "BU_CODE"                  AS ID
		,         "BU_NAAM"                  AS NAME
		,         "SHAPE".ST_Transform(3857) AS SHAPE 
		FROM      "GEO_DEMO"."buurt2018"
		WHERE     "WATER" = 'NEE'; -- Dataset contains duplicate rows
                                   -- for water and land info
	
 	DELETE FROM "GEO_DEMO"."NEIGHBORHOODS_2018";

	OPEN c1;

	FOR curr_row AS c1 DO
		DECLARE maxGeom ST_GEOMETRY;
		DECLARE maxArea DOUBLE := 0;
		DECLARE centroid ST_GEOMETRY;
		DECLARE i INT := 1;
		DECLARE ashape ST_GEOMETRY := curr_row.SHAPE;

		IF :ashape.ST_GeometryType() = 'ST_MultiPolygon' THEN
			FOR i IN 1.. ashape.ST_NumGeometries() DO
				DECLARE geom ST_GEOMETRY := ashape.ST_GeometryN(i);

				IF :geom.ST_Area() > maxArea THEN
					maxArea := geom.ST_Area();
					maxGeom := geom;
				END IF;
			END FOR;

			centroid := maxGeom.ST_Centroid();
		ELSE
			centroid := ashape.ST_Centroid();
		END IF;
		
		IF :ashape.ST_Intersects(:centroid) = 0 THEN
			centroid := ashape.ST_PointOnSurface();
		END IF;

 		INSERT INTO "GEO_DEMO"."NEIGHBORHOODS_2018"
		(      "ID"
		,	   "NAME"
		,      "SHAPE"
		,      "SHAPEPOINT"
		,      "IS_LEAF" 
		)
		VALUES
		(      curr_row.ID
		,      curr_row.NAME
		,      curr_row.SHAPE
		,      centroid
		,      TRUE -- Set to true on last level (NEIGHBORHOOD)
		);
	END FOR;

	COMMIT;

	CLOSE c1;
END;

Execute the procedures:

CALL "GEO_DEMO"."PR_GENERATE_TOWNSHIPS"( );
CALL "GEO_DEMO"."PR_GENERATE_DISTRICTS"( );
CALL "GEO_DEMO"."PR_GENERATE_NEIGHBORHOODS"( );

After creating and execution the three procedures verify if the attribute views now contain data. For example by executing the following statement:

SELECT COUNT(*), 'TOWNSHIPS' FROM "_SYS_BIC"."geo_demo/TOWNSHIPS_2018"
UNION ALL
SELECT COUNT(*), 'DISTRICTS' FROM "_SYS_BIC"."geo_demo/DISTRICTS_2018"
UNION ALL
SELECT COUNT(*), 'NEIGHBORHOODS' FROM "_SYS_BIC"."geo_demo/NEIGHBORHOODS_2018"

When the views are ready and populated the next step is to configure the custom hierarchies levels. By default the following content is available:

In this case a mixed hierarchy can be used. It will consist of the standard delivered Country (L1) and Region (L2) followed by the custom Township (L3), District (L4) and Neighborhood (L5). Based on the package and view names above this can be done with the following statement:

--
-- Mind the uniqueness/alignment of HIERARCHYID and ID when adding additional hierarchies.
--
INSERT INTO "SAP_FPA_SPATIAL_CUSTOM_REGIONS"."sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY"
(         "NAME"
,         "HIERARCHYID"
,         "COLUMN"
,         "COLUMNLABEL"
,         "ID"
,         "LEVEL"
,         "LNAME"
,         "LOCATION"
,         "OBJECT"
,         "PACKAGE"
,         "SCHEMA"
)
SELECT    'Neighborhood 2018 Hierarchy' AS "NAME"
,         2                             AS "HIERARCHYID"
,         'AREA_NAME'                   AS "COLUMN"
,         'name'                        AS "COLUMNLABEL"
,         6                             AS "ID"
,         1                             AS "LEVEL"
,         'Country'                     AS "LNAME"
,         'SHAPE'                       AS "LOCATION"
,         'CHOROPLETH'                  AS "OBJECT"
,         'FPA_SPATIAL_DATA.choropleth' AS "PACKAGE"
,         '_SYS_BIC'                    AS "SCHEMA"
FROM      DUMMY
UNION ALL
SELECT    'Neighborhood 2018 Hierarchy' AS "NAME"
,         2                             AS "HIERARCHYID"
,         'AREA_NAME'                   AS "COLUMN"
,         'name'                        AS "COLUMNLABEL"
,         7                             AS "ID"
,         2                             AS "LEVEL"
,         'Region'                      AS "LNAME"
,         'SHAPE'                       AS "LOCATION"
,         'CHOROPLETH'                  AS "OBJECT"
,         'FPA_SPATIAL_DATA.choropleth' AS "PACKAGE"
,         '_SYS_BIC'                    AS "SCHEMA"
FROM      DUMMY
UNION ALL
SELECT    'Neighborhood 2018 Hierarchy' AS "NAME"
,         2                             AS "HIERARCHYID"
,         'NAME'                        AS "COLUMN"
,         'name'                        AS "COLUMNLABEL"
,         8                             AS "ID"
,         3                             AS "LEVEL"
,         'Township'                    AS "LNAME"
,         'SHAPE'                       AS "LOCATION"
,         'TOWNSHIPS_2018'              AS "OBJECT"
,         'geo_demo'                    AS "PACKAGE"
,         '_SYS_BIC'                    AS "SCHEMA"
FROM      DUMMY
UNION ALL
SELECT    'Neighborhood 2018 Hierarchy' AS "NAME"
,         2                             AS "HIERARCHYID"
,         'NAME'                        AS "COLUMN"
,         'name'                        AS "COLUMNLABEL"
,         9                             AS "ID"
,         4                             AS "LEVEL"
,         'District'                    AS "LNAME"
,         'SHAPE'                       AS "LOCATION"
,         'DISTRICTS_2018'              AS "OBJECT"
,         'geo_demo'                    AS "PACKAGE"
,         '_SYS_BIC'                    AS "SCHEMA"
FROM      DUMMY
UNION ALL
SELECT    'Neighborhood 2018 Hierarchy' AS "NAME"
,         2                             AS "HIERARCHYID"
,         'NAME'                        AS "COLUMN"
,         'name'                        AS "COLUMNLABEL"
,         10                            AS "ID"
,         5                             AS "LEVEL"
,         'Neighborhood'                AS "LNAME"
,         'SHAPE'                       AS "LOCATION"
,         'NEIGHBORHOODS_2018'          AS "OBJECT"
,         'geo_demo'                    AS "PACKAGE"
,         '_SYS_BIC'                    AS "SCHEMA"
FROM      DUMMY;

This leads to the following filling in the table:

With this in place it is possible to start the setup in SAC.

 

4. Create a sample data dimension to report on

To show something in SAC, at least, a single calculation view is needed. In this case the view will be names INHABITANTS_PER_NEIGHBORHOOD. In this demo we base this on the same data which is loaded in section 1. The view is showing the number of inhabitants, men and women per neighborhood and has the original BU_CODE as key field.

The view has the privileges removed;

The data is filtered again on WATER = ‘NEE’ identical to filling the hierarchies.

After the view is created a data preview shows the following, part of, data:

 

5. Create SAP_BOC_SPATIAL views

The sample data view in section 4 doesn’t include any spatial dimension which can be used to plot data in SAC. In order to map this special views are needed in in a specific SAP_BOC_SPATIAL package are required. The document Creating Geo model from Live HANA Calculation View attached to SAP Note 2370226 – SAP BusinessObjects Cloud 2016.19 release information contains a description on how to add a this. In this demo the data used to make the location dimension view for the neighborhoods (BU_CODE join) can be reused. Create a view in the SAP_BOC_SPATIAL package, again mind the privileges:

In the column names be aware of two points:

  1. The ID column can not be the same name as the ID column in the data to which this location dimension is joined. As the SAP_BOC_SPATIAL package name has to be used, a name like SAP_BOC_SPATIAL_ID would be relatively safe in future.
  2. The SHAPE or SHAPEPOINT column names are shown value in the hierarchy selection. This will now result in AREA instead.

 

6. Create SAC content

First of all a HANA Live connection is required, in this case to SCP. This can be added through the connections menu option:

Next, create a new model:

  1. Choose: Get data from datasource
  2. Choose: Live data connection
  3. Specify:
    • System Type: SAP HANA
    • Connection: GEOSCPDEMO (connection made above)
    • Data Source: INHABITANTS_PER_NEIGHBORHOOD (view made in section 4)
    • Name: INHABITANTS_PER_NEIGHBORHOOD (can be any name)
  4. Choose menu item: Create a Location Dimension
  5. Specify:
    • Location Identifier: BU_CODE (this column is from the model)
    • View Name: NEIGHBORHOOD_MAPPING (view made in section 5)
    • Identifier for Mapping: SAP_BOC_SPATIAL_ID (mind the name can not match the Location Identifier)
  6. Save

Finally, create a new story:

  1. Choose: Add a Canvas Page
  2. Choose: Geo Map
  3. Choose: Add Layer
  4. Select the model created above; INHABITANTS_PER_NEIGHBORHOOD
  5. Set the layer type to Choropleth
  6. Choose the AREA Location Dimension
  7. Hover over the Location Dimension and choose the custom Neighborhood 2018 Hierarchy
  8. Pick a measure, for example, INHABITANTS
  9. Choose: Navigate up/down the hierarchy
  10. Select: AREA
  11. Pick a hierarchy level, for example District

The steps above are recorded in the YouTube video below:

 

A special thanks to Dirk Buitendijk for editing the contentI hope this blog/tutorial helped you get started. Please leave any comment, questions or remarks below.

 

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Himanshu Nahata

    Hi Dennis,

    Excellent post !! Can we use shapefiles containing ‘multilinestrings’ instead of ‘polygon’? Does the custom choropleth layer supports linestrings?

    Thanks

    Himanshu

    (0) 
  2. Fabio Sist

    Hi Dennis,

    Very useful post! Just one thing for the future readers, could you modify it to set the SRID = 28992? Sometimes it is 28992 and sometimes 29882.

    Thank you

    Fabio

    (0) 

Leave a Reply