AnsweredAssumed Answered

External Database View to Feature Service for Use in Dashboard

Question asked by gsipes on Jul 31, 2017
Latest reply on Aug 3, 2017 by jesdial

ArcMap/Catalog 10.4
ArcServer 10.3

IBM Maximo 7.5 (SQL Asset Management Software)

 

Steps:

  1. Using SQL Studio, write database spatial view on Maximo workorder table with the following query:

CREATE VIEW dbo.DrainageWOs AS
SELECT CAST(rowstamp AS int) AS ObjectID, wonum, status, location, agx, agy, failurecode, problemcode, worktype, description, actstart, actfinish, actlabhrs, actlabcost, actmatcost, acttoolcost, actlabcost + actmatcost + acttoolcost AS TOTALCOST, geometry::STPointFromText('POINT (' + CONVERT(varchar, agx) + ' ' + CONVERT(varchar, agy) + ')', 102740)
AS WoLoc FROM dbo.workorder
WHERE (actstart >= '2016/10/01') AND (actfinish <= GETDATE()) AND (problemcode IN ('DR01', 'DR02', 'DR03', 'DR05', 'DR06', 'DR07', 'DR08', 'DR09', 'DR10', 'DR11', 'DR12', 'DR13', 'DR23',
'DR24', 'DR37', 'DR38', 'DR40', 'DR45'))

   2. In Catalog, right-click the view and select export... to geodatabase (single). I export the view to an SDE Geodatabase, where it becomes a feature class. At this point, I publish it as a feature service and consume it in Operations Dashboard with no problems. Meaning, I have full use of widgets and no data source erros in Dashboard. This is great and all, but it is static data. In order to get fresh data I would have to do this each time and republish. I want to find a python script that will do this for me, and overwrite the FC each day and republish the service. I know next to nothing about python, and have been toying around with it. First I created some steps in model builder and then exported as python, but I kept getting the error "failed to execute output already exists", even though I am using the overwrite python snippet.

 

Has anyone every accomplished this? That is, automated chron task, spatial view to feature service? Any advice, tips, hints would be greatly appreciated. Thanks community! 

 

Enterprise GIS ArcGIS Enterprise Geodatabase

Outcomes