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.

[AllanBenvin_yyc]

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.

[TanuHoque]

@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

[ElaineBryant]

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

[TanuHoque]

@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

[ElaineBryant]

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

[AllanBenvin_yyc]

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.

[denniszammarchi]

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

[ScottAndersonMississauga]

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.

[ScottAndersonMississauga]

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 

[TanuHoque]

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