It’s an SCN World on the SAP HANA Cloud Platform
- Main Page Tiles
- Tile One
- Tile Two
- Final Tile
- Site Details
- Thank you
- References to Sites used for the Maps
- Uploading a Shapefile to the SAPHCP
- Public SAPID based XS Service
- SAP ID Service
- December 2015 Update
- Recent HCP HANA SPS10 Inspired Update – January 2016
My blog is a follow up to the ideas I covered in my SAP HANA Cloud Portal movie challenge entry. That post was about ideas to use SAPHCP as a platform to share maps and site statistics about SCN.
I have followed up and implemented a demo of the “Where in the world are you?” ideas in that blog using the HCP. I will use my HCP demo site as an entry to the Data Genius challange. The idea being that you, dear reader, will be prepared to share your location on my map and then see other SCN users on the overall map. As part of the overall map I have added two SAP events (2015 Techeds for Las Vegas and Barcelona) and use an HCP XS service to determine which event is closer to the selected SCN member on the map. And finally I have also used the spatial capabilities of the SAPHCP platform to map SDN points totals from 2004 to 2010.
So onto the details of my SAPHCP demo site and how to use it. I will keep the site active until the end of the Data Genius competition which is due to end in December 2015 and hopefully you have registered your location by that time :). *The site will remain active now, see December 2015 update at the end of the blog.
Main Page Tiles
The main page of my site is at the following link
is to submit (or subsequently used to update) your location to the SCN map.
To locate any user then the process relies on a modern browsers geo-location functionality. Therefore to use the service you must be prepared to share your location via any prompts from the browser.
Click on the first tile
When the page loads then you need to agree to share your location. E.g. the Firefox browser’s prompt may look like the following screenshot.
Select the “Share Location” ( agree to share the location on any other modern browser ).
If the following screen remains for more than a few minutes then please check you have the location services active and running. There are a few links in the page for various browsers/platforms location services. If you need to activate the location service then please reload the page.
The above screen should automatically be replaced by the following location screen after a short wait.
Move the marker to a position you would want to register your location on the map. E.g. you may not want the position that is automatically chosen by the browser. The marker can be dragged to any location – I would prefer it still to be in the general area and not another country for example 🙂
Enter twitter id (with the @ e.g. @rjruss). You do not need to enter a twitter id, a name/nickname can be entered but for the twitter feature to work in the overall map then only enter the twitter id and it must start with the @ character.
Select the submit/update your location button.
Confirmation of the update
Once the submit button is entered then a prompt should confirm success
That’s the end of the first phase.
click on the middle/second tile
The following screen should appear
Using the map zoom icons (+/-) move and zoom in on the map to your location (you should see a borrowed SCN icon in your general area) and click on the icon. Following the twitter link in the popup should display the twitter profile page as below.
SAP Event Locations
As part of the map I have added two SAP events, those being the Techeds this year in Las Vegas and Barcelona (I have used the general location of each event from the SAP Teched pages for both events). The following screenshot is the default location for the Barcelona Teched.
When I select Tammy Powlas’s location on my map (I’ll cover Tammy’s involvement in testing my site later but thanks Tammy :)) then there is a call to an XS service to determine which SAP event is closer to Tammy.
The SCN rocket icon as moved to Las Vegas as the closest event and there is a detail popup for the events registered on my site.
If you have an SCN/SAP event that I can add to the map then that would be great, just let me know the location and I will add it to the map.
3d Globe View
A 3d globe view of the world is available if you have a compatible computer and browser that works with WebGL. Select the “Enable/Disable ol3-cesium” button to swap between the 2d and 3d maps. The popups and twitter links do NOT work in the globe view.
The last tile is a link to my SDN points data set in the SAP HCP cloud. This is using the data and spatial capabilities of the SAPHCP. The following screen is the initial 2004 data.
I used my own ESRI Shapefile for the SDN points data from 2004 to 2010 , using data collected via my ABAP code I shared in my blog here. Here is a link to a video about the country that inspired me to get into maps (and thematic maps ) again, I have shared it previously here and yet again here so I must like that video 🙂
Back to the options on the page
The dropdown box can select the year and the “update map” button has to be selected to load the data.
Selecting the “Enable/Disable ol3-cesium” button again will show a 3d globe view. Changing the year and updating the map while it is in 3d mode will NOT work and will most likely need a refresh of the entire page to restore the settings. Only updating the year in 2d mode will work.
When I created the SCN location map I was interested in people checking the service from other countries. As I have been thankful for their help previously I contacted Tammy Powlas and Sergio Ferrari to test out the site. Tammy replied and was able to help out and test. Sergio kindly took time and replied during his holiday and was not able to test at the time of asking. Thank you both once again. Some of the above screenshots were taken after Tammy had tested the site.
Also other SCN members I would like to thank as their information on SCN allowed me to complete my demo site.
First a thank you again to Stoyan Manchev for his blog on XS applications on the HCP. His blog is one I would like to mark higher than 5 stars as it was my reference for all other activities in putting together my demo XS application.
Louenas Hamdi’s blog on build a basic REST service
Trinoy Hazarika’s blog for geo spatial HANA information that made me realise I could achieve what I wanted with the geo-spatial features of HANA and I tried the approach with the HCP.
Kevin Small’s blog for the geojson information
Henrique Pinto’s post about using a GET method to update a HANA database.
Thomas Jung’s blog about various HANA developer tools. I took the information about the UPSERT command. Also thanks to Thomas’s for other general HANA blogs and also I read about preventing potential SQL injection methods by using prepared SQL statements from his comment linked below.
References to Sites used for the Maps
The way I use these tools can be seen in the source code for the web page. So if you are interested follow the url to see how I use these libraries/tools.
Nearest SAP event
I adapted an answer to this stackoverflow SQL question for my HANA XS service to identify which SAP event would be closest to the selected user.
Uploading a Shapefile to the SAPHCP
The eclipse HANA tools allow uploading ESRI Shapefiles to the SAPHCP from a local computer. The following screenshots allowed me to upload my previously created shapefile for the SDN points data from 2004-10. Using Trinoy’s and Kevin’s blogs referenced earlier I was able to turn these into XS geojson services. Combining that with Stoyen’s base blog about allowing HANA views to be setup for use with an XS service. I did find I had to create a pure HANA dimension calculation view as this was only allowed with spatial data types. I do load over 1.6mb of geojson to map each years data, a next step for me is to separate the spatial data from the actual points data as this would improve the load times and creation of the map.
In the next screen, select your system and the next screen would be to import from the current client
The name of the shapefile and table is then displayed and then choose your schema in the next screen.
Screenshot below of the calculation view I use on my site.
Public SAPID based XS Service
As I wanted to allow all SAP HCP trial users to use my service I thought about registering over 2 million accounts in my access model file ;). Then I thought again and read the help and used the following method to allow my services to be used by all end user accounts.
“HCP_GRANT_TO_PUBLIC_ROLE(role_name NVARCHAR(256)) – you use this procedure to grant an activated hdbrole to theHCP_PUBLIC role. HCP_PUBLIC role is granted to all end users by default. For the trial landscape, the end user name is their C, D, P, S, or Iuser.”
In my XS service to update the location table I use the $.session.getUsername() command to get the user id and hash this to mask the actual value. More details of how I do that next.
SAP ID Service
I use the SAP ID default account to identify individual users for the map. As the trial SAP HCP uses this as a default authentication method then it is a good way to identify users. If I only share the link on SCN then there is a very good chance I only get registered SCN users on my map.
Masking the SAP ID user id
As I didn’t want to store your SAP ID user account in my trial account HANA database (have you registered your location yet 🙂 ) , I have used the SHA256 HASH function as a mask. This way if I concat the SAP ID and a known random string I can mask your ID. E.g. for example and not the actual random text I use to hash the user ID.
SELECT HASH_SHA256(to_binary(‘” + SAPIDUserName + “‘), to_binary(‘Some Random Text‘)) HASH FROM DUMMY
This would generate the following value that I would store in my HCP site and this combination means I do not know the user id of any user on my map.
There is a timeout SAML issue for the site and after some time there will be an error due to a timeout of authentication. The issue “Assertion did not contain a valid MessageID” is discussed here http://scn.sap.com/docs/DOC-50418 for SAML based XS services. I did attempt to capture the error (and other potential errors) by following the jquery jqXHR method discussed here. My adapted code looks for any status errors and force reloads the page. This will then re-authenticate and allow the process to complete. You can always reload the page if it appears that nothing is updating after a period of inactivity on my site.
Thanks for reading and registering your location on my map 😉
December 2015 Update
The Data Genius IV has now ended and originally I was going to shutdown this site as stated above, however I do have a general interest in spatial topics and the HCP is spatially enabled. So I have changed my mind and I have decided to keep the site. The data set is of interest to me (and hopefully of interest to you too 🙂 ) and I have tried out a spatial query on the dataset and added further tiles to the site.
Tile: Delete Your Location
As I am keeping the site for longer than originally stated I thought it would be good to offer the option to delete a registered location from the map. There are a number of anonymous entries where people have left the default text when registering on the map. This is one way to use the site anonymously and you can always update your registered location with any text at any time (and remove a twitter id for example) but I wanted a delete option as well. Clicking on the tile on my site will take you to the next screen to use a button to confirm the deletion. The page does try and detect the XS SAML timeout issue as before and will reload the page if there is a timeout of authentication. The deletion process removes the registered location completely from my database table.
Tile: SCN Map Reports
I do have a general interest in spatial topics and I can now try out some spatial queries on the data. The first attempt was to combine the SCN user location with my SDN Points world map to determine the country where the user has registered from. ( I could always look at the map to determine that information 😛 but wanted to achieve it with code.) When an SCN user registers on the SCN World Map (that means you 😉 have registered) I record the latitude and longitude for the location. Using HANA I can query this location against my SDN Points 2004-2010 world shapefile (which was based on the Natural Earth data). The query I chose was based on the ST_COVERS spatial method.
var select_all_sales_orders_query = “select top 1 z.NAME_2 FROM \”p1248461150trial.hihanaxs.neogeoxs::SCNUSER9\” c, \”_SYS_BIC\”.\”p1248461150trial.hihanaxs/SDNWORLD\” z where z.SHAPE.ST_covers( NEW ST_Point( ” + LOC + “) ) = 1 and z.YEAR = 2004 “;
The above is the XS service based query I run to get the country name based on the location details. (the YEAR 2004 is used to limit the query as I do have a shapefile loaded in the HCP with spatial and the SDN points data combined. I am only using the shapefile for the country name information). If you follow the link to the SCN Map Reports page it will load an UI5 based table and will query the XS service for each row based on a technique I thanked Peter Muessig previously for here. And the variable “select_all_sales_orders_query” is a tip of the hat appreciation of Stoyen’s XS blog – as everything is a sales order in my code 🙂 . Thank you Stoyen for sharing that eight easy steps to an XS application blog.
The UI5 table will appear as below showing the registered user and country name based on the HANA spatial query. I will be looking to add more spatial queries after you have registered on my site 🙂 ……I’ll check now to see if you have registered….. 🙂
Recent HCP HANA SPS10 Inspired Update – January 2016
The trial HCP HANA version was recently updated to SPS10
As with every new HANA SPS there are new spatial features and one I wanted to try was ST_AsSVGAggr which can group geometries and I can use it to create a world map. I wanted to use a spatial query to generate a jigsaw view (which is an incomplete view & maybe you can add another country by registering 😉 ) of the SCN World Map. Lets see how many countries will eventually appear on the map. At the moment 13 different countries as shown.
I used the ST_AsSVGAggr by updating/extending my existing sql query to detect a registered user’s country, I can then generate an SVG image of just these countries via XS code.
var scnsql = ‘select ST_AsSVGAggr(z.SHAPE) FROM “p1248461150trial.hihanaxs.neogeoxs::SCNUSER9″ c,”_SYS_BIC”.”p1248461150trial.hihanaxs/SDNWORLD” z where z.SHAPE.ST_covers( NEW ST_Point( c.”lon”, c.”lat”) ) = 1 and z.YEAR = 2004;’;
A static image is below. A dynamic image can be found by accessing the “SCN Map Reports” tile mentioned above.
The code for this particular XS function can be found in this Google Drive Document – It’s An SCN World Update Page – Google Docs
I will use this Google document to capture these types of smaller updates and publish back to SCN as appropriate. Also I will be putting all the code I have used so far, for this SCN World, in that document. This is because there are more functions I want to try with the HCP but at the moment I am restricted in using the trial HCP, as things appear a bit broken in places after the recent update. So I will be keeping a backup of my code in that document soon.