- SAP Managed Tags:
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:
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.
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:
If it doesn't exist, it can be created using the following statement:
After the spatial system is created, the ESRI™-shape file can be loaded through the import option in the file menu of HANA Studio.
At this point the custom spatial data is available in HANA.
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:
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:
This custom role can be granted using the following statement:
After this is done, log in with the user GEO_DEMO and verify the default content can be retrieved:
At this point the default content to make custom hierarchies is available 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:
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:
In every view:
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:
Execute the procedures:
After creating and execution the three procedures verify if the attribute views now contain data. For example by executing the following statement:
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:
This leads to the following filling in the table:
With this in place it is possible to start the setup in SAC.
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:
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:
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:
Finally, create a new story:
The steps above are recorded in the YouTube video below:
A special thanks to Dirk Buitendijk for editing the content. I hope this blog/tutorial helped you get started. Please leave any comment, questions or remarks below.
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:
- SAC Tenant
- SCP HANA Database
- Download of map data from CBS, link to external download
- HANA Studio
The following steps were taken:
- Download map data and import into HANA
- Prepare HANA with required delivery units
- Create custom hierarchy table and views in HANA
- Create a sample data dimension to report on
- Create SAP_BOC_SPATIAL views
- 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.
- Choose ESRI Shapefiles.
- Specify the location of the ESRI™-shape files.
- Choose which ESRI™-shape files to load.
- 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:
- Add the table
- Add all columns to the output
- Set the ID column key property to true
- Remove the 'Apply Privileges' in the properties
- 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:
- 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.
- 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:
- Choose: Get data from datasource
- Choose: Live data connection
- 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)
- Choose menu item: Create a Location Dimension
- 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)
- Save
Finally, create a new story:
- Choose: Add a Canvas Page
- Choose: Geo Map
- Choose: Add Layer
- Select the model created above; INHABITANTS_PER_NEIGHBORHOOD
- Set the layer type to Choropleth
- Choose the AREA Location Dimension
- Hover over the Location Dimension and choose the custom Neighborhood 2018 Hierarchy
- Pick a measure, for example, INHABITANTS
- Choose: Navigate up/down the hierarchy
- Select: AREA
- 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 content. I hope this blog/tutorial helped you get started. Please leave any comment, questions or remarks below.
8 Comments
| User | Count |
|---|---|
| 12 | |
| 12 | |
| 11 | |
| 11 | |
| 11 | |
| 9 | |
| 8 | |
| 7 | |
| 7 | |
| 7 |
