Over the years we’ve constantly run into a challenge with Esri utility customers who need to get to ‘State 0’ to perform some geodatabase operation. In a past article we documented the geodatabase operations that can be done with versions in place and those that require you to get to State 0 with an un-versioned geodatabase.
For those readers who aren’t familiar with the concept of State 0, this basically means getting rid of all of your Esri versions, compressing th
e geodatabase to move your edits from the Add & Delete tables to the base tables, and then most often un-versioning your geodatabase which completely removes the Add & Delete tables. If you’re asking yourself what the Add & Delete tables are in the first place, we’ve got a great article series for you on Esri Versioning for Dummies.
The main pain-point for utilities arises because we are so focused on geometric networks and long transactions.
Our geometric networks exist to manage our electric, gas, water, or telecom connectivity and we need those long transactions to allow us to create design versions for our proposed network changes – this is common at all utilities but is especially prevalent if you are a Schneider Electric (SE) Designer™ user where versions might be outstanding for months or even years in some cases.
So when you occasionally get to a point where you have to un-version your geodatabase most utilities are in the ‘oh crap that’s not happening anytime soon‘ mindset. Some of our largest customers have more than 6,000 versions at any given time while even most smaller Designer™ sites usually have over 100 versions they can’t get rid of.
So therein lies our challenge.
Is there a way for us to get to State 0 with an un-versioned geodatabase without losing all those versions?
If you follow our newsletter/blog you may have read Dennise Ramirez’s post called Show Me the Edits, where she covers enhancements to our SSP All Edits tool which allows users to save all of a version’s edits into our own storage tables within your geodatabase. That alone is pretty cool but that got us to thinking about how we might utilize that infrastructure to get to State 0 without losing our versioned edits.
The goal would be to run the All Edits export against ALL of the outstanding design and edit versions to cache the edits into the SSP table structure. Our tool already reconstructs a version’s edits into Esri graphics with full attribution even after the original version has been deleted. So it’s not too far a stretch to say we could reconstruct them back into actual Esri versions in the geodatabase.
The twist here for SE Designer™ users is that SE maintains quite a bit of information about the Design version within a binary XML field in the database. Most importantly, this XML contains object ids that point to the specific edited records within a design version. So in addition to recreating the versioned edits we also need to handle re-synching the XML with the new object ids.
With that in mind, here’s our approach:
- Run a full system reconcile on an existing geodatabase, identify any version conflicts, and resolve them in their respective versions. The starting point should be a geodatabase without any conflicts, which is fully reconciled. Obviously we should remove any versions that don’t need to be kept around.
- Create a Nightly Batch Suite application to run the SSP All Edits extraction against ALL of the outstanding Esri versions to cache their edits into the SSP table structures, including all attribution and geometries.
- Delete all Esri versions, compress the geodatabase, and un-version the GIS data. I know… a scary thought for most of us…
- Perform whatever un-versioned, State 0 activities that caused us to attempt this crazy idea in the first place (geometric network changes, re-projection, topology changes, etc).
- Re-version the geodatabase.
- Here comes the magic… Create a second Nightly Batch Suite application that will programmatically recreate all of the edit versions, replay the edits from the SSP table structures as actual Esri edits within those versions, and resynchronize the Designer™ XML to point to the new object ids.
And voila!, we open up our SE Sessions, Designs, and Esri versions like nothing ever happened! Impossible you say? Well we’re set to find out and have found a customer that believes we can do it at Intermountain REA right here in beautiful south Denver.
We have received a lot of interest from many, many utilities who want to track our progress and results in this effort.So far we have completed the first Nightly Batch Suite app detailed in #1 above and are starting work on #6. We will keep you all updated and will even host the celebration party here in Denver when we make it all work!