Cannot access data error - Operations Dashboard

10715
15
04-30-2018 11:33 AM
LSaunders
New Contributor III

I have been trying out the new Operations Dashboard, but have to use services that are fed from our Oracle database through query layers, the published as map services to our AGS (10.6).

In the old Operations Dashboard, I could load the layers from the service into a map and then use those to feed widgets.  In the new application (web based) I get an error on all of these layers "Cannot Access Data".  

Has anyone successfully done this with data that is being published from a query layer? 

15 Replies
DerekLaw
Community Moderator

Hi Leah,

The new Operations Dashboard for ArcGIS app should be able to work with map services that contain query layers. I would suggest you recheck your access and permissions settings for the data and web service.

Hope this helps,

0 Kudos
LSaunders
New Contributor III

Thanks Derek!  My access is fine, the server has the database added as it is on my production server.  I can add the services to a map, have them refresh as per usual.  Still get the error.  This is for any and all query layers coming from our Oracle databases.

0 Kudos
DerekLaw
Community Moderator

Hi Leah,

Can you please contact Esri Tech Support and open a ticket so they can investigate your issue further?

Thanks,

0 Kudos
LSaunders
New Contributor III

Turns out this is not supported!  Talked to tech support and the functionality is not possible, and they were not sure if it is on the list for consideration. There is an enhancement request and an idea on the ideas page. I'll update when I have this information.

0 Kudos
LSaunders
New Contributor III

Extra information...

There is also an ArcGIS Idea on which you can vote and add a comment: https://community.esri.com/ideas/6954

Here is a Geonet post on the topic with a reply from a member of the development team: https://community.esri.com/thread/70899

Enhancement request: NIM088331 : Allow outStatistics to be calculated in the Query operation on a map service for query layers.

MichaelVolz
Esteemed Contributor

Does ESRI have any documentation on what operations  are currently not supported with data from query layers?  I am seeing sorting issues of queries performed on query layers as 1 example of loss of functionality.

0 Kudos
patrickb
Esri Contributor

A lot of the linked to posts in the above are old and some (but not all) things have changed.

Generally speaking, Operations Dashboard supports whatever service layers advertise they support (or at least, we try to). Behind the scenes, the service layer's REST endpoint will contain metadata that tells us what we can do with the service. Look for JSON something like the following (I've highlighted some of the ones that are looked at)  

"supportsAdvancedQueries": true, 
"supportedQueryFormats": "JSON, AMF, geoJSON",  
"ownershipBasedAccessControlForFeatures": {"allowOthersToQuery": true},  
"useStandardizedQueries": true,  
"advancedQueryCapabilities": {   
  "useStandardizedQueries": true,  
  "supportsStatistics": true,  
  "supportsHavingClause": true,   
  "supportsOrderBy": true,  
  "supportsDistinct": true,   
  "supportsPagination": true,   
  "supportsTrueCurve": true,   
  "supportsReturningQueryExtent": true,   
  "supportsQueryWithDistance": true,   
  "supportsSqlExpression": true  
},

Tip: Operations Dashboard likes useStandardizedQueries=true. This is a setting on your Server/Portal. 

If useStandardizedQueries=true and you have a service layer that advertises it supports something but are still having problems, we'd have to look deeper for an explanation. It could be a bug in Operations Dashboard, in the service implementation, or maybe even in the database.  

In the meantime, a workaround **might** be to sling the summary statistic queries in the query layer definition itself. In Operations Dashboard, you can then create your visualizations using the 'Features' option rather than the 'Grouped Values' option. For example, the following query (SQL Server) creates a query layer for crime counts in the last 24 hours, broken down by crime type.   

SELECT COUNT(*) AS incident_count, ucr_type from CrimeDB.dbo.incidents
WHERE DISPATCH_DATE_TIME >= DATEADD(hour,-24, GETUTCDATE()) and DISPATCH_DATE_TIME <= GETUTCDATE()
GROUP BY ucr_type

(Note: To create query layers like this, I believe you'd need Pro as ArcMap might not be up to the task). 

LSaunders
New Contributor III

The links above were provided by tech support yesterday!  They confirmed that I could not use my xy event layers from my query layers in Operations Dashboard.  My query layers work well in the Windows Operations Dashboard and I use them on a daily basis in other maps and applications. I can't group by in the query as I need the individual points for incidents and we have moving patrol vehicles that we track a count of.

0 Kudos
patrickb
Esri Contributor

Apologies. I meant that the posts/threads origins were a bit dated, not that the links had not been provided recently. I believe they were both created circa 2013.

XY event layers add another layer of complexity, and a different set of factors comes into play. When creating the query layer that your XY event layer is based on, are you able to make sure you use one (integer) field as the unique identifier?  I did this (in Pro), published my map. and was able to create summary statistics and charts in a dashboard. 

If not, maybe you can take advantage of your Oracle database's built-in functionality (assuming it is spatially enabled). I don't have an Oracle instance handy, but I was able to create a query layer like the following (i.e. create the point on the fly using SQL Server's built-in abilities),  publish the map,and again was able to create summary statistics and charts in a dashboard. 

select ID,DC_DIST,SECTOR,DISPATCH_DATE_TIME,LOCATION_BLOCK,UCR_GENERAL,TEXT_GENERAL_CODE,DIV_NAME,PSA_NUM,TimeBlock,UCR_CATEGORY,UCR_Type,geometry::STGeomFromText('POINT('+convert(varchar(20),X)+' '+convert(varchar(20),Y)+')',3857) as SHAPE from
CrimeDB.dbo.crime_incidents
WHERE DISPATCH_DATE_TIME >= DATEADD(hour,-24, GETUTCDATE()) and DISPATCH_DATE_TIME <= GETUTCDATE()

If none of this works, are you able to use map services that have both a layer (i.e. the xy event layer) and a stand alone table? In that case, the idea is to use the layer on the map, but the stand alone table for the other visualizations. (Interactivity between elements via actions should still be possible as there will be a common field)

Note: The way Windows Operations Dashboard and the new web-based Operations Dashboards works for doing things like counts is fundamentally different (client side vs server side) and the troubleshooting steps will be a bit different. Hopefully we can get something to work for you. Please feel free to reach out to us at dashboard4arcgis@esri.com if you'd like to move this to a private thread.  

0 Kudos