Skip to Content
Technical Articles

Creating an SAC Geo Map from WebIDE (HDI) based Calculation Views

In this blog post I will share how to create an SAP Analytics Cloud (SAC), Geo Map based on Calculation Views created within the WebIDE. These are Calc Views that reside within an HDI Container as Column Views.

Pre-requisites

  1. HANA Cloud / HANA On-Prem with XSA
  2. Live Connection from SAC to HANA
  3. Dataset with Latitude and Longitude
  4. WebIDE Calculation Views

Required Steps

  1. hdinamespace subfolder Setting
  2. Create SAP_BOC_SPATIAL folder
  3. Spatial Reference 3857
  4. Location Data
  5. Create Geo Dimensional Calculation View with ST_POINT
  6. Associate Calc Views in SAP Analytics Cloud Model

For the official documentation please see the SAP Analytics Cloud Help

1. hdinamespace subfolder Setting

For the SAC Location Dimension to be picked up in your model, you need the WebIDE hdinamespace setting as below

Figure%201%3A%20db/src/.hdinamespace

Figure 1: db/src/.hdinamespace

"subfolder": "append"

Warning, if you change an existing project using “ignore” then some re-work will be required.

 

2. Create SAP_BOC_SPATIAL folder

SAP Analytics Cloud looks for location data inside the sub folder SAP_BOC_SPATIAL.
This should be created inside the WebIDE project src folder.

Figure 2: SAP_BOC_SPATIAL folder

 

3. Spatial Reference 3857

For SAC to visualise the location data, HANA must have the Spatial Reference 3857 available.
We can check this with the public synonym ST_SPATIAL_REFERENCE_SYSTEMS.

Figure%203%3A%20ST_SPATIAL_REFERENCE_SYSTEMS%20-%203857

Figure 3: ST_SPATIAL_REFERENCE_SYSTEMS – 3857

 

