Some Lessons from trying out SAP HANA and Esri together part1
We acquired Esri Platform for all our geospatial analysis and because we have an instance of SAP HANA being used by the business for analytics I decided to have a go with Esri ArcMap and ArcCatalog to consume spatial data from SAP HANA and here, in this first part, I want to share with you some lessons from this exercise.
SAP HANA SP10 Rev102.
Some were pretty straight forward from the Esri website while for others I had to do some reading in the HANA guides.
- Connection from Esri to SAP HANA: This is pretty straight forward. You install the HANA client driver that matches the version of Esri client applications. In my case ArcMap is a 32-bit version application so I installed HANA Client 32 bits. ForArcGIS server you need 64-bit version. Further steps to configure the ODBC connection can be found here: http://desktop.arcgis.com/en/arcmap/10.3/manage-data/databases/connect-saphana.htm
- Privileges on SAP HANA: The user that will consume the data (viewer) needs to have the following privileges. Taken from arcgis website. Privileges for using ArcGIS with SAP HANA&mdash;Help | ArcGIS for Desktop
- SELECT on sys.st_geometry_columns and sys.st_spatial_reference_systems.
- SELECT on tables that contain other type of data needed for analysis in Esri.
- There are other privileges suggested by Esri which are more about creating tables and data into HANA through Esri client apps so I didn’t used those.
- Table definition on HANA: In our case we’ve been using the HDBTABLE framework to build all our tables in HANA and here is the first challenge. HDBTABLE doesn’t support spatial data types. To support this you have to use CDS Framework on all the tables that have spatial data types. CDS is pretty much the framework going forward as HDBTABLE won’t receive further development or new features, at least that’s what I got informed from SAP. The syntax is something like this:
ID : String(10);
SHAPE : hana.ST_GEOMETRY(4326);
- One point to note here is the definition of the SRID (Spatial Reference Identifier) value for the spatial column. By default, when not specified, HANA assumes SRID = 0 which is fine when working in a 2D space, however, earth is not flat so running functions with SRID 0 gives wrong results. Therefore, SRID = 4326 needs to be set up for the column when spatial functions are run in an earth coordinates scenario. You can verify this by using one of the spatial functions ST_SRID() on the spatial column. Something like this:
SELECT column_name.ST_SRID() FROM Table_Spatial;
- One last challenge is that when creating tables with the CDS framework the run-time objects generated by the activation process contain certain characters that are not supported by ArcMap or ArcCatalog, like double punctuation mark. So, one example of a table name after activation would be like this: Package.Data::CUSTOMER_DIM which contains double punctuation marks between the namespace and the table name. This causes that ArcMap or ArcCatalog can’t read the table and therefore the table is not visible at all. Here, our workaround is to put the table in a view created by SQL script which name is supported by ArcGIS. This bring an extra step to be done when developing tables with spatial data. So far, it’s working for us, however, if any of you think of an alternative or better way please let me know. 🙂
For further references, check this technical article from ArcGIS
In this short article I tried to highlight the challenges from HANA side and in part 2 I will continue to develop this further including from ArcGIS point of view as well.
So, these have been some of the lessons learned from going through the integration challenges between SAP HANA and Esri whichI think it’s normal at this stage although I would’ve thought that the integration was a bit more developed. I’m looking forward to see how SAP and Esri take this integration further to a point where SAP HANA can become a truly geospatial database.
Please feel free to leave comments around your experience with this integration as the more we share more knowledge we generate.
First, I'm also creating my tables using CDS (with spatial data) but I don't use SQL script view to see it in arcmap.
I'm adding a query layer and I write my select query manually. I can't see the tables in the wizard but you can just write the query manually. Just don't forget to add " to the table name (e.g SELECT * FROM "Package.Data::CUSTOMER_DIM")
Another issue, ESRI can't recognize spatial data with srid 4326 but it only accept data with srid 100004326 (wgs84 planar) - watch out if you are doing calculation in HANA and want to show the data with a query layer in ESRI.
If you don't have this problem I would like to hear what you did.. Maybe I missed something.
And last thing you might help me with, I'm facing an issue I didn't solve yet.
I'm trying to publish to arcgis server 2 services. Each has a query layer that it's data source is to a different HANA system (lets say I have my dev map services and qa services on the same ESRI server - but for each I have a different HANA server)
After publishing - It acts like it knows only one of the hana servers (arcmap detects 2 connection strings but the server doesn't only one of them - the first one who was registered)
Can you see if it happens in your system also?
Thanks for your message and sorry for my late reply.
I'll check your question against my system and will get back to you shortly with some feedback.