ArcGIS Utility Network

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:

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
Versioning typesin the online help compares the benefits and limitations of branch versioning as well as traditional versioning
Branch versioning scenariosfrom the online help provides a detailed description of the workflows associated with branch versioning
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.

Associations

One of the most common requests we have is how to query the associations table to analyze the relationships between equipment and structures. Once you create a materialized view for the associations table the approach is like joining any other relationship table. You can then use this table to analyze the containment, structural attachment, and explicit connectivity between network features. In the query below we are creating a report that lists out the names of each device and the substation it belongs to.

SELECT
  Station.Name, Device.AssetID
FROM
  StructureBoundary_BVW Station, Device_BVW Device, Associations_BVW Associations
WHERE
  Station.AssetGroup = 1 AND
  Station.GlobalID = Associations.FromGlobalID AND
  Device.GlobalID = Associations.ToGlobalID

Multiple Associations

Many customer data models need to traverse multiple relationships to produce reports. The most common example of this is to traverse the relationship between structures and assemblies to identify where key pieces of devices are located. In the example below we must traverse the associations table multiple times, first to get all the assemblies attached to structures and then a second time to get all the devices contained in those assemblies. You should exercise some caution with this approach when dealing with large datasets since this may result in hundreds of thousands or millions of combinations. In those cases you should consider breaking up your results using another attribute like asset group, subnetwork, or other characteristic that is important to your organization.

SELECT
  Structure.AssetID, Assembly.AssetID, Device.AssetID
FROM
  Structure_BVW Structure,
  Assembly_BVW Assembly,
  Device_BVW Device,
  Associations_BVW Attachment,
  Associations_BVW Containment
WHERE
  Structure.GlobalID = Attachment.FromGlobalID AND
  Assembly.GlobalID = Attachment.ToGlobalID AND
  Assembly.GlobalID = Containment.FromGlobalID AND
  Device.GlobalID = Containment.ToGlobalID

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!

About the author

Robert Krisher

Robert Krisher is a Product Engineer with Esri who has over 15 years of experience implementing Enterprise GIS for Utilities.

Connect:

Commenting is not enabled for this article.

ArcGIS Production Mapping and ArcGIS Defense Mapping: Getting Started with Generalization

Read this article

Utility Network Journey: Branch Versioning and SQL