Branch Version Sql Views - Utility Network

[PierreloupDucroix]

Hi, @RobertKrisher can you elaborate a bit more on materialized views VS regular views ? I cannot find information about performance assessment or the use of views for data access.

Ex : I want to join rows in the electricJunctionObjects with their associated features so I can create a view that allows me to zoom to each object.

Should I create a standard or a materialized view with a JOIN, or create separate materialized views for electricJunctionObject and electricDevice first, then create a JOIN view against them ?

[RobertKrisher]

This thread is in response to this article: https://community.esri.com/t5/arcgis-utility-network-questions/re-branch-version-sql-views-utility-n...

Materialized views aren't available on all platforms, so in some cases, you would be writing the contents of the branch version analytics to a table for reporting purposes. For the sake of this comment, I'll just assume you're using a materialized view.

I would typically create a materialized view on the separate tables first. That way when I need to create a second or third report using any of those tables I can just use the tables I've already materialized.

Creating a materialized view that contains a join for those tables will probably take longer than materializing the tables separately, especially once you bring the associations table into the mix. If this is a one-off, and the performance is better this way, then go ahead. But as soon as you need to make a second or third materialized view you will likely find the approach outlined in the article will be more scalable.

[PierreloupDucroix]

Interesting! So the first step is to create materialized views for all the tables we'll use for analysis or export purposes, and to create a refresh function.

In addition to the join view, we need to create a view on the association table for exports, but based on your message, it would be more appropriate to also create a materialized view for this table and refresh it just before exporting.

Similarly, for visualization purposes (e.g., a dashboard with equipment symbolized by its last maintenance status), we could consider a standard view based on the materialized views of equipment and maintenance.

Thanks

[RobertKrisher]

That all seems reasonable.