Branch Versioning and SQL Queries

Update: This blog post was originally published on July 8, 2022 but was updated to recommend using materialized views instead of regular database views. All the SQL queries and procedures for the views in the article have been moved to a separate community page to improve the usability and maintainability of the sql statements.

In this article I want to discuss a question I get asked about frequently: How do I write sql queries against branch versioned data like the utility network dataset? The reason this is such a common question is because branch versioned only supports access data via web services and many customers are used to directly accessing their database using sql queries.

The new services-based architecture provides many opportunities to create engaging, meaningful experiences for your end users. The challenge many customers are up against is that they are often support legacy systems that can’t integrate with services or lack the resources internally to replace interfaces and reports that make use of sql.

In this article, we will discuss what branch versioning is, techniques you can use to safely query this data using sql, as well as several examples of how to build reports that make use of these techniques.

Branch Versioning

Branch versioning is a new type of versioning introduced by Esri to support a services-based architecture for viewing and editing data. If you want to have a better understanding of what branch versioning is and why it’s so important, I encourage you to read the following articles:

1. To Branch or not to branchis a blog post written by Esri that serves as a good introduction to Branch Versioning for less technical users
2. Versioning typesin the online help compares the benefits and limitations of branch versioning as well as traditional versioning
3. Branch versioning scenariosfrom the online help provides a detailed description of the workflows associated with branch versioning
4. Version Management with ArcGISis a recorded technical session from the 2021 Esri User Conference that provides a detailed description of the database and services model for branch versioning

The utility network dataset, parcel fabric datasets, and the trace network were built using this new architecture, allowing them to be readily accessible in all desktop, web, and mobile platforms. With the reliance on services for all viewing / editing functionality this has left many of our customers wondering how to replace existing interfaces and reports that relied on sql once they migrate to the utility network.

Esri’s recommendation is, and continues to be, that customers use the services and APIs wherever possible However, we also recognize that there are some scenarios, especially those involving other legacy systems, where an API level solution is not possible. With that in mind, let’s look at how we can read data in the default version of the geodatabase.

Views

Whenever you’re querying transactional data in a database, you always need to make sure that your queries are requesting the data for the time you are interested in. If I’m writing a query to see how much money I have in my bank account I need to make sure that I am not only looking at all of the deposits in my account, but that I am also taking into account all the withdrawals and any committed transactions. Because the transactional models for different systems vary in implementation, many systems provide stored procedures or views that make interacting with these models simpler.

Because of this decision, there are no longer system-maintained views that expose the transactional model for customers. However, it is still possible to query the database directly and get the correct result as long as your queries respect the transactional model. These queries are not a full replacement for multi-version views since they do not support editing and only show data for the current moment in the default. But for customers who are looking to create reports this is exactly what they need!

For example sql statements that show how to create a view that respects this transactional model you can refer to the Branch Version Sql Views – Utility Network page in the Esri Community page for the ArcGIS Utility Network. This post shows how you can use the queries and logic outlined in the Version Management with ArcGIS technical session from the 2021 Esri User Conference to create materialized views for several of the supported databases for the utility network

Reports

When building reports, you should use materialized views as your building blocks. This means any additional filtering or joining should be done with sql queries that reference these views instead of building separate views for every table in every report. Because materialized views are managed outside of the Esri APIs you may need to recreate some of these views as you make schema changes, so having a separation between your data access layer (views) and your reporting layer (queries) will make managing these changes easier.

If you find that some of your reports need to access data in specific versions, across versions, or need to perform spatial operations then you should strongly consider modifying your approach to make use of one of the APIs provided by Esri. This will ensure that your interface will have effective performance and correctness.

Now that you know how to create views for branch versioned data, let’s look at some examples of how to query these views. You can then refer to them as you build your own reports that reference your own views, fields, and queries.

The examples in the next section show how you can write queries using materialized views for various branch versioned tables. The reports below assume that you have created materialized views for all the branch versioned tables available in a typical Electric Utility Network Foundation deployment.

Conclusion

Now that you’ve read this article you are well equipped to create your own materialized views that let you query your branch versioned data for the default version of your database. Use the examples in this article to create your own materialized views that reference your own tables and return the fields you need.

We still recommend customers developing new solutions make use of the APIs, since they provide full access to all the functionality of the GIS. If you need to perform spatial analysis, access non-default data, or edit data you have no other option than using the new APIs. Either way you should now have a better understanding and appreciation of how branch versioning works!