idea Publish services using ArcGIS Pro with Joined Data in ArcGIS Pro Ideas

Publish services using ArcGIS Pro with Joined Data

MichaelHino2 — Thu, 06 May 2021 19:46:43 GMT

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.

Re: Publish services using ArcGIS Pro with Joined Data

AllanBenvin_yyc — Mon, 03 Apr 2023 16:31:51 GMT

Allow for the display of related data in ArcGIS Enterprise Portal without using relationship classes

I would like to second this idea. From our perspective, we have a large environment with over 900 datasets in our read-only geodatabase that are published as services to our Enterprise portal as referenced services. The data can be joined/related in a myriad of ways. We are trying to create viewing apps that have one to many relationships between things that will show in a popup window.

There is an ESRI post about this that says in memory relates are not supported for web feature layers.

24040: In-memory relates are not supported—ArcGIS Pro | Documentation

The article suggests using database relationship classes to get this functionality but this recommendation is not practical. Relationship classes are made for editing data. They enforce behaviour such as setting parent field values to null when records are deleted. Relationship classes also add additional resource requirements to the database in terms of memory and dependencies. Adding relationship classes to a read-only database to meet all possible data relationships is really not practical. It would result in hundreds of relationship classes and would make the database unmanageable. Data and schema updates would be a nightmare. I would imagine that this is likely why in-memory relates were created in the first place. They give the user the ability to relate two datasets for read-only purposes using tools such as Identify without having to make structural changes to the database.

As we move our hundreds of users to Enterprise Portal with targeted apps we lack the ability to have a simple relate between two objects without having to modify the structure of our database. To me this seems like a huge lack in functionality as we move users to portal-based applications.

There may be technical reasons as to why in-memory relates are not supported in feature services but as an alternative maybe functionality can be added to allow for relates or joins between services to be made at the portal level in an application or a map that would offer the same functionality for related records that has existed in ArcMap for over 20 years.

Thanks.

Re: Publish services using ArcGIS Pro with Joined Data - Status changed to: Needs Clarification

TanuHoque — Thu, 31 Aug 2023 00:51:38 GMT

@MichaelHino2 , @AllanBenvin_yyc

We do support publishing/sharing layer joined/related to other tables (from different workspaces) aka in-memory joins/relates as a service in enterprise. You need to publish that as a map service or map image layer. While consuming this in a web app, you can add that as a map image layer or a feature layer.

With map image layers, drawing happens on the server side. Whereas with feature layer, features are drawn on the client side. You should still be able to access attributes, performs queries etc.

You are right that we don't support sharing this type of data as 'feature service' (or feature layer) that enables edit operations. I don't see you mention any editing scenarios, therefore you'd not need to have feature service capability turned on.

Please let me know if I misunderstood your use cases and workflows.

I hope this helps. Thanks

Tanu

Re: Publish services using ArcGIS Pro with Joined Data

ElaineBryant — Mon, 18 Sep 2023 14:09:18 GMT

I tried published a join as a map image layer and it does NOT work.

Re: Publish services using ArcGIS Pro with Joined Data

TanuHoque — Mon, 18 Sep 2023 20:49:51 GMT

@ElaineBryant

It should as I do it all the time. There must be something that I'm missing. If possible, please send me data and steps that you went thru to help us reproduce in house. Or, reach out to Esri Support and an analyst would help you.

thanks

Re: Publish services using ArcGIS Pro with Joined Data

ElaineBryant — Tue, 19 Sep 2023 14:47:28 GMT

@TanuHoque

I may have to reach out to Esri Support on this. For all I know the combination of software versions could be part of the issue. The join is happening between two different databases on two different servers. While the join happens fine in ArcGIS Pro, it gets messed up as a map service. Basically all the data from the non-spatial table that is part of the join gets removed. As a temporary workaround, we have done the join at the database level in the form of a view; however we can't register that view so I was unable to get it to display in the proper place in ArcGIS Pro unless I added another layer first. But when publishing it didn't turn out correct in the service. However, I could do it in ArcMap because it'll let you assign a coordinate system when it is unknown, so I published it from ArcMap and other than when you first load the data and it zooms in on Africa, it works. But we will need to get to the bottom of why the join doesn't work.

