It’s great to see increasing adoption of 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 BusinessObjects 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

While pre-requisites are included in the Creating Geo model from Live Hana document, it is worth highlighting this again with latest updates.

  1. 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.
  1. Your HANA system must be licensed for SAP HANA Spatial.
  1. 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.
  1. The HANA system must be configured with a valid Spatial Reference Identifier (SRID) used by SAP BusinessObjects 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 BusinessObjects 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> = :newrow.<SOURCE TABLE ID COLUMN NAME>;

END;

 

Release History

I strongly recommend that you read the SAP release notes for each release of SAP BusinessObjects Cloud. If there is a new DU released, please update your DU on your HANA system to keep it in synch.

 

Release Version

(SAP BusinessObjects 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.

 

BOBJ_CLOUD01_0-80002222.TGZ

SAP Note 2395407

1.1.6
2017.09 (Planned)

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)

Upcoming 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

  1. You cannot use Distance Filter, Distance Slider, and Intersection Filter in SAP BusinessObjects Cloud when working with models based on live HANA calculation views
  2. 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!

To report this post you need to login first.

1 Comment

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

Leave a Reply