Friday, February 02, 2018

Detect ArcGIS Feature Changes When Archiving is Not Enabled

Use the information below at your own discretion and risk. Feel free to adjust, modify and leverage to meet your own requirements.

The method proposed in this post aims to be simple for easy adoption though it is highly recommended that it is performed by an experienced IT professional.

The approach here described is only viable if Archiving is not enabled for your Enterprise Geodatabase e.g. if you have Geometric Networks enabled which at this point cannot be combined with Archiving functionality.

Complex ETL processes run periodically to keep data in sync across all these repositories. In order to move data around, these processes track deltas (data changes), that occurred in the database. These deltas are then synchronized with the relevant databases.

These ETL processes are heavily dependent on the ArcGIS Archiving functionality since it provides details about each delta including a unique identifier and dates when the change occurred. A delta can also have one of three possible states:

CREATE

UPDATE

DELETE

This information is critical for the synchronization processes.

The purpose of this article is to discuss what to do when Archiving is not available e.g. you are dealing with an Electrical Network that is part of a Geometric Network and archiving is not an option as it cannot be combined with datasets that already participate in a geometric network topology.

There are 4 ways of addressing this issue:

ArcGIS Replication

This can work in very specific use cases, but typically does not support complex scenarios where the databases don't share the same logic and schema

It is possible to write custom code to compare the database state with a replica generated in a previous run but this can become complex to implement and to maintain over time

Inspecting the ArcGIS SDE adds 'A' and deletes 'D' versioning states

Complex to implement and maintain over time. Changes to the Esri gdb schema can break the implementation logic and become difficult to manage over time

SQL Server Native Replication

SQL Server Replication including peer to peer (in two-ways) can be enabled in certain scenarios. Live changes can only be implemented in non-versioned databases. Versioned databases can be sync once reconcile, post and compress have been executed so that changes are moved to the base tables. I'll leave this discussion to a future article, but be aware that this sort of implementation also creates tight coupling between the schemas of all participating tables which can become difficult to manage over time

SQL Server Change Tracking

You can leverage the native SQL Server tracking capabilities to provide information about what rows have changed for a particular base table and the type of change done i.e. whether it was an INSERT, an UPDATE or a DELETE

This article focuses in point 4 above and provides a basic example of how it can be implemented for software developers.