topic Re: Cannot access data error - Operations Dashboard in ArcGIS Dashboards Questions

Cannot access data error - Operations Dashboard

LSaunders — Mon, 30 Apr 2018 18:33:16 GMT

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?

Re: Cannot access data error - Operations Dashboard

DerekLaw — Wed, 02 May 2018 17:15:59 GMT

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,

Re: Cannot access data error - Operations Dashboard

LSaunders — Wed, 02 May 2018 17:19:39 GMT

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.

Re: Cannot access data error - Operations Dashboard

DerekLaw — Wed, 02 May 2018 22:03:01 GMT

Hi Leah,

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

Thanks,

Re: Cannot access data error - Operations Dashboard

LSaunders — Tue, 15 May 2018 17:51:33 GMT

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.

Re: Cannot access data error - Operations Dashboard

LSaunders — Tue, 15 May 2018 18:04:49 GMT

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.

Re: Cannot access data error - Operations Dashboard

MichaelVolz — Tue, 15 May 2018 20:08:40 GMT

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.

Re: Cannot access data error - Operations Dashboard

patrickb — Fri, 10 Dec 2021 23:18:16 GMT

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).

Re: Cannot access data error - Operations Dashboard

LSaunders — Wed, 16 May 2018 14:33:02 GMT

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.

Re: Cannot access data error - Operations Dashboard

patrickb — Wed, 16 May 2018 16:21:11 GMT

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.

Re: Cannot access data error - Operations Dashboard

ShelbyZelonisRoberson — Tue, 22 May 2018 16:11:10 GMT

Hi Patrick -- I'm having a related problem and I was hoping you could provide insight. I am able to get query layers to work in my dashboard items (the query layers are coming from SQL server, similar to what your setup seems to be). However, I don't have any options to create Actions in any of the items (charts, lists, etc) with the layers that are built on query layers. There are no Actions tabs to configure when I choose these data sources. Patrick, are you able to use Actions with your query layer feature services or is this not possible in Ops Dashboard?

Re: Cannot access data error - Operations Dashboard

DarrenHaag — Mon, 28 May 2018 01:51:57 GMT

So I was running into similar problems and will give a rundown of my findings.

In my case, we have capitol improvement points and lines on a map. I am creating a dashboard to filter by year, type, status, etc using the widgets instead of selectors. I needed a table with all the records to supply data to the widgets/metrics from both points and lines. The fields in the points and lines match, so I created a sql view to union the points and lines into a table, then created a new objectid from the globalid’s. Then registered the view with the geodatabase.

Quasi code below

select c.*, cast(ROW_NUMBER() OVER(ORDER by c.globalid) as int) As objectid

from

(points union lines) c

this gave me the table I needed for the metrics. When using it in the dashboard I could create metrics, but as another poster mentioned, I could not use widgets using data from the table to control layers on the map even though the fields matched 1 to 1. Conversely, I could use layer selections on the map to filter the widgets created by the standalone table. So it works in one direction, but not both.

With that attempt thwarted, I decided to go the sql spatial route. I got the x,y from the points, then created an x,y from the lines based on the nearest point on a line to the centroid of the feature extent.(dealing with multiple line segments per feature). With the x,y’s, I updated the view to a spatial view of points. Same code as above, adding in the logic to pull x,y’s from both tables, then geometry::point(x,y,srid) as shape after the row_number function. In this attempt I could see the points, see the attribute table, etc..., in the map. But when trying to create a metric with the service, I got the cannot acces data icon. Same sharing as before.

third attempt, create the table view as before only adding in the x,y’s as fields. Then create a query layer in pro and generate the points in the query layer instead of the view. I used the globalid as my uniqueid in the query layer spatial properties window which then generates your esri_oid. Same issue as above when trying to create metrics only this time it said cannot access data of 1038 records(number of records in my query layer). Or something similar. Still didn’t work, but an error at least giving me the count of records in the layer. (I’ll note that even though it says it can’t access the data, if you select a drop down to select fields for statistics, etc, all the fields from the layers populate). Progress.

final attempt. for some reason, I was stuck on objectid being the issue. I was using globalid in the spatial properties window of the query layer dialog as my unique Id because it defaults to that. But I had also created a new objectid in the union view, so it was also unique. I used objectid as my unique Id in the spatial properties window and created the query layer. This time, the query layer created, but did not create an esri_oid field and instead made my objectid field the unique Id field with the field type objectid instead of integer. Plugged this layer into the dashboard and everything worked flawlessly. Success.

