can the dashboard access external data in a SQL table/view

3510
8
02-05-2016 11:33 AM
MikeHargreaves
New Contributor III

I'm new to dashboard, and was thinking it would be a nice tool to display various stats from our Accela permitting system.  Can I access the SQL table directly, or do I first need to make a service out of it?

0 Kudos
8 Replies
JoeHershman
MVP Regular Contributor

Yes that would be nice.  Unfortunately, as far as I have ever been able to figure out it is not possible.  I think for Portal to reach its potential this is a necessity. 

0 Kudos
MikeHargreaves
New Contributor III

Thanks Joe.  In the What's New section:

Operations Dashboard for ArcGIS | ArcGIS

t shows a matrix for Online and Portal, and it says the current version is supposed to have "Support for stand-alone tables as data sources" with the caveat that it is "Currently (only available) as external data sources"

But when I look at the help for external data sources, it seems to only discuss ArcGIS Services...

Can you create a service from a table if there is location info?  What is no location info?

0 Kudos
ChristopherOlsen
Regular Contributor

Mike-

In order for you to use the table as a data source in Ops Dashboard, it has to be accessible through the service, i.e., through the REST endpoint as a table.  For example, the following service has a feature class and a table available that you could use as data sources:

http://services.arcgis.com/c3gjVStQJSOiRJaj/ArcGIS/rest/services/TestRelationshipsForPro/FeatureServ...

The other caveat with using a table is that it has to have an OID field in order to be brought in as a data source. This help topic has more information on external data sources.

Thanks,

Chris

0 Kudos
MikeHargreaves
New Contributor III

Thanks Chris.  So it sounds like the able must reside in SDE, or how else do you get the OID field?  We are looking to use Socrata as a data warehouse.  I was thinking that the Dashboard would be a handy way to show the status of permits - pie chart showing permit types, another showing % completed, etc.  I can create URLs to access that data and do various functions, but it sounds like you are unable to directly use just a free standing SQL table...  Here are some examples of the URLs:

Socrata API end point:

https://data.srcity.org/resource/fpj8-e7vu.json

Count records where current status = Issued:

https://data.srcity.org/resource/fpj8-e7vu.json?$select=count(*)&$where=statuscurrent=%27Issued%27

Any way to bring this into Dashboard?

0 Kudos
ChristopherOlsen
Regular Contributor

There isn't any way to bring in the data without some kind of service behind it- either ArcGIS Online, Portal or Server.  And you could use a table view as well (with OID) from either a map service or feature service.

0 Kudos
JacksonTrappett
Occasional Contributor II

I have this same question - I'm trying to add a non-spatial SQL table to a dashboard.  How do I share the table as a service in pro?  The only way I've found is to make it a hosted table, but that makes the data static.  I've added it to a map as a query layer, can I publish it without having other map data in my map?

If I try to publish it as a web map from pro to portal - it publishes, but the table is just dropped.

If I try to publish it as a web layer - it gives me an error "Map does not have layers"

If I try to publish it as a table directly, it only gives me the option to share it as a hosted table.

How do I publish it as a registered table so that I can add it to my dashboard?

0 Kudos
Simon-Coleman-TfN
New Contributor

My organisation has been sucessful publishing tables by uploading as a map/feature service, with a geometry field that only contains null values. We use a database view to create the geometry field. The data isn't visible in ArcGIS Pro, but if you go ahead, you have a published service containing the table data.

0 Kudos
AnthonyWhitley
New Contributor II

April 2021; has this question of external database sources not been explored any further?