Re: Publish services using ArcGIS Pro with Joined Data

AllanBenvin_yyc — Thu, 21 Sep 2023 15:14:47 GMT

So I am more confused than ever now. I created an aprx in 3.1.1 with a point layer and a stand-alone table. I added a relate in Pro between the two and published it both as a map service and as a feature service by reference to our portal. There were no warnings as I think there were with older versions of Pro.

In portal, using the Classic Map viewer I can do an identify a point and the "Show Related Records" link at the bottom will then open the related records quite nicely. If I use the new map viewer there is not "Show Related Records" link in the popup. Perhaps this will come with time.

In short it appears that publishing a relate on a feature service now works better than before despite the article that says this functionality will be removed. ( 24040: In-memory relates are not supported—ArcGIS Pro | Documentation.

It also does seem to work with a feature service but in my particular case I am not editing. Being able to edit associated records could be done via Survey123 if a custom app was built.

I would really like to see this functionality remain and be added to the new map viewer.

PS: In my case both the point layer and table are in the same database. I'm not sure if this makes a difference.

Re: Publish services using ArcGIS Pro with Joined Data

denniszammarchi — Wed, 24 Apr 2024 13:24:50 GMT

Hello everyone, we noticed that from Rest some fields, after the join, had a suffix "_n". We fixed it by removing the table from the Contents Pane and doing the Join with right click and selecting the table directly from the DB connection of a SQL Server not registered as gdb

Re: Publish services using ArcGIS Pro with Joined Data

ScottAndersonMississauga — Wed, 05 Jun 2024 18:57:33 GMT

So to continue on this, I am able to join a feature layer in an Enterprise database to a standalone SQL table in a separate SQL Server instance. If I publish this to Portal, I am able to see the field names in the pop-up configuration tool but the data are unable to be located when clicking on one of the features that should have joined data. I understand this may not be a supported function.

My question is, how do you join to live data from another data source instance? If the answer is batch updating those data tables into Enterprise I'm not sure that is a satisfactory answer.

Re: Publish services using ArcGIS Pro with Joined Data

ScottAndersonMississauga — Wed, 05 Jun 2024 19:44:18 GMT

Ok there was a very good clue in @denniszammarchi post. If I add the standalone table to the Map in ArcPro, it renames the fields with a % sign and this destroys the functionality of the pop-up in webmaps. However, if I do the Join and select the table from the original data source via the Join dialog, not the Current Map, it does NOT rename the fields. When they are published to Portal they remain as designed and the pop-up show the data correctly.

This is an important distinction and should be more clear. Or I need more seat-time in ArcPro..

Thank you @denniszammarchi

Re: Publish services using ArcGIS Pro with Joined Data

TanuHoque — Wed, 05 Jun 2024 20:00:59 GMT

@ScottAndersonMississauga

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?

If so, it was because of a a bug we had and that is fixed in 11.3 that just got released.

Re: Publish services using ArcGIS Pro with Joined Data

TanuHoque — Wed, 05 Jun 2024 20:15:48 GMT

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

Re: Publish services using ArcGIS Pro with Joined Data

ScottAndersonMississauga — Thu, 06 Jun 2024 01:20:40 GMT

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.

Re: Publish services using ArcGIS Pro with Joined Data

TanuHoque — Thu, 06 Jun 2024 01:42:28 GMT

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

Re: Publish services using ArcGIS Pro with Joined Data

ScottAndersonMississauga — Thu, 06 Jun 2024 13:54:27 GMT

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.

Re: Publish services using ArcGIS Pro with Joined Data

AndreaB_ — Tue, 17 Dec 2024 19:47:05 GMT

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.

Re: Publish services using ArcGIS Pro with Joined Data

TanuHoque — Tue, 17 Dec 2024 20:38:36 GMT

@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?

Re: Publish services using ArcGIS Pro with Joined Data

AndreaB_ — Tue, 17 Dec 2024 20:47:36 GMT

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

Re: Publish services using ArcGIS Pro with Joined Data

TanuHoque — Tue, 17 Dec 2024 21:45:55 GMT

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

You can add that as a map image layer, and/or
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?

Re: Publish services using ArcGIS Pro with Joined Data

AndreaB_ — Mon, 23 Dec 2024 21:37:01 GMT

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