ArcGIS Pro 2.9.5; Oracle 18c 10.7.1 eGDB:
My unit has recently completed a months-long project that involved extensive tabular analysis of our EGDB data. The analysis involved complex queries, including non-spatial joins.
Ultimately, my coworkers found they couldn't use ArcGIS Pro for the analysis and ended up using Excel as a temporary workaround. Now that the project is complete, they've asked the following question:
Going forward, is ArcGIS Pro the right tool for tabular/join-based analysis of our enterprise geodatabase data?
Based on the issues listed below, we are coming to the conclusion that the answer is, unfortunately, no.
- Select By Attributes on joined data: For rows that are 1:M, all rows in the join table get selected, despite the selection SQL expression.
- BUG-000154798
- No virtual attribute functionality
- Whereas, tools like Excel can have real-time calculated fields (formulas) in true Excel tables, in Power Query, etc. for ad hoc analysis.
- Trust issues with query layers: Query Layers omit rows from the underlying query without any warning.
- Here is a specific example, but there are other cases too: Query Layers — Include features with null shape in attribute table
- Slow joins to query layers
- BUG-000160320
- Esri Case: 03356933
- Definition query on join table will effectively result in an inner join
- Bug/Article-000007166, BUG-000153808, BUG-000131999, BUG-000129607, BUG-000153950, BUG-000164109
- Fixed in 3.x?
- Definition query on join returns incorrect rows
- Export join to FGDB: Join table ID field is null
- Esri Case: 03616469
- BUG-000146098
- Join with definition query: Export has more rows than input (definition query is ignored)
- BUG-000135505
- Esri Case: 03619440
- Can't sort on join table field in attribute table
- BUG-000157874
- Esri Case: 03616093
- Can’t create 1:M join using unregistered tables, query layers, and database views
- It’s not always clear why a join is one-to-first vs. one-to-many.
- There are likely a few other issues that I’ve missed.
It's unfortunate that we feel Pro isn't the right tool for tabular analysis of geodatabase data. We've been die-hard ArcGIS Pro users up to this point. But ultimately, users have developed trust issues and have come up against too many walls when doing tabular analysis in Pro.
What tools do experienced practitioners use for complex tabular analysis of enterprise geodatabase data?
The current use case is: analysis of construction project GIS data, with no room for error. And many other use cases.