Build geospatial data type in Data Warehouse Cloud for a geo map in SAP Analytics Cloud

[boman_hwang]

A big thank you to my colleague sathyathayan.rajendram for his technical wizardry on building this functionality and unlocking some hidden power within Data Warehouse Cloud!

Introduction

SAP Data Warehouse Cloud (DWC) currently includes an embedded SAP Analytics Cloud (SAC) instance and in general allows you to leverage the capabilities of SAP Analytics Cloud whether it is while using the embedded SAC version or by using the live connection capability to an external SAC instance.

There are a couple of features however that DWC does not yet support but SAC is already capable of, for example planning functionality or support for geospatial visualization using GeoMaps. SAP hast collected currently unavailable functionalities in Note 2832606 

For GeoMaps, it is by currently not possible by default to create geospatial data in DWC that can be consumed by SAC to build a Geomap. The DWC Roadmap is also currently unclear on when support is coming. But even though there is no official support from SAP for geospatial visualization in DWC yet, in this article we want to share a possible workaround on how to do it anyway and create geospatial data in DWC and use it in SAC.

Step 1:

Create a relational database containing the latitude (important: Data Type=DecimalFloat) and longitude (Data Type=DecimalFloat) of the location and a unique ID. In the example, only one key figure EBIT) is added, but of course all required dimensions and key figures can be added here.   

Latitude Longitude database (Source: Own screenshot)

Step 2:

Define a dimension using a table function. The dimension must contain the unique identifier and the conversion of the latitude and longitude  into the Mercator projection (3857) which is suitable with SAC. The latitude and longitude cannot be converted directly into the Mercator Projection. To be able to create the Mercator projection, the latitude and longitude must first be converted to the World Geodetic System 1984 (4326). This is all done in the following SQL script.

return SELECT "ADDRESSID", NEW ST_POINT( "LONGITUDE", "LATITUDE").ST_SRID(4326).ST_TRANSFORM(3857) LOC  FROM "CT_GEO_TEST_V002";

Please make sure that the two columns outputs in the table function are defined as shown in the following screenshot.

Table function column output (Source: Own screenshot)

Step 3:

Create a graphical or SQL view from the table function and save it as a dimension. 

Table function graphical SQL view (Source: Own screenshot)

Step 4:

In the last step, the relational data created in step 1 must be connected to the dimension view created in step 3. To do this, a new view must be created with the relational data and then the dimension view defined in step 3 must be added as an association.

Connect relational data to dimension view (Source: Own screenshot)

Now the view can be used in SAC and Geomap can be created, for example looking like this:

 

SAC GeoMap using DWC data (Source: Own screenshot)

 

Using GeoMaps in SAC with a DWC data source might not be officially supported yet, but in this article we show you how to still make geospatial data in DWC work in SAC GeoMaps. Official support will surely come later from SAP but our article can give you a way to start building something right now without needing to wait for the functionality to officially arrive. Have fun trying it out and let us know in the comments how it worked for your of if you have any questions!