Skip to Content
Technical Articles
Author's profile photo Ian Henry

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. HDI Calculation Views (created via Business Application Studio or WebIDE)

Required Steps

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

For the official documentation please see the SAP Analytics Cloud Help

1. Create SAP_BOC_SPATIAL namespace

For the SAC Location Dimension to be identified, your calculation view needs the correct namespace. The namespace needs to be SAP_BOC_SPATIAL

There are two ways to different ways to achieve this.

Choose either the subfolder setting or the global setting.

1a. Specify the namespace at a specific sub-folder level

This can be done by creating a .hdinamespace file in a subfolder and that sub-folder will use that setting.

 

Figure 1a: Subfolder Project Setting db/src/calc_views/Spatial

{
    "name": "SAP_BOC_SPATIAL",
    "subfolder": "ignore"
}

 

1b. Use the global project setting to append the hdinamespace setting as below

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

Figure 1b: Global Project Setting db/src/.hdinamespace

"subfolder": "append"

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

With the Global namespace setting you the require a SAP_BOC_SPATIAL folder

SAP Analytics Cloud looks for location data inside the namspsace 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

By specifying the “Key” column, SAC can identify which is the description field as it won’t be the key.

Figure%207.1%3A%20Specify%20the%20Key%20Field

Figure 7.1: Specify the Key Field

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.  From this blog post you can understand how SAP Business Application Studio or WebIDE based Calculation Views from HANA or HANA Cloud can be consumed live.

