Branch versioning and SQL Views

[DarrenHaag]

We are exploring the viability of using branch versioning.  Our organization utilizes SQL views for much of what we serve out to our portal as enrichment services.  Looking over the structure of how the branch versioned tables are built, it appears we won't be able to call just a generic select statement to pull data from the base table, but will have to incorporate some heftier sql just to get the correct rows to return.  Is this something that has been addressed and I've just missed it?  Or does ESRI have a sample of the code needed to pull the current state of features in the default version?

We'd love to explore branch versioning, but if it removes support for database views (or over complicates them), it would be a real buzzkill.

[MelissaJarman]

Darren - 

Branch versioning follows a services based implementation pattern and there are not currently any plans for direct SQL access. As you may have noticed, versioned views are not created for branch versioned datasets. REST calls can be used for these workflows to query and edit within specific branch versions. Have you attempted to query within REST using the Query (Feature Service/Layer) operation?

[DarrenHaag]

Thank you for the reply Melissa.

What we do for some of our services is create a view pulling data from multiple databases/tables etc..  register it with our sde database and then publish that out as a service.  For example:  we have street gis features.  In our asset management software we have records of work performed, inspections, etc...  instead of requiring people to perform processing or maintain joins, we create views.  In that example it may be how many times have we fixed potholes on a street segment in the last 2 years and when did we last do it.  With a view, this is always accurate to the data in both systems and available to everyone in our city via our internal portal.

I hope that makes sense.

[Rubén_MoisésAfonso_Romero]

In my organization we have the same problem. We have many SQL views that combine different database tables. We have created queries that extract the data from the current state of each element but they are not very efficient queries that come to paralyze the applications. The problem would be solved if we knew how ArcGIS Pro performs the queries to fetch only the current state of each table.

[DasheEbra]

hi Darren, you can try the following statement i think it's usefull.

SELECT Objectid, shape
FROM Buildings
WHERE Buildings.GDB_ARCHIVE_OID IN
  (Select MB_.GDB_ARCHIVE_OID
   From
     (SELECT GDB_ARCHIVE_OID,
         ROW_NUMBER() OVER (PARTITION BY OBJECTID
         ORDER BY gdb_from_date DESC) rn, gdb_is_delete
      FROM Buildings
	  WHERE (gdb_branch_id = 0 AND
              gdb_from_date <= '12.31.9999 23:59:59.000')) MB_
    WHERE rn = 1 AND gdb_is_delete = '0' ); 

[LeilanieLopez]

This worked for me! Thank you!

[philippenn]

Same issue here. We have a lot of reports based on versioned views. For reasons of performance and avoiding rewrite, we see a reasonable use case for maintaining versioned views in branched versioning.
We would like the creation of EVWs and the equivalent of sde.version_util.set_current_version() for branched versioning versions.
I realize this is not that tricky to implement now there are no more state_lineages to deal with, but I would think it's in ESRI's interest to make sure we're doing it correctly, consistently and in a performant way, whether through product or sample code on github.

[Anonymous User]

I am also in need of a way to do this. Specifically, we like to create Query Layers based on joins from related records directly from SQL. This creates several joins per record for assets that have been updated via branch versioning. 

I'm not opposed to using the services but I need an easy way to join data that is referenced from a database and not hosted. I know that Portal has the Join Features analysis tool but it requires both tables to be hosted.

[JTLancer]

Our organization also uses similar methods with SQL views that other users have described.  I am interested in branch versioning particularly because it is a requirement of Utility Networks.  I did find the following article discussing methods to access bv datasets. I haven't had a chance to test out as we are not currently using bv, but wanted to get in on this thread to keep in the loop.

https://www.esri.com/arcgis-blog/products/utility-network/electric-gas/utility-network-sql/

[Johan-Terblans]

You can build your own views , example SQL above is a good example.
Include the following for each table in where clause you want to query, to get the latest record in DEFAULT for a row.
SQL Server:
WHERE GDB_ARCHIVE_OID IN (SELECT GDB_ARCHIVE_OID
FROM (SELECT GDB_ARCHIVE_OID, ROW_NUMBER() OVER (PARTITION BY OBJECTID
ORDER BY GDB_FROM_DATE DESC) rn_, GDB_IS_DELETE
FROM <My Table>
WHERE GDB_BRANCH_ID = 0) bv1
WHERE bv1.rn_ = 1 AND bv1.GDB_IS_DELETE = 0)