External Database View to Feature Service for Use in Dashboard

[GeneSipes1]

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! 

https://community.esri.com/community/gis/enterprise-gis?sr=search&searchId=a90e2143-7840-43d2-8517-f...‌ https://community.esri.com/community/gis/enterprise-gis/arcgis-for-server?sr=search&searchId=c6f6676...‌ https://community.esri.com/groups/geodatabase?sr=search&searchId=1dd545be-8c0e-4bb0-93da-ac09587c8f2...‌

[Asrujit_SenGupta]

Register a table or view with the geodatabase—ArcGIS Help | ArcGIS Desktop 

Note: This tool is available starting 10.5.

Registering a view with the geodatabase allows the view to be included in read-only feature services.

[Anonymous User]

There are two additional approaches, one based on your and other different:

- The one close to your proposal: why deleting the table in the geodatabase? You can simply truncate it and populate with the data in the original tables or view. This could be done using SQL and a script that you can launch every night.

- Anycase, I you want to access live data, you can create a view from your SDE geodatabase pointing to your IBM Maximo geodatabase (depending the RBDMS that is supporting your GDB, mechanism for doing this could be different). Then register the view and create the feature service joining both layer and view.

Pd. Maybe your are experiencing that error because the ArcGIS Service is "blocking" your layer and cannot be deleted. Try publishing the layer unchecking the option for blocking the layers.

Hope this helps

Jesús de Diego