"}],"short_description":"This article will show you how you can write sql queries that can read data from the default version in a branch versioned Esri geodatabase.","flexible_content":[{"acf_fc_layout":"content","content":"Update<\/strong>: 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<\/a><\/strong> to improve the usability and maintainability of the sql statements.<\/p>\n 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.<\/p>\n 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\u2019t integrate with services or lack the resources internally to replace interfaces and reports that make use of sql.<\/p>\n 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.<\/p>\n"},{"acf_fc_layout":"content","content":" Branch versioning<\/a> 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\u2019s so important, I encourage you to read the following articles:<\/p>\n 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.<\/p>\n Esri\u2019s 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\u2019s look at how we can read data in the default version of the geodatabase.<\/p>\n"},{"acf_fc_layout":"content","content":" Whenever you\u2019re 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\u2019m 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.<\/p>\n 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!<\/p>\n 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<\/a><\/strong> 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<\/a> technical session from the 2021 Esri User Conference to create materialized views for several of the supported databases<\/a> for the utility network<\/p>\n"},{"acf_fc_layout":"content","content":" 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.<\/p>\n 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.<\/p>\n Now that you know how to create views for branch versioned data, let\u2019s 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.<\/p>\n 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.<\/p>\n"},{"acf_fc_layout":"sidebar","content":" The first basic technique of using these views is to filter your results. This technique is useful for when you are trying to focus your report on a specific type of feature or if you have a very large dataset that you need to break into manageable pieces.<\/p>\n","image_reference":false,"layout":"code_snippet","image_reference_figure":"","snippet":"SELECT\r\n *\r\nFROM\r\n Device_BVW\r\nWHERE\r\n ASSETGROUP = 5;","spotlight_name":"","section_title":"","position":"Center","spotlight_image":false},{"acf_fc_layout":"sidebar","content":" Another common requirement for reports from the GIS is to join the information in the GIS with tabular data from other systems. In the example below we have a materialized view of our customers in the GIS and we want to generating a report of the number of customers associated with each device.<\/p>\n","image_reference":false,"layout":"code_snippet","image_reference_figure":"","snippet":"SELECT\r\n Device.SubnetworkName, Device.AssetID, Count(1)\r\nFROM\r\n Device_BVW Device, Customer_Info\r\nWHERE\r\n Device.SPID = Customer_Info.SPID\r\nGROUP BY\r\n Device.GlobalID\r\nORDER BY\r\n Count(Customer.SPID) DESC","spotlight_name":"","section_title":"","position":"Center","spotlight_image":false},{"acf_fc_layout":"sidebar","content":" 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.<\/p>\n","image_reference":false,"layout":"code_snippet","image_reference_figure":"","snippet":"SELECT\r\n Station.Name, Device.AssetID\r\nFROM\r\n StructureBoundary_BVW Station, Device_BVW Device, Associations_BVW Associations\r\nWHERE\r\n Station.AssetGroup = 1 AND\r\n Station.GlobalID = Associations.FromGlobalID AND\r\n Device.GlobalID = Associations.ToGlobalID","spotlight_name":"","section_title":"","position":"Center","spotlight_image":false},{"acf_fc_layout":"sidebar","content":" 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.<\/p>\n","image_reference":false,"layout":"code_snippet","image_reference_figure":"","snippet":"SELECT\r\n Structure.AssetID, Assembly.AssetID, Device.AssetID\r\nFROM\r\n Structure_BVW Structure,\r\n Assembly_BVW Assembly,\r\n Device_BVW Device,\r\n Associations_BVW Attachment,\r\n Associations_BVW Containment\r\nWHERE\r\n Structure.GlobalID = Attachment.FromGlobalID AND\r\n Assembly.GlobalID = Attachment.ToGlobalID AND\r\n Assembly.GlobalID = Containment.FromGlobalID AND\r\n Device.GlobalID = Containment.ToGlobalID","spotlight_name":"","section_title":"","position":"Center","spotlight_image":false},{"acf_fc_layout":"content","content":" Now that you\u2019ve 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.<\/p>\n 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!<\/p>\n"}],"related_articles":[{"ID":1532622,"post_author":"304562","post_date":"2022-05-09 08:00:33","post_date_gmt":"2022-05-09 15:00:33","post_content":"","post_title":"Journey to the Utility Network: Integrations Overview","post_excerpt":"","post_status":"publish","comment_status":"closed","ping_status":"closed","post_password":"","post_name":"utility-network-integrations-overview","to_ping":"","pinged":"","post_modified":"2025-07-22 16:32:52","post_modified_gmt":"2025-07-22 23:32:52","post_content_filtered":"","post_parent":0,"guid":"https:\/\/www.esri.com\/arcgis-blog\/?post_type=blog&p=1532622","menu_order":0,"post_type":"blog","post_mime_type":"","comment_count":"2","filter":"raw"},{"ID":1540522,"post_author":"304562","post_date":"2022-05-23 07:00:41","post_date_gmt":"2022-05-23 14:00:41","post_content":"","post_title":"Journey to the Utility Network: Common Integrations","post_excerpt":"","post_status":"publish","comment_status":"closed","ping_status":"closed","post_password":"","post_name":"utility-network-integrations-non-network","to_ping":"","pinged":"","post_modified":"2025-12-31 05:42:35","post_modified_gmt":"2025-12-31 13:42:35","post_content_filtered":"","post_parent":0,"guid":"https:\/\/www.esri.com\/arcgis-blog\/?post_type=blog&p=1540522","menu_order":0,"post_type":"blog","post_mime_type":"","comment_count":"0","filter":"raw"},{"ID":2900672,"post_author":"304562","post_date":"2025-05-04 07:48:49","post_date_gmt":"2025-05-04 14:48:49","post_content":"","post_title":"ArcGIS Utility Network migration reading list","post_excerpt":"","post_status":"publish","comment_status":"closed","ping_status":"closed","post_password":"","post_name":"arcgis-utility-network-migration-reading-list","to_ping":"","pinged":"","post_modified":"2026-01-06 08:50:47","post_modified_gmt":"2026-01-06 16:50:47","post_content_filtered":"","post_parent":0,"guid":"https:\/\/www.esri.com\/arcgis-blog\/?post_type=blog&p=2900672","menu_order":0,"post_type":"blog","post_mime_type":"","comment_count":"0","filter":"raw"},{"ID":1527822,"post_author":"304562","post_date":"2022-04-25 08:00:41","post_date_gmt":"2022-04-25 15:00:41","post_content":"","post_title":"Journey to the Utility Network: First Steps","post_excerpt":"","post_status":"publish","comment_status":"closed","ping_status":"closed","post_password":"","post_name":"starting-your-utility-network-journey","to_ping":"","pinged":"","post_modified":"2025-07-22 16:33:20","post_modified_gmt":"2025-07-22 23:33:20","post_content_filtered":"","post_parent":0,"guid":"https:\/\/www.esri.com\/arcgis-blog\/?post_type=blog&p=1527822","menu_order":0,"post_type":"blog","post_mime_type":"","comment_count":"0","filter":"raw"},{"ID":2748552,"post_author":"9432","post_date":"2025-07-21 14:00:15","post_date_gmt":"2025-07-21 21:00:15","post_content":"","post_title":"ArcGIS for Utilities \u2013 Enterprise GIS for Modern Network Information Management","post_excerpt":"","post_status":"publish","comment_status":"closed","ping_status":"closed","post_password":"","post_name":"arcgis-for-utilities-enterprise-gis-for-modern-network-information-management","to_ping":"","pinged":"","post_modified":"2025-09-10 09:33:34","post_modified_gmt":"2025-09-10 16:33:34","post_content_filtered":"","post_parent":0,"guid":"https:\/\/www.esri.com\/arcgis-blog\/?post_type=blog&p=2748552","menu_order":0,"post_type":"blog","post_mime_type":"","comment_count":"0","filter":"raw"}],"card_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/03\/cardFINAL-1.png","wide_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/03\/BannerFINAL.png","show_article_image":false},"yoast_head":"\nBranch Versioning<\/h1>\n"},{"acf_fc_layout":"image","image":{"ID":1656962,"id":1656962,"title":"branch_versioning","filename":"branch_versioning.png","filesize":24709,"url":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/branch_versioning.png","link":"https:\/\/www.esri.com\/arcgis-blog\/products\/utility-network\/electric-gas\/utility-network-sql\/branch_versioning","alt":"Branch Versioning","author":"304562","description":"Branch Versioning","caption":"Branch Versioning","name":"branch_versioning","status":"inherit","uploaded_to":1656742,"date":"2022-07-07 16:19:38","modified":"2022-07-07 16:19:50","menu_order":0,"mime_type":"image\/png","type":"image","subtype":"png","icon":"https:\/\/www.esri.com\/arcgis-blog\/wp-includes\/images\/media\/default.png","width":640,"height":451,"sizes":{"thumbnail":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/branch_versioning-213x200.png","thumbnail-width":213,"thumbnail-height":200,"medium":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/branch_versioning.png","medium-width":370,"medium-height":261,"medium_large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/branch_versioning.png","medium_large-width":640,"medium_large-height":451,"large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/branch_versioning.png","large-width":640,"large-height":451,"1536x1536":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/branch_versioning.png","1536x1536-width":640,"1536x1536-height":451,"2048x2048":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/branch_versioning.png","2048x2048-width":640,"2048x2048-height":451,"card_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/branch_versioning.png","card_image-width":640,"card_image-height":451,"wide_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/branch_versioning.png","wide_image-width":640,"wide_image-height":451}},"image_position":"center","orientation":"horizontal","hyperlink":""},{"acf_fc_layout":"content","content":"
\n
Views<\/h1>\n"},{"acf_fc_layout":"image","image":{"ID":1657022,"id":1657022,"title":"power_bi_model","filename":"power_bi_model.png","filesize":20059,"url":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_model.png","link":"https:\/\/www.esri.com\/arcgis-blog\/products\/utility-network\/electric-gas\/utility-network-sql\/power_bi_model","alt":"Database Views","author":"304562","description":"Database Views","caption":"Database Views","name":"power_bi_model","status":"inherit","uploaded_to":1656742,"date":"2022-07-07 16:57:06","modified":"2022-07-07 16:57:20","menu_order":0,"mime_type":"image\/png","type":"image","subtype":"png","icon":"https:\/\/www.esri.com\/arcgis-blog\/wp-includes\/images\/media\/default.png","width":1043,"height":546,"sizes":{"thumbnail":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_model-213x200.png","thumbnail-width":213,"thumbnail-height":200,"medium":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_model.png","medium-width":464,"medium-height":243,"medium_large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_model.png","medium_large-width":768,"medium_large-height":402,"large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_model.png","large-width":1043,"large-height":546,"1536x1536":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_model.png","1536x1536-width":1043,"1536x1536-height":546,"2048x2048":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_model.png","2048x2048-width":1043,"2048x2048-height":546,"card_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_model-826x432.png","card_image-width":826,"card_image-height":432,"wide_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_model.png","wide_image-width":1043,"wide_image-height":546}},"image_position":"center","orientation":"horizontal","hyperlink":""},{"acf_fc_layout":"content","content":"
Reports<\/h1>\n"},{"acf_fc_layout":"image","image":{"ID":1657352,"id":1657352,"title":"power_bi_report","filename":"power_bi_report.png","filesize":47910,"url":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_report.png","link":"https:\/\/www.esri.com\/arcgis-blog\/products\/utility-network\/electric-gas\/utility-network-sql\/power_bi_report","alt":"GIS Report","author":"304562","description":"GIS Report","caption":"GIS Report","name":"power_bi_report","status":"inherit","uploaded_to":1656742,"date":"2022-07-07 19:55:41","modified":"2022-07-07 19:55:57","menu_order":0,"mime_type":"image\/png","type":"image","subtype":"png","icon":"https:\/\/www.esri.com\/arcgis-blog\/wp-includes\/images\/media\/default.png","width":1329,"height":747,"sizes":{"thumbnail":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_report-213x200.png","thumbnail-width":213,"thumbnail-height":200,"medium":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_report.png","medium-width":464,"medium-height":261,"medium_large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_report.png","medium_large-width":768,"medium_large-height":432,"large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_report.png","large-width":1329,"large-height":747,"1536x1536":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_report.png","1536x1536-width":1329,"1536x1536-height":747,"2048x2048":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_report.png","2048x2048-width":1329,"2048x2048-height":747,"card_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_report-826x465.png","card_image-width":826,"card_image-height":465,"wide_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2022\/07\/power_bi_report.png","wide_image-width":1329,"wide_image-height":747}},"image_position":"center","orientation":"horizontal","hyperlink":""},{"acf_fc_layout":"content","content":"
Filtering<\/h3>\n
Joining and Aggregating Data<\/h3>\n
Associations<\/h3>\n
Multiple Associations<\/h3>\n
Conclusion<\/h1>\n