Assigned Tags

      33 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Keito Tajima
      Keito Tajima

       

      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.

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      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

      Author's profile photo Henrique Pinto
      Henrique Pinto

      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/

      Author's profile photo Michel Magne
      Michel Magne

      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

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Michel,

      I would double check that your dataypes are consistent, you need a common dimension datatype to join with from your 2 views.  Confimrm ST_POINT Calc View object is using the 3857 SRS_ID. This can be done with some syntax similar to this.

      SELECT LOCAL_AUTHORITY_GEO.ST_SRID() FROM "SAP_BOC_SPATIAL::LOCAL_AUTHORITY_LD";
      Author's profile photo Michel Magne
      Michel Magne

      Dear Ian

      Thank you very much for your answer, I checked in the system and can confirm that every single entry has 3857 SRS_ID:

       

      Do you have any other idea?

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      I guess you are joining on STANDORTID_LD? Can you double check the datatype for that that and whichever column you are trying to join with?

      Can you also check for duplicates?

      SELECT count("LAD19CD"), count(distinct "LAD19CD") from "SAP_BOC_SPATIAL::LOCAL_AUTHORITY_LD";

      If that's not given any clues, check SAC, Chrome Developer Tools. Clear the console before you press the location dimension and then check what you see there.

      Author's profile photo Michel Magne
      Michel Magne

      Hi Ian,

      thank you for your input.

      As described by your blog I did already the necessary work to remove the duplicates:

      Duplicate%20Check

      Duplicate check

       

      On SAC I don't see any error. Even all networking requests are sucessful.

      Chrone%20Console-Tab

      Chrome Console-Tab

      Chrome%20Networktab

      Chrome Networktab

       

      Do you have any other idea?

       

      Thanks in advanced.

      Best regards

      Michel

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Can you double check data types from both views?

      Author's profile photo Maxime Simon
      Maxime Simon

      Hello,

      I just had the same issue.
      - Location dimension calculation view was created and activated correctly.
      - No duplicates in the location dimension calculation view.
      - Correct SRID set up.

      However, the view was not visible from SAC. The solution is to build the whole project from business application studio.
      You MUST activate the whole project containing the location dimension calculation view and the .hdinamespace file so that it becomes visible from SAC.

      Author's profile photo Chia-Yu Wu
      Chia-Yu Wu

      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

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Chia-Yu,

      As mentioned above please check your spatial reference ID is 3857 and that you have no duplicate records on the column you are joining with from the Location Dimension.

      Author's profile photo Maxime Simon
      Maxime Simon

      Hello,

      I just had the same issue.
      - Location dimension calculation view was created and activated correctly.
      - No duplicates in the location dimension calculation view.
      - Correct SRID set up.

      However, the view was not visible from SAC. The solution is to build the whole project from business application studio.
      You MUST activate the whole project containing the location dimension calculation view and the .hdinamespace file so that it becomes visible from SAC.

      Author's profile photo Elson Neves Simoes
      Elson Neves Simoes

      Hello  Ian Henry

      When creating an .hdinamespace in an existent project we need to prefix all the artifacts already created? or there is any other solution.

       

      Thank you very much for sharing your knowledge with the community.

      Best Regards,

      Elson

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Elson,

      Yes, there is another solution 🙂

      You can create a .hdinamespace file in the SAP_BOC_SPATIAL folder like below.
      You will then need to rename/refactor your calc view so that the namespace is included in the name.
      It then should appear with the SAP_BOC_SPATIAL when you view the column view.

      {
          "name":    "SAP_BOC_SPATIAL",
          "subfolder": "append"
      }
      
      Author's profile photo Elson Neves Simoes
      Elson Neves Simoes

      Dear Ian,

       

      Thank you very much!! It worked as supposed 🙂

       

      Have a nice end of week/weekend!

       

       

      Author's profile photo Liliana Cantero
      Liliana Cantero

      Hi Ian,

      Thank you for posting! I have completed all the steps you showed above and I created a Location Dimension in SAC correctly but when I use it in a Geo map this error appears:

      Caught exception : exception 1600301: Expected a coordinate, but found ',' instead at position 9 of WKT POINT(-58,557012 -34,619977 )
      ID de correlación: 17119703-4624-4172-9057-089589752342

       

      error

      error

      I already checked and I have no duplicated records. And I also checked if there is a comma in the lat and long values ​​but there isn't.

       

      data%20geo_dimension

      data geo_dimension

      Do you know why this error ocurrs?

      Thanks!

      Liliana Cantero

       

      Author's profile photo Neil Uebelein
      Neil Uebelein

      Ian:

      This was an incredibly helpful post as we did considerable searching and only saw guidance on how to accomplish in the SAP HANA Studio and not SAP Cloud Platform.  We used your approach with SAP Business Application Studio.

      Thank you for taking time to share. Like others, we were not getting the Location View to show in SAP Analytics Cloud in our initial attempt, but rebuilt a new project with the namespace added before other artifacts and it worked.

      I should have read the comments more closely as I believe your Dec 3rd solution would have solved our original issue without the need to rename existing artifacts.

      Thanks,

      Neil Uebelein

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Thanks for the feedback Neil,

      Glad you got it working.
      If you have some Choropleth layers I have a blog on that coming soon.

      I will update the body of the blog to include those details about the namespace. Hopefully should save others some time too.

      Author's profile photo Niklas Kemper
      Niklas Kemper

      Hi Ian,

      thank you for your tutorial. It was very helpful, but I have done all steps in your tutorial and then I get this error. After adding the Location Dimension to the Layer the map moves to the right position but no points are shown. Also every time I move the map, the error message appears again. I already checked the browser console, but there is no error or additional information.

      Could you help me? Or do you have some tips?

      Thanks,

      Niklas

       

       

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Can you click, expand and share the error details?

      Chrome Developer Tools (F12), console tab can sometimes provide additional details.

      Author's profile photo Niklas Kemper
      Niklas Kemper

      When I click expand I only get a Correlation ID. For example:

      "Correlation ID: 15438218-2720-4933-9171-819573543933"

      There are no additional details in the developer tools either.

      Author's profile photo Davide Fregonese
      Davide Fregonese

      Hi Lan,

      i tried every way: 1b return error while building.

       

      1a not, but my SAC Model doesn't see my location View.

      I tried also this way but my issue still the same:

       

      Checking Column View on my space i see my View:

       

      with correct data:

       

      what am I doing wrong?

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi David,

      Something to check.

      1. Check the identifier, COD_CUSTOMER_LD is unique, compare count and count distinct.
      2. Check the datatypes are consistent between the HANA Calc View attributes that you are joining.
      3. Confirm that CUSTOMER_GEO, st_point is using spatial reference 3857. Using SQL similar to below.
        SELECT CUSTOMER_GEO.ST_SRID() FROM "SAP_BOC_SPATIAL::SAP_BOC_SPATIAL_CUSTOMER_LOC";

        Cheers, Ian.

      Author's profile photo Franco Mangalaviti
      Franco Mangalaviti

      Author's profile photo Davide Fregonese
      Davide Fregonese

      I tried, but it seems correct, maybe Hana version? or because i'm using Business Application?

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Davide,

      Business Application Studio is fine. I have also developed Calculation Views this way.

      Are both your Calc Views in the same HDI container?

      Author's profile photo Davide Fregonese
      Davide Fregonese

      Hi Ian, I solved issue. The problem was pending activation on package SAP_BOC_SPATIAL (not calculation view).

      Author's profile photo Benedikt Lueth
      Benedikt Lueth

      Hi Ian,

      First of all, a very big thank you to you for providing this information so well presented here. Without you we would be hopelessly lost!

      We have followed your steps meticulously, but get the following error message when previewing the data in Business Application Studio:

      Could not open 'CD06848EF535443FBE7506500D40C263'.'SAP_BOC_SPATIAL::LOCATION_DIMENSION'.
      Error: (dberror) [669]: spatial error: exception 1600310: '54.501613424.3782003' is not a valid coordinate at position 6 of WKT POINT(54.501613424.3782003)
       at "st_geomfromtext" function (at pos 0) 
      
      The data preview uses the following statement to fetch data:
      SELECT TOP 1000
      	"ID",
      	"lat",
      	"long",
      	"LOCATION_GEO"
      FROM "CD06848EF535443FBE7506500D40C263"."SAP_BOC_SPATIAL::LOCATION_DIMENSION"

      Obviously the calculation of the coordinate is wrong. But we can't find any error here:

      Just so you see that "lat" and "long" are actually coordinates, here is the data preview of the data source:

      Can you please help us out here?

      Thank you very much and best regards,

      Benedikt

      Author's profile photo Mathias KEMETER
      Mathias KEMETER

      Seems you're missing a space between lon and lat when assembling the WKT. Just add the space in your expression and you should be good.

      Author's profile photo Benedikt Lueth
      Benedikt Lueth

      Thanks Mathias, that solved it!

      Author's profile photo Jin Wei Min
      Jin Wei Min

      Hi Ian,

      Thanks for sharing such detailed steps, easy to follow.

      I have followed your tutorial step by step till the end, however, I encounter issue on last step. The GeoMap simply did not show the location. To simplified my learning, I use the same geoloc data as yours, but I only copy the first 3 lines:

      objectid lad19cd lad19nm long lat
      1 E06000001  Hartlepool -1.27018 54.67614
      2 E06000002 Middlesbrough -1.21099 54.54467
      3 E06000003 Redcar and Cleveland -1.00608 54.56752

      I have checked every single step, no issue. Need some guide, thank you.

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Jin Wei Min,

      Happy to share and glad it's useful for you.

      In terms of troubleshooting there's a few places to check.

      1. Web Browser -> Developer Tools. In Chrome you can check the Network tab and the the look at the GetResponse call "Response" tab, one of these can show more details as to the issue. To reduce the volume of calls, it's best to only open this directly before you encounter the error.
      2. HANA Database diagnostic files from Database Explorer, look at the end of the indexserver or indexserver alert.
      3. MDS trace, but this one is much more verbose. I can share details if needed, but hopefully either of the other suggestions can give you an error to work with.

      Let me know what you find.

      Thanks, Ian.