It’s great to see increasing adoption of SAP Analytics Cloud (formerly known as SAP BusinessObjects Cloud) and SAP Digital Boardroom in hybrid deployment scenarios. Customers want to keep the data in their HANA server, be it on SAP HANA Cloud Platform (HCP) or on-premise server, but access and analyze their data from anywhere. With this trend, one question I receive most frequently nowadays is how to model their location data in HANA and visualize it on the map.
The support for visualizing location data from HANA calculation view has been available since 2016 with a few incremental updates released since. What is important to understand in this scenario is that, unlike pure Cloud environment where your data is in the same HANA platform as the SAP Analytics Cloud/SAP Digital Boardroom, the data is in your server. We have no rights to change or write to your server. We can only query the data. So, enabling geospatial analysis from your HANA server requires you to model the data in a particular way and have the query engine support we need (hence the minimum supported HANA version). We also expect to use the HANA spatial processing engine in your server where it makes sense to delegate the processing to the server for performance benefits (hence the need for SAP HANA Spatial license).
I am putting this blog together to consolidate the information that you need to know and provide future updates.
- Pre-requisites
- Step-by-step instructions for Geo-enabling your data
- Downloading Spatial Data Delivery Unit for Choropleth visualization
- How to update location data using HANA trigger
- Release History
- Feature Limitations
Pre-requisites
While pre-requisites are included in the Creating Geo model from Live Hana document, it is worth highlighting this again with latest updates.
- The live HANA system must be at least on SPS11 on 112.07 or higher or SPS12 on 122.03 or higher. If you have an opportunity, I highly recommend you move to 122.06 or higher as we will be soon releasing the support of HANA Spatial based clustering for live HANA data.
- Your HANA system must be licensed for SAP HANA Spatial.
- To use Choropleth Layer, you must download Spatial Data Delivery Unit, GEO CONTENT BOBJ CLOUD from SAP Service Marketplace (SMP). See below for the instructions. Refer to Release History for the latest version.
- The HANA system must be configured with a valid Spatial Reference Identifier (SRID) used by SAP Analytics Cloud. See the document link mentioned in the next section for the instruction.
Step-by-step instructions for Geo-enabling your data
- At the bottom of SAP Note #2387484, which is the release note for SAP Analytics Cloud 2016.22, there is an attachment containing instruction for preparing the data for live HANA calculation view: Creating Geo model from Live Hana
- The Location Data View you create needs to exist under Content > SAP_BOC_SPATIAL package as instructed. If this is your first time, you must create the package first.
Downloading Spatial Data Delivery Unit for Choropleth visualization
If you want to use choropleth layer to visualize regional shapes with filter and drill capability, a Spatial Data Delivery Unit (DU) needs to be downloaded from SAP Service Market Place (SMP) and deployed to your HANA server. To access this DU, you must have SAP HANA Spatial license associated with the S-User ID used to access the Suppor Portal/SMP.
- Use the Support Portal S-user ID associated with your HANA Spatial software to access it
- Search GEO CONTENT BOBJ Cloud 1.0 from SAP Service Marketplace/Software Center
- Click on the Maintenance Software Component link
- Select and download the DU. Note that the DU gets renamed by SMP so it looks something like BOBJ_CLOUD_0-8xxxxxxx.TGZ
- See Release history section for the latest version of the DU and associated SAP Note
- Deploy the DU to your HANA server
How to update location data using HANA trigger
For HANA server SPS11 and SPS12, you have a separate table and view that contains Location Data represented in spatial data. As your data gets updated with newer records, the Location Data does not get automatically updated with new locations. Following template provides a way for you to implement a HANA trigger to keep your data up to date. There are separate instructions for keeping added (INSERT), deleted (DELETE), and updated (UPSERT) data up to date. (Many thanks to JT in my team for writing this out!)
-- ************************************************
-- create a trigger for inserts
-- ************************************************
CREATE TRIGGER <INSERT TRIGGER NAME>
AFTER INSERT ON <SCHEMA>.<SOURCE TABLE>
REFERENCING NEW ROW newrow
FOR EACH ROW
BEGIN
INSERT INTO <SCHEMA>.<LOCATION TABLE> VALUES(:newrow.<ID COLUMN NAME>, ST_GeomFromText('POINT(' || :newrow.<LONGITUDE COLUMN NAME> || ' ' || :newrow.<LATITUDE COLUMN NAME> || ')',4326).ST_Transform(3857));
END;
-- ************************************************
-- create a trigger for deletes
-- ************************************************
CREATE TRIGGER <DELETE TRIGGER NAME>
AFTER DELETE ON <SCHEMA>.<SOURCE TABLE>
REFERENCING OLD ROW deletedrow
FOR EACH ROW
BEGIN
DELETE FROM <SCHEMA>.<LOCATION TABLE> WHERE <LOCATION ID COLUMN NAME>= :deletedrow.<ID COLUMN NAME>;
END;
-- ************************************************
-- create a trigger for updates
-- ************************************************
CREATE TRIGGER <UPDATE TRIGGER NAME>
AFTER UPDATE ON <SCHEMA>.<SOURCE TABLE>
REFERENCING OLD ROW currentrow
FOR EACH ROW
BEGIN
UPDATE <SCHEMA>.<LOCATION TABLE>
SET <LOCATION TABLE LOCATION COLUMN NAME> = ST_GeomFromText('POINT(' || :currentrow.<LONGITUDE COLUMN NAME> || ' ' || :currentrow.<LATITUDE COLUMN NAME> || ')',4326).ST_Transform(3857)
WHERE <LOCATION TABLE ID COLUMN NAME> = :currentrow.<SOURCE TABLE ID COLUMN NAME>;
END;
Release History
I strongly recommend that you read the SAP release notes for each release of SAP Analytics Cloud. If there is a new DU released, please update your DU on your HANA system to keep it in synch.
Release Version (SAP Analytics Cloud version) | Features | Spatial DU Download version (SMP) | Spatial DU Data version (post deployment)* |
2016.19 SAP Note 2370226 | Initial support for HANA calculation view | N/A | N/A |
2016.22 SAP Note 2387484 | Added support for choropleth layer and geo hierarchy | Version obsolete Use the latest DU available | Version obsolete 1.1.2 |
2016.25 SAP Note 2404175 | New DU released to match the Cloud version. | Version obsolete Use the latest DU available | 1.1.6 |
2017.09 and later What's New | New DU will be released to match the Cloud version Hierarchy filter and drill performance improved Bubble Layer clustering uses SAP HANA Spatial clustering instead of client side clustering for faster performance (requires HANA Server 122.06 or higher. For HANA systems lower than 122.06 will use Client side clustering) | BOBJ_CLOUD01_0-80002222.TGZ SP02 SAP Note 2395407 | 1.1.13 |
* Spatial DU data version looks different from the SMP packaging version. It can be checked by running the following query
select * from "_SYS_BIC"."FPA_SPATIAL_DATA.choropleth/VERSION"VERSION_MAJOR column includes the version number represented by x of 1.1.x. For example, DU Version 1.1.6 will have the VERSION_MAJOR value of 6.
Feature Limitations
- You cannot use Distance Filter, Distance Slider, and Intersection Filter in SAP Analytics Cloud when working with models based on live HANA calculation views
- For Cloud models, new support to geo-enrich your region data was made available in 2016.25. This allows creating location dimensions based on names a region. This is not yet enabled for live HANA connectivity
I have had a number of people informing me that they have successfully created the model from their live HANA data. Looking forward to hearing more success stories.
Good luck!
- SAP Managed Tags:
3 Comments