now, I didn’t go any further down the rabbit hole to test why it wasn’t working before. Did it need a field called objectid to be the unique identifier? Did the fact I had objectid as an integer field in my query layer throw everything off because it wasn’t the designated unique field? I’m guessing one or both of those 2 is the problem but I didn’t test.

Notes::

as stated above. Creating widgets from standalone tables and then trying to filter map layers by those tables didn’t give an action option for the map layers.

now, I still have my cip polylines. I’ve created a centroid point for them, but still want to show the associated lines when I select a point on the map.(using globalid as my connection between the 2 layers). I can filter just the lines I want when I select a point. But, when I don’t have a point selected, all the lines show up and I can’t control them with the widgets either since they were created using the points query layer. The question I have is. Is there a way to default a layer to filter out all the features and only show a feature after an action. In my case, I select a point on the map and the lines associated with that point show up. If no points are selected, all the lines are “hidden”. If not, is this functionality on the radar for dashboards?

sorry for the extremely long winded response. I was hoping to give users a workaround or ideas as to why the query layers may not be working as well as provide enough information for debugging purposes.

Re: Cannot access data error - Operations Dashboard

BrianFausel — Thu, 21 Jun 2018 18:51:09 GMT

Darren's response was the answer to my dilemma. After seeing the error "Cannot Access Data" with my spatial view, I was finally able to get the new "web-based" Op Dashboard to work by using OBJECTIDs. This works even with our legacy ArcGIS Server 10.3.1 / SQL 2008 R2 environment. I'll try to sum it up as best I can:

The key seems to be to use the OBJECTID attribute rather than the GLOBALID when you define the unique identifier in ArcMap. I have a pretty simple 1:M relationship modeling Hydrants(1):Events(M). The relationship class keys are Hydrant(GLOBALID):Event(GUID). Both the feature class and event table are in the same SDE geodatabase.

When adding the spatial view to ArcMap to create an ArcGIS Server map service, I was originally using the Event GLOBALID as the unique identifier. While the Event GLOBALID is unique, any time you add the service to the Op Dashboard it would give the message "Cannot Access Data." From there you couldn't create pie or serial charts.

SOLUTION: I re-created the map service using the Event OBJECTID as the identifier and now it works great with the Op Dashboard as well as the Web AppBuilder Infographic widgets.

Sidenote: The thing that was driving me most crazy was that I had other more complicated spatial views that were working the whole time while this new one was not. The other spatial views involved an SDE feature class and multiple business tables from an external asset management database. In that case, I was using the OIDs of the external database. So I can vouch that it also works outside SDE, provided you have that unique identifier.

Re: Cannot access data error - Operations Dashboard

DrewDowling — Tue, 28 Aug 2018 04:23:49 GMT

This helped me immensely, thank you. I had created both spatial and non spatial views without an explicit attribute called objectid. I then added these views to ArcMap using the Add query layer option. I had defined a composite unique index in arcmap when I added these views to ArcMap. The result was my dashboard was giving the no data error.

After reading Brian Fausel and Darren Haag posts I altered the underlying views to include a unique int field called objectid. I then registered these views with the geodatabase and added them to the .mxd as any other table or feature class. After these steps these views started working in dashboard without a problem. Grouping, charting etc is working fine so far.

Steps:

Create your view using SQL which includes a unique int field called objectid
register the view with the geodatabase
Add the view to ArcMap and publish feature service
add service to web map
Use the webmap to dashboard.

Re: Cannot access data error - Operations Dashboard

Anonymous User — Tue, 23 Oct 2018 11:52:03 GMT

In support of what's been discussed above - if you look to the definition of the "Source" - is ESRI using this to determine that an objectid exists?

Data Type: XY Event Source
Server: xxxx
User: xxxxx
Instance: sde:sqlserver:xxxxxx
Location: xxxxx
Table: xxxxxx
X Field: LONGITUDE
Y Field: LATITUDE
Has Object-ID Field: Yes

Re: Cannot access data error - Operations Dashboard

ABVŠT — Thu, 12 Nov 2020 09:42:13 GMT

Hello,

Maybe anyone have video how to solve this problem? Tanks