- SAP Managed Tags:
More and more of our customers that run SAP ERP and ArcGIS Enterprise are knocking down silos between GIS and transactional data by referencing their transactional data from ArcGIS Enterprise. This is made possible by putting an ArcGIS geodatabase on HANA or HANA Cloud and using HANA's Smart Data Access (SDA) to query the transactional data on the fly.
Another key component that makes this possible is a sync framework which captures the transactional asset ID and saves it as an attribute in the corresponding ArcGIS feature class. For example, the SAP Object ID for a power pole is stored in the corresponding feature in the power pole feature class (a "foreign key relationship"). The same process plays out on the SAP side where the geometry ID of a feature in an ArcGIS feature class is stored as an attribute of the asset in SAP ERP. SAP's Geographical Enablement Framework (GEF) relies on the sync framework to make sure that assets in SAP have corresponding geometries in ArcGIS Enterprise and vice-versa. The GEF enabled Plant Maintenance module enables Plant Maintenance workers to do their work on a map. RE-FX and Project Management can be enabled with GEF as an engineered service. Remember that GEF is for the users of these ERP modules, but what about everyone else in the enterprise?
Our customers who have installed GEF can take advantage of this foreign key relationship to light up the entire enterprise with transactional data using ArcGIS Enterprise very quickly. This is exactly what Provincie Noord Brabant (PNB) has done.
PNB had 3 different manual ETL processes in their previous architecture. Data replication requires ETL - creation, maintenance and approval. ETL increases footprint and introduces the chances for errors. Users have to wait for ETL to pass change control approvals. Because PNB has GEF and S/4HANA, it was straightforward to conduct a POC to show the advantages of eliminating those 3 ETL processes. Remember that HANA and HANA Cloud are designed to help you avoid replicating data. The impact?
Here is a high level architecture of what PNB did:
One of the many advantages of S/4HANA is the availability of ABAP CDS views. These provide context rich information without having to know about the underlying schema and presents the data with easy to understand column names. Accessing the ABAP CDS views using SDA is done at the ABAP level so S/4HANA security is respected. It's the supported method for accessing S/4HANA data. In fact, S/4HANA uses CDS views to reference the data within the system itself.
I defined a SQL view in HANA using this SQL statement which queries an ABAP CDS view that contains notification information. Remember that the CDS view is registered as a virtual table in the ArcGIS HANA tenant but actually resides in an S/4HANA system.
The query worked in HANA Studio, but when using a view in ArcGIS Pro, this occurred:
It's important to use the right datatype to cast an ST_GEOMETRY value into. The CDS view we created happened to use VARCHAR( 1024) for the EWKT representation. The danger is the EWKT length can explode especially for a long linestring or a polygon with many vertices. The better choice is the CLOB datatype. Fortunately, in this situation, that wasn't the issue. So what was it?
It's a combination of the HANA optimizer shuffling around parts of queries and what ArcGIS Pro adds to a query layer (or the query against the feature class's underlying HANA table...)
ArcGIS adds a filter against the view-port by default. That means only the data that is within that rectangle (view-port) will be retrieved. So, instead of executing the original query, ArcGIS executes
This should prevent HANA from trying to perform the conversion of a blank or null EWKT representation to an ST_GEOMETRY value... however, at least in this case, the HANA optimizer steps in to optimize performance and rewrites the query to something like this:
As a result, the geometry construction is moved to the WHERE-clause before filtering out empty strings. When attempting to plot the notification geometries on the web map in ArcGIS Pro, it fails as shown above. Instead, the answer is using a CASE statement in the target list like this:
With this approach, the optimizer can only move the whole CASE construct to the WHERE-clause and will never try to construct a geometry from an empty EWKT.
With that, here is the web map showing notification geometries retrieved from S/4HANA Plant Maintenance on the fly:
This illustrates how straightforward it is to expose transactional data from SAP ERP (in this case S/4HANA using ABAP CDS view) on an ArcGIS web map or in any ArcGIS information product. Your enterprise can use this capability by putting the geodatabase on HANA or HANA Cloud and get the advantages that PNB realized: the elimination of error prone and manual business processes resulting in insight reaching the stakeholders 40% faster. Lighting up the entire enterprise with transactional data is a real competitive advantage!
Another key component that makes this possible is a sync framework which captures the transactional asset ID and saves it as an attribute in the corresponding ArcGIS feature class. For example, the SAP Object ID for a power pole is stored in the corresponding feature in the power pole feature class (a "foreign key relationship"). The same process plays out on the SAP side where the geometry ID of a feature in an ArcGIS feature class is stored as an attribute of the asset in SAP ERP. SAP's Geographical Enablement Framework (GEF) relies on the sync framework to make sure that assets in SAP have corresponding geometries in ArcGIS Enterprise and vice-versa. The GEF enabled Plant Maintenance module enables Plant Maintenance workers to do their work on a map. RE-FX and Project Management can be enabled with GEF as an engineered service. Remember that GEF is for the users of these ERP modules, but what about everyone else in the enterprise?
Our customers who have installed GEF can take advantage of this foreign key relationship to light up the entire enterprise with transactional data using ArcGIS Enterprise very quickly. This is exactly what Provincie Noord Brabant (PNB) has done.
PNB had 3 different manual ETL processes in their previous architecture. Data replication requires ETL - creation, maintenance and approval. ETL increases footprint and introduces the chances for errors. Users have to wait for ETL to pass change control approvals. Because PNB has GEF and S/4HANA, it was straightforward to conduct a POC to show the advantages of eliminating those 3 ETL processes. Remember that HANA and HANA Cloud are designed to help you avoid replicating data. The impact?
"[we] are able to deliver information 40% faster to the business because many manual and error prone business processes were eliminated. It was all made possible by ArcGIS Enterprise on SAP HANA and HANA’s virtual data integration and data modeling capabilities."
Provincie Noord Brabant
Here is a high level architecture of what PNB did:
PNB nominal architecture
One of the many advantages of S/4HANA is the availability of ABAP CDS views. These provide context rich information without having to know about the underlying schema and presents the data with easy to understand column names. Accessing the ABAP CDS views using SDA is done at the ABAP level so S/4HANA security is respected. It's the supported method for accessing S/4HANA data. In fact, S/4HANA uses CDS views to reference the data within the system itself.
I defined a SQL view in HANA using this SQL statement which queries an ABAP CDS view that contains notification information. Remember that the CDS view is registered as a virtual table in the ArcGIS HANA tenant but actually resides in an S/4HANA system.
SELECT "NOTIFICATIONNUMBER", "SYSTEMSTATUSCODE", "USERSTATUSCODE", "SYSTEMSTATUS", "USERSTATUS",
ST_GEOMFROMEWKT( "NOTIFICATIONGEOMETRY") AS "NOTIFGEOMETRY",
"NOTIFICATIONDESCRIPTION", "REQUIREDSTARTDATE", "REQUIREDSTARTTIME", "REQUIREDENDDATE",
"REQUIREDENDTIME", "TECHNICALOBJECTTYPE", "TECHNICALOBJECT", "WORKCENTER", "PRIORITY",
"PLANNERGROUP", "PLANNINGPLANTNAME", "PLANNINGPLANTID", "MAINTENANCEACTIVITYTYPE",
"PERSONRESPONSIBLE", "EQUIPMENT", "EQUIPMENTNAME", "EQUIPMENTSERIALNUMBER", "EQUIPMENTID",
"EQUIPMENTDESCRIPTION", "SIZEORDIMENSIONTEXT", "ASSETMANUFACTURERNAME",
"MODELNUMBER", "MANUFACTURERPARTNMBR", "INVENTORYNUMBER", "OPERATIONSTARTDATE",
"MANUFACTURERCOUNTRY", "SERIALNUMBER", "CONSTRUCTIONYEAR", "PLANTSECTION",
"MAINTENANCEPLANNINGPLANT", "SORTFIELD", "SUPERIORTECHNICALOBJECT"
FROM "NAPERVILLE"."S4U_ZE2IQ_NOTIF"
WHERE "NOTIFICATIONGEOMETRY" != '' OR "NOTIFICATIONGEOMETRY" IS NULLThe query worked in HANA Studio, but when using a view in ArcGIS Pro, this occurred:
Error reading features in ArcGIS Pro
It's important to use the right datatype to cast an ST_GEOMETRY value into. The CDS view we created happened to use VARCHAR( 1024) for the EWKT representation. The danger is the EWKT length can explode especially for a long linestring or a polygon with many vertices. The better choice is the CLOB datatype. Fortunately, in this situation, that wasn't the issue. So what was it?
It's a combination of the HANA optimizer shuffling around parts of queries and what ArcGIS Pro adds to a query layer (or the query against the feature class's underlying HANA table...)
ArcGIS adds a filter against the view-port by default. That means only the data that is within that rectangle (view-port) will be retrieved. So, instead of executing the original query, ArcGIS executes
SELECT * FROM (
SELECT "NOTIFICATIONNUMBER", "SYSTEMSTATUSCODE", "USERSTATUSCODE", "SYSTEMSTATUS",
"USERSTATUS", ST_GEOMFROMEWKT( "NOTIFICATIONGEOMETRY") AS "NOTIFGEOMETRY",
"NOTIFICATIONDESCRIPTION", "REQUIREDSTARTDATE", "REQUIREDSTARTTIME", "REQUIREDENDDATE",
"REQUIREDENDTIME", "TECHNICALOBJECTTYPE", "TECHNICALOBJECT", "WORKCENTER", "PRIORITY",
"PLANNERGROUP", "PLANNINGPLANTNAME", "PLANNINGPLANTID", "MAINTENANCEACTIVITYTYPE",
"PERSONRESPONSIBLE", "EQUIPMENT", "EQUIPMENTNAME", "EQUIPMENTSERIALNUMBER",
"EQUIPMENTID", "EQUIPMENTDESCRIPTION", "SIZEORDIMENSIONTEXT", "ASSETMANUFACTURERNAME",
"MODELNUMBER", "MANUFACTURERPARTNMBR", "INVENTORYNUMBER", "OPERATIONSTARTDATE",
"MANUFACTURERCOUNTRY", "SERIALNUMBER", "CONSTRUCTIONYEAR", "PLANTSECTION",
"MAINTENANCEPLANNINGPLANT", "SORTFIELD", "SUPERIORTECHNICALOBJECT"
FROM "NAPERVILLE"."S4U_ZE2IQ_NOTIF"
WHERE "NOTIFICATIONGEOMETRY" != '' OR "NOTIFICATIONGEOMETRY" IS NULL
) WHERE "NOTIFYGEOMETRY".ST_IntersectsRectPlanar(<view-port-geometry>) = 1;This should prevent HANA from trying to perform the conversion of a blank or null EWKT representation to an ST_GEOMETRY value... however, at least in this case, the HANA optimizer steps in to optimize performance and rewrites the query to something like this:
SELECT "NOTIFICATIONNUMBER", "SYSTEMSTATUSCODE", "USERSTATUSCODE", "SYSTEMSTATUS", "USERSTATUS",
ST_GEOMFROMEWKT( "NOTIFICATIONGEOMETRY") AS "NOTIFGEOMETRY", "NOTIFICATIONDESCRIPTION",
"REQUIREDSTARTDATE", "REQUIREDSTARTTIME", "REQUIREDENDDATE", "REQUIREDENDTIME",
"TECHNICALOBJECTTYPE", "TECHNICALOBJECT", "WORKCENTER", "PRIORITY", "PLANNERGROUP",
"PLANNINGPLANTNAME", "PLANNINGPLANTID", "MAINTENANCEACTIVITYTYPE", "PERSONRESPONSIBLE",
"EQUIPMENT", "EQUIPMENTNAME", "EQUIPMENTSERIALNUMBER", "EQUIPMENTID", "EQUIPMENTDESCRIPTION",
"SIZEORDIMENSIONTEXT", "ASSETMANUFACTURERNAME", "MODELNUMBER", "MANUFACTURERPARTNMBR",
"INVENTORYNUMBER", "OPERATIONSTARTDATE", "MANUFACTURERCOUNTRY", "SERIALNUMBER",
"CONSTRUCTIONYEAR", "PLANTSECTION", "MAINTENANCEPLANNINGPLANT", "SORTFIELD",
"SUPERIORTECHNICALOBJECT"
FROM "NAPERVILLE"."S4U_ZE2IQ_NOTIF"
WHERE
ST_GEOMFROMEWKT( "NOTIFICATIONGEOMETRY").ST_IntersectsRectPlanar(<view-port-geometry>) = 1
AND ("NOTIFICATIONGEOMETRY" != '' OR "NOTIFICATIONGEOMETRY" IS NULL)As a result, the geometry construction is moved to the WHERE-clause before filtering out empty strings. When attempting to plot the notification geometries on the web map in ArcGIS Pro, it fails as shown above. Instead, the answer is using a CASE statement in the target list like this:
SELECT "NOTIFICATIONNUMBER", "SYSTEMSTATUSCODE", "USERSTATUSCODE", "SYSTEMSTATUS", "USERSTATUS",
CASE "NOTIFICATIONGEOMETRY"
WHEN '' THEN NULL
ELSE ST_GEOMFROMEWKT( "NOTIFICATIONGEOMETRY")
END AS "NOTIFGEOMETRY",
"NOTIFICATIONDESCRIPTION", "REQUIREDSTARTDATE", "REQUIREDSTARTTIME", "REQUIREDENDDATE",
"REQUIREDENDTIME", "TECHNICALOBJECTTYPE", "TECHNICALOBJECT", "WORKCENTER", "PRIORITY",
"PLANNERGROUP", "PLANNINGPLANTNAME", "PLANNINGPLANTID", "MAINTENANCEACTIVITYTYPE",
"PERSONRESPONSIBLE", "EQUIPMENT", "EQUIPMENTNAME", "EQUIPMENTSERIALNUMBER", "EQUIPMENTID",
"EQUIPMENTDESCRIPTION", "SIZEORDIMENSIONTEXT", "ASSETMANUFACTURERNAME", "MODELNUMBER",
"MANUFACTURERPARTNMBR", "INVENTORYNUMBER", "OPERATIONSTARTDATE", "MANUFACTURERCOUNTRY",
"SERIALNUMBER", "CONSTRUCTIONYEAR", "PLANTSECTION", "MAINTENANCEPLANNINGPLANT", "SORTFIELD",
"SUPERIORTECHNICALOBJECT"
FROM "NAPERVILLE"."S4U_ZE2IQ_NOTIF";With this approach, the optimizer can only move the whole CASE construct to the WHERE-clause and will never try to construct a geometry from an empty EWKT.
With that, here is the web map showing notification geometries retrieved from S/4HANA Plant Maintenance on the fly:
Notification geometries displayed in ArcGIS Pro
This illustrates how straightforward it is to expose transactional data from SAP ERP (in this case S/4HANA using ABAP CDS view) on an ArcGIS web map or in any ArcGIS information product. Your enterprise can use this capability by putting the geodatabase on HANA or HANA Cloud and get the advantages that PNB realized: the elimination of error prone and manual business processes resulting in insight reaching the stakeholders 40% faster. Lighting up the entire enterprise with transactional data is a real competitive advantage!
6 Comments
