Publish services using ArcGIS Pro with Joined Data

[MichaelHino2]

This request is to implement an enhancement to ArcGIS Pro to be able to join, relate, or whatever process is necessary, data from different data sources and publish it as a service to be consumed in a web application while still allowing the data to be dynamic, reading from the data sources.

Currently, ArcGIS Pro cannot publish a service to ArcGIS Enterprise while an in memory join is present. However, this function can be critical when needing to link to different data sources (feature class and tabular data). For instance, if the tabular data is part of a mission critical read only system that is dynamic that the user would like to connect to a feature class and bring into a web mapping application. I have also tried creating a relationship class but that cannot be created on a view.

[TanuHoque]

By any chance the database where your "standalone sql table from a separate sql server instance" is a non-geodatabase...? or maybe a geodatabase but the table is not a registered table?

@ScottAndersonMississauga 

I got the answer from your last comment. For some reason I didn't see that comment when I posted mine.

With that as I said before the bug is fixed in 11.3. Please let us know if you still run into this issue in 11.3 

[ScottAndersonMississauga]

Yes that's correct @TanuHoque . It's a separate SQL Server instance.  Is the bug also causing the % sign when adding in the Map?  Or is this works as designed functionality. I  only ask so that when I document this process for other user I use the recommended workflow.  If that workflow is to join to the outside DB via the Join dialog and NOT by adding to the Map, that's fine and I'll document as such.  If the % thing is still a bug I may add that as a future option if that's corrected at some point. I'm not sure what the technical back-end process is so they may be entirely different behind the scenes.

Thank you everyone.

[TanuHoque]

@ScottAndersonMississauga 

• It doesn't matter how out create a join (*). Whenever you create join in ArcGIS Pro, and one or both parties is an un-registered table, you will see % sign in fully qualified field names.
  • having % sign in fully qualified field name is by design
• The bug (BUG-000156380) was that when you publish that as a map service, and you make query using one of those field names (with % sign in it), map service query was failing
  • as you mentioned, typically you run into issues like when you add that map service in Map Viewer and you click a feature to see its attributes; popup window either shows nothing or fails.

(*) IIRC someone might have mentioned earlier in this thread that creating the join in a database view or using query layer as solution. Those are good solutions. I believe that would work as long as they both belong in the same SQL Server instance (in your case they are from two different databases) -- , unless there are some additional capabilities at the db level that will allow you to create joins using tables from multiple databases instances. I'm not aware of that.

[ScottAndersonMississauga]

Not to be argumentative @TanuHoque , but please see these screen grabs.  The first shows the resultant attribute fields when using a direct join and the second shows the join when using the table from the map. I suspect there is a bug in one workflow that doesn't exist in the other.  However, it does function "correctly" at this time.  Hopefully this method also doesn't break.

And the resultant successful pop-up in a Portal.

[AndreaB_]

Hi all,

I'm using Enterprise 11.1. I can publish a map service and feature service (no editing enabled) with a join (Feature class to table) to my Enterprise Portal. I can view the joined fields in the popup in a map viewer map in Portal if I use the map service. The feature service will not load the point data nor the table for the point data that is joined to the table. No error messages it just doesn't load.

Is the ability to use the feature service in a web map in Portal and see the joined data something that has been added after Enterprise 11.1? I'm also using ArcGIS Pro 3.1.5. Thanks.

[TanuHoque]

@AndreaB_ 

FeatureServices don't support joins. Therefore, when you access your layer/table (that was originally authored with joins) via FeatureService REST resources, the join information are dropped.

I'm curious to know if you are not planning edit your data, why do you need a FeatureService?

[AndreaB_]

@TanuHoque ok, thanks. We offer a FeatureService so the end users can see the related tables and also so they can change the symbology of the layers. We also offer export/extract capability sometimes. 

[TanuHoque]

@AndreaB_ 

We offer a FeatureService so the end users can see the related tables and also so they can change the symbology of the layers

You can still do that with map services... 

As you know there are two ways you can consume data from a map service in a map viewer or an experience builder app.

1. You can add that as a map image layer, and/or
2. You can add a layer off a map service, as a feature layer (just like you do from a feature service)

Regardless how you add them, you should be able to modify symbology and access related records. We had some issues accessing related records which got fixed in 11.3 for map services. And Map Viewer enhancement went in for 11.3 for feature layer (off a map service). In 11.4 it should work for map image layers too. Pls let me know if you find something not working.

We also offer export/extract capability sometimes.

Are you asking about exporting from portal item page?

[AndreaB_]

Wow @TanuHoque Thank you! I didn't realize I could add a layer off a map service as a feature layer. That could solve some problems. However, here are some things I am seeing:

When I add the map image layer service to ArcGIS Online or Portal Map Viewer I am able to change symbology. However, when I add the map image layer service to ArcGIS Pro 3.2.4 I am not able to change symbology - see below, greyed out. I can add an individual feature layer to change symbology but that's an added step that users might not understand. Is this by design? Is this a bug? 

 

I am not able to view related records in Map Viewer with map image layer or feature layer service. I think this is because my relationship class is set to origin - table and destination feature class. I don't think this is supported - I'm in Enterprise 11.1. I hope someday? Maybe it was enhanced to offer this in Enterprise 11.3/11.4? I can see the related records from the map image layer in ArcGIS Pro.

Exporting data from the map image layer service isn't possible. That would be nice if it were. Feature Service option -  ArcGIS Server manager: capabilities Feature Access - Extract - 

Adding as a Feature layer from the Map image layer or using the Feature Service allows data export in ArcGIS Pro. Not sure where to find this option in a Map Viewer map or the Portal item page?

Thank you,

Andrea

[GerryGarza]

Hi All,

I have been using the "SQL table-to-Feature Class Join" service for over 10 years or more as a Map Image Layer (read-only). It does not work if Feature (editable) is used, but everyone here knows that already. I am on ArcGIS Pro 3.4.2 and AE 11.3. Does it work, kind of? It does work if the layer is added to the Map Viewer Classic, but the data from the connected SQL table does not pull across into the popup window, most important for my users. The attribute table shows the attributes from both the geometry and the table.

It works if the layer is added to the Map Viewer, but the fields under Popups are off by default. The attribute table shows the attributes from both the geometry and the table.

One other strange note. I manage two systems and it only works with the one where I am the DBO of the SQL databases. Not sure if this is related, but the DBO credential system has always been more stable.

Regards,

Gerry