Branch Versioning: Versioned views

[JohannesLindner]

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.

[jcarlson]

What would you be using the views for? Just to look at a specific version without having to actually "occupy" it?

[JohannesLindner]

No. The versioned views would show the current state of the version I'm currently connected to.

Basically, they would look exactly like what you see when you open a branch versioned feature class in ArcGIS Pro: For each ObjectID, show the entry with the most recent GDB_FROM_DATE, except when GDB_IS_DELETE = 1. Hide all the versioning fields.

ArcGIS Pro does all that, but apparently only in the application. I want exactly that behavior as an automatic, queriable view in the database.

In traditional versioning, we have the base table, the a and d tables, and the archive table. ArcGIS provides a versioned view that pieces together the base, a, and d tables. This way, you can easily query the current state with SQL for database views, without having to worry about all the versioning stuff.

SELECT * FROM TraditionallyVersionedTable_evw

In branch versioning, we have only one table that contains versioning info and the archive. Now we don't have to fuss with a and d tables, but we have to take care to remove archived features from our database views (eg using the approach in my question). I don't want to have to do that myself. ArcGIS is capable of doing it, I just want to write my database views without having to worry about all the versioning stuff.

SELECT * FROM BranchVersionedTable_evw

[RobertKrisher]

Back to the original question, what is the purpose of these views? I'm seeing several workflows listed in your post.

1. Do you want to present data to your end users for viewing / reporting purposes? This is something that we can talk through some best practices on.
2. If you're looking to edits views of your branch versioned data, this is not supported and quite dangerous. The reason why we don't allow this is because there are constraints and internal dependencies maintained by the feature services that we enforce as you edit the data. This is even more important if you have controller datasets like a utility network, trace network, or parcel fabric as you will likely end up placing your controlled datasets in an inconsistent or corrupt state.

[jcarlson]

I can confirm the "dangerous" part! In the early days of branch versioning, we had a number of Query Layers and Views that we configured for some legacy purposes, and it ended up corrupting one of our more important layers. Thank goodness for backups!

[JohannesLindner]

I just want the views to present data.

For example: I have a feature class "Buildings" which stores information about buildings. I have a table "Contacts" which stores contact information. Buildings store the key to the contact information of their maintainers.

In my map, I want to show the contact info in the building's popup. So I create a database view:

SELECT b.GDB_ARCHIVE_OID, b.Shape, b.SomeAttribute, c.Name, c.Telephone
FROM db.do.Buildings b
LEFT JOIN db.do.Contacts c
ON b.ContactID = c.ContactID

This works fine for unedited buildings and contacts. But let's say I edit a contact. Because a branch versioned table contains its own archive, the same ContactID is now in the table twice. The database view now contains two objects at the same position: one with the old contact information, one with the new. If I delete the building, the view will still show it. Basically, I see the whole archive of Buildings and Contacts for this object.

I can circumvent that by selecting the most current entry for each OBJCETID in each table:

SELECT b.GDB_ARCHIVE_OID, b.Shape, b.SomeAttribute, c.Name, c.Telephone
FROM (
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY OBJECTID ORDER BY GDB_FROM_DATE DESC) AS rank
        FROM db.do.Buildings
    ) ranked_buildings
    WHERE rank = 1 AND GDB_IS_DELETE = 0
) b
LEFT JOIN (
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY OBJECTID ORDER BY GDB_FROM_DATE DESC) AS rank
        FROM db.do.Contacts
    ) ranked_contacts
    WHERE rank = 1 AND GDB_IS_DELETE = 0
) c
ON b.ContactID = c.ContactID

This is difficult to read, difficult to write, error prone, and probably inefficient. Especially as I often join multiple tables.

And it seems like this could be dome automatically by the geodatabase, analogous to the versioned views in traditional versioning.

From a user's perspective, I want to be able to use the first query, just with Buildings_evw instead of Buildings.

[RobertKrisher]

Before we talk about using views, let's discuss some alternatives:

1. Arcade Popup
   1. Open your popup configuration
   2. Write an arcade expression in your to retrieves the contact information
   3. This value will appear in your field list
   4.  
2. Publish a Web Map
   1. You could take your existing layers from your feature service
   2. join them using the "Add Join" tool
   3. Share this web map

There are current no plans to provide views for branch versioned data. So if you are going to take this approach you will need to be careful in how you construct your views. If you do go down this route you should consider the following:

If the views will be used heavily, consider materializing your views and creating indices purpose built for your queries

Instead of creating a single view for each use case, create a materialized view for each branch versioned object.  You can then construct purpose built views views on top of these materialized views.

[MichaelOlkin1]

I agree that for any relatively current Esri technology, there are sufficient alternatives to versioned views. The ongoing value of versioned views is that they provide a SQL native way to integrate GIS data with other systems. It would be tremendously helpful for Esri to provide a geoprocessing tool that could be used to create or update a versioned view for a branch versioned feature class. In absence of this, the approach described here appears to be helpful.