I recently made the switch to branch versioning and am now stumbling over the lack of versioned views (like the automatically created Featureclass_evw views for traditionally versioning).
I have a typical relational database (SQL Server), mostly in third normal form. Of course, users of my maps don't care how the data is structured behind the scenes, they want to have all the info for an object in one place. So my workflow up to now has been to create database views that take care of all the joins and then publish these views to our Portal and consume them in my web maps.
Now I have branch versioning in place, published the raw feature classes and tables to the Portal, users can use those to edit the database, cool.
But now my database views are all messed up.
With traditional versioning, I would use the automatically provided versioned views like this:
SELECT t1.OBJECTID, t1.Shape, t2.Field
FROM database.dataowner.Table1_evw t1
LEFT JOIN database.dataowner.Table2_evw t2 ON t1.Key = t2.Key
If I use that approach with branch versioned tables (without the _evw), the whole archive of both tables is pulled into the database view. If I edit a feature in Table1 and edit a related row in Table2 two times, I will get 6 output rows for the same feature. If I move the feature, it will have both the old and the new location in the view. If I delete it, it will still be shown in the view.
I can circumvent that by creating versioned views for each table myself and then use those views for the other database views:
/*Table1_evw*/
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY OBJECTID ORDER BY GDB_FROM_DATE DESC) AS rank
FROM database.dataowner.Table1
) AS ranked_table
WHERE rank = 1 AND GDB_IS_DELETE = 0
But this seems clunky and error prone and is probably quite computationally wasteful.
And ArcGIS Pro already does this: If you look at a branch versioned table, it shows the current state of the table in the selected version, and it hides all the versioning fields. All "Add Archive" seems to do is add the raw table to the map.
So, my suggestion: Give us automatically created versioned views for branch versioned tables. These would show the current state of the selected version, and they would be addressable by SQL.
