SQL Views and Branch Versioning
Branch Versioning
ArcGIS Pipeline Referencing (APR) at launch was based on traditional versioning geodatabase transaction model. However, as one of the next generation ArcGIS applications, APR added compatibility with branch versioning at ArcGIS Enterprise 10.6.1 with ArcGIS Pro 2.2. At subsequent releases, newer capabilities like services-based editing, Utility Network integration and conflict prevention require branch versioning. APR with branch versioning is the recommended and preferred implementation pattern for pipeline organizations.
- Branch versioning follows a services-based architecture for viewing and editing data. It facilitates long database transactions using feature services, taking advantage of ArcGIS Server's Version Management service.
- Direct SQL access to the relational database is not supported or recommended with this geodatabase transaction model.
- Database Views are more applicable to traditional versioning and data level joins or integrations. In the absence of multi-versioned views, updates/insert/deletes using SQL should not be applied on feature classes that have been branch versioned. This can cause data corruption and other unexpected behavior within ArcGIS applications.
- Create Database View geoprocessing tool will accept branch versioned GDB as input workspace. However, the tool is not branch version aware.
Design Considerations
Users see opportunities in this changed services-based landscape that offer numerous benefits. But fundamental tasks like generating reports and database integration interfaces are key to running business processes. Solution architects and GIS Managers have carefully re-assessed the business needs and worked their way using a mix of the newer design patterns. Refer to Utility Network Journey: Branch Versioning and SQL by @RobertKrisher for additional insights.
- REST API: The supported method to query branch versioned data is through the REST API for e.g. Query (Feature Service/Layer), where appropriate and meets the need. Specific methods have been developed in REST and ArcGIS clients to ensure data integrity is in place when accessing the branch versioned data.
- Python Scripting: The ArcGIS API for Python is branch version aware and scripts can be used to generate reports from branch versioned geodatabase. The scripts can be run as scheduled jobs, often during non-business hours to compile data reports.
- Non-versioned Publication Geodatabase: Required datasets can be replicated to a non-versioned publication geodatabase that can be used for reporting, SQL views, integration etc. Note that there is an overhead of the replication process and maintaining a secondary geodatabase copy. So, a careful design is recommended based on the layer requirements, frequency of replication, delta changes and overall cost benefit analysis.
- SQL Views: Additional SQL logic may be used for view definition (read-only) access to branch versioned data. The document Branch Version SQL Views presents several samples:
- Create Materialized Views and refresh Views
- Create Reporting Table and refresh Tables
Note that the SQL Views option, is considered secondary for legacy database level operations, and INSERT, UPDATE and DELETE functionality is not available. REST API based query is the best practice, recommended option moving forward for service-based architecture.
Parting thoughts (courtesy xkcd)