If you do NOT see the SRS_ID 3857 then you can execute the SQL below to add that

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["cen tral_meridian",0],PARAMETER["scale_factor",1],PARAMETER["false_easting",0],PA RAMETER["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'

 

4. Location Data

The location dataset must not contain duplicate values.  To perform the correct join SAC requires a unique list of locations to ensure referential integrity of your model. This dataset will need latitude, longitude data and a field that joins to other Calc Views.

Here I have the table of UK Local Authorities, LAD2019_WKT, sorted on the field lad19cd, which I can see is unique.

Figure 4: Location Dataset

 

5. Create Geo Dimensional Calculation View with ST_POINT

We can now create a new dimensional Calculation View inside the SAP_BOC_SPATIAL folder. Notice how the Namespace field is populated with the required folder name.

Figure%20x%3A%20New%20Dimensional%20Calc%20View%2C%20note%20the%20namespace

Figure 5: New Dimensional Calc View, notice the Namespace: SAP_BOC_SPATIAL

 

The output column names need to be unique across the two calculation views that you are using with SAC. I therefore appended _geo to the end of the lad19cd and lad19nm.

Figure 6: Location Dimensional Calculation View

 

Create Calculated Column of data type ST_POINT.

Using the fields “long” and “lat” we generate the ST_POINT column that SAP Analytics Cloud requires using the SRS 3857.

ST_Transform(ST_GeomFromText('POINT(' || "long" || ' ' || "lat" || ')', 4326),3857)

Figure 7: Calculated Column, LOCAL_AUTHORITY_GEO ST_POINT

We can Save and Build the Calculation View.

Figure%207%3A%20Build%20Calculation%20View

Figure 8: Build Calculation View

We can find our Calculation View as a Column View. Note how the name is prefixed with SAP_BOC_SPATIAL (the subfolder we created in step 2.)

Figure%20x%3A%20Database%20Explorer%20Column%20View

Figure 9: Database Explorer Column View

We should data preview it to verify it works as expected. Note how the name is again prefixed with SAP_BOC_SPATIAL.

Figure%207%3A%20Data%20Preview%20of%20Local%20Authority%20Calc%20View

Figure 10: Data Preview of Local Authority Calc View

 

6. Associate Calc Views in SAP Analytics Cloud Model

Open an existing SAC Model that will join to the fields exposed in the above model, in my case I can join on either lad19cd_geo or lad19nm_geo. Click the “Location Dimension” button.

Figure%209%3A%20Open%20Existing%20SAC%20HANA%20Model

Figure 11: Open Existing SAC HANA Model

We need to associate our Geo Dimensional Calc View with an existing calculation view.

The Location Identifier comes from an existing calculation view, this is the Attribute Column that joins to the Identifier for Mapping column coming from the step 5 “Geo Dimensional Calculation View”.  In my case Area_code joins to lad19cd_geo. This would be a 1:1 or many:1 with the many coming from the existing calculation view.

Figure%2010%3A%20Create%20Location%20Dimension

Figure 12: Create Location Dimension

The final test is to create a Geo Map using this model to verify both the data and locations are   displayed correctly on the map.

Figure 13: SAP Analytics Cloud Geo Map

Conclusion

Spatial data has become more prevalent within the Enterprise, using SAP Analytics Cloud with HANA is a convenient way to visual this.  Using this blog post you can understand how WebIDE based Calculation Views from HANA or HANA Cloud can be consumed live.

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

    Hi Ian,

    Thank you for posting.

    Let me ask you some questions below.

    1. Is the HDI container on SCP (Cloud Foundry) Environment ?
    2. Can you tell me the relation between “location identifier” and “identifier for mapping”? Are they the columns which have same value?

    We are following your procedure of Geo Map based on Calculation Views created within the WebIDE but it doesn’t work well because of an error message at step number 6.

    The error message is “You have no authorization to the model”.

    I don’t know why it happens because I have created the model by myself and put it in the public folder.

    HDI container we are using is HANA Service available at SCP (Cloud Foundry) Environment.

    Please kindly give us your advice.

    /
    • Thanks Keito,

      Glad it’s useful.

      1. My HDI was in HANA Cloud in SCP, Cloud Foundry. The process would be the same for any HDI container
      2. Yes, in step 6, the “Location Identifier” is the key field from your Geo CV and “Identifier for Mapping” is the corresponding field from your existing SAC Model/Calc View.

      The error suggests that the user making the live connection to HANA does not have the rights on one of the calc views. How are you connecting to HANA? I am using the HAA with a predefined user/HDI container.

      If your calc view linked to “test_kk1” is from a different HDI container you would need to give the access role or similar role to the user.

      You can check the Chrome -> View -> Developer Tools -> Console for more information

    • Check which user is SAC using to connect to HANA, then log with that user in DB Explorer and try to select from the same calc view. Chances are you’ll get the same error.

      Like Ian said, you need to authorize that user to be able to select from the HDI Container objects.
      The easiest way is to create a design time role in your HDI Container project with the SELECT object privilege, then to grant this role to the DB User SAC uses to connect to HANA. See the second option mentioned on this blog for more details: https://blogs.sap.com/2018/01/24/the-easy-way-to-make-your-hdi-container-accessible-to-a-classic-database-user/

  • Hello together,

    In an existing project on XSA we have add a calculation view as described in this blog.

    After building and deploying the changes we can see the new calculation view in the column views.

     

    But unfortunately on SAP Analytics Cloud we are not able to see the view as described in “Figure 12: Create Location Dimension”. The dropdown is empty and I don’t get any error.

    Do you have any idea what wen’t wrong?

     

    Best regards,

    Michel

    /
  • Hello Ian Henry and Henrique Pinto,

    thank you for the detailed explanation. We have yet completed all the steps you showed above. We are currently stuck in SAC, Create Location Dimension pop up  window. No view suggestion appears in the Location Data View name dropdown list. We are assuming, that we have a similar problem as Michel.

    SAC Create Location Dimension Popup

    Ian Henry   Is this something that you have seen before, could you help us out with this issue?

     

    Dimension%20View%20created

    Dimension View created

    Best regards,

     

    Chia-Yu