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?
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 NULL
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
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:
No comments:
Post a Comment