As we pointed out in another post (ArcGIS Archiving) the standard meta-data fields (CreationUser, DateCreated, LastUser, DateModified) provided a limited amount of information about what edits have really occurred on a given feature or row.
For example, the DateModified date may refer to a change made by an automated process (such as Feeder Manager) and overwrite a map update made manually by a user as the result of information provided by field crew observation.
As suggested before, ArcGIS Archiving is a great way to record all of this information. However, Archiving may not be for you.
For one, if you’re moving to ArcGIS from another platform, or for whatever reason loading data for the first time and have a corresponding set of historical edits there’s no way to move these into your Geodatabase archive tables.
For another, Archiving is entirely tied to ArcGIS versioning. If you ever un-version your data — which you *shouldn’t* need to do — then you’ll lose the Archive info. At least using out of the box tools.
So, for these or other reasons Archiving might not be for you. What then?
Well, we know of no product level solution to this problem. (Please speak up if you know differently!) Meaning solutions are in the realm of customizations. If you’re in this spot here are some observations on implementations where we’ve seen this requirement tackled.
Before talking about implementation we should be clear about how we would expect to use the tracked data – and use it in ways that differ from use of simple meta-data fields. Here are some we’d anticipate to be common:
- Tracking Deletes. This is one of the biggest concerns for system governance and accountability. If something is not in the database that used to be we want to know when and how the delete happened. Or at least we want to be able to know. And we want the tracking of the operation done at a system level and not the responsibility of an application or a user. For example, it may be your standard practice to not delete features but “abandon” them using the ArcFM Abandon Features tool. This is great, but it doesn’t prevent a user from deleting records.
- Tracking editor productivity. Not to say that a user that has edited more individual features than another is necessarily more “productive,” but some companies may want some quantitative assessment. There is the adage “you can’t manage what you can’t measure.”
- Tracking large edits. It is possible (without some customization to prevent it) for a user to inadvertently select more than what she expected and update or delete everything in the selection. If you’ve worked in ArcMap long enough you almost certainly have done it yourself – and hopefully have clicked “Undo” or “Stop Editing” immediately after.
There are certainly more possible, but these are probably a good start.
General Implementation Considerations
Here are a few important items to consider before we dig in to talk about solution components.
- Edit tracking mechanisms. Core ArcGIS technology provides access to “edit events” that allow you to handle all adds, changes and deletes to any edited feature/row at the time the edit is actually made. It also allows you to detect differences between your version and its parents, giving a set of edits upon request.
- Undo/redo. Any edit that’s made can be un-made by clicking the “undo” button — up until the time a “save” is done. Then the edits are persisted and can only be changed by making other edits.
- Versioning. Most edits at most sites are performed in versions that are children of SDE.DEFAULT (or a child of it). A version maybe deleted at any time — and along with it any edits made within that version. And, by the way, when your version is reconciled/posted and then deleted, while the results of the edits are present in SDE.DEFAULT, any evidence of the edits themselves is lost.
So, armed with that background let’s explore potential components of an *all* edit tracking solution.
Given that an application is a set of processes operating on a data structure, here are some things for which our solution will need to account.
- The Data Store. If we’re going to track all the edits ever performed on any feature/row we need a place to store the data. All this data can’t exist in the edited class itself. The options are an edit transaction class per tracked class or a single edit transaction class for all tracked classes.
We prefer the single edit log approach – with no formal database relationships from tracked to tracking class. Here’s why.
- A tracking class for each class tracked would introduce *many* tables — nothing our DBA friends would be happy with.
- There might be an advantage to a tracking class per class if we wanted to implement composite relationships to control the lifespan of child (tracked) records — but we don’t.
- Searching for edits made will be much simpler if we don’t need to guess which class to search in. Anticipating a typical use case being either counting or retrieving a set of edit operations by some criteria (i.e., user, date, type, etc.) it will be much easier to do if all the edit records are in one place.
Data Store Columns Here are at least some of the fields we would need in our edit tracking table.
Column Name Data Type Description Feature Class ID Long Class ID in which the edit was performed – though it applies only within the current database instance. Feature Class name Text In case we need to track edits in an instance extracted from the main edit database instance. Record Global ID GUID If your database has these. Record ObjectID OID If your database does not have GlobalIDs. Edit Time DateTime Date and time of edit. Edit Type Text INSERT, UPDATE or DELETE. User Name Text Login name of the user that performed the edit. Edit Field Name Text Only for UPDATEs. Old Value Text Only for UPDATEs. New Value Text Only for UPDATEs.
Optionally, you may choose to a geometry field, which could be helpful for visualizing and possibly restoring deleted features.
Is the tracking table versioned?This would really be an implementation preference, but we would suggest it should be. Why?
At the end of the day the important thing to know is edits that have occurred on rows in the “system of record” version of our database — that is SDE.DEFAULT. is it interesting to know about edits made to a version that was deleted before getting posted to SDE.DEFAULT? We don’t know the business case for this.
Further, including edits made that did not make their way into SDE.DEFAULT would just muddy the water.
- What about potential conflicts if we version the edit tracking table? No worry. Rows are only added to this table — never deleted or changed, thus no chance of a conflict.
When to track the edit? There are really two options here — on edit or on reconcile. And when we say “On Reconcile” we mean on reconcile with SDE.DEFAULT. If we take the approach that we only care about edits that reach SDE.DEFAULT, then we don’t care about reconciles where the parent is a child of SDE.DEFAULT. Here then are pros and cons for each.
On Edit On Reconcile Tracks *All* Changes Yes. All the changes are captured and we avoid the danger of a reconcile failure. Normally, yes. Though susceptible to loss on reconcile failure No Impact on Editor Performance No.Rows are inserted to the edit log on each edit operation. Yes.Edits are tracked only when a Reconcile is performed Avoids un-needed edit tracking No.Records are written and kept even in the case the version is not posted to SDE.DEFAULT. Yes.Records are written only for rows that are to be posted to SDE.DEFAULT.
There are arguments to be made on either site of the one. We lean toward tracking “on edit” to make sure that everything is captured and nothing missed – but we could be swayed.
This post has discussed aspects of solutions to track *all* edits made in an ArcGIS Geodatabase using observations from implementations we’ve seen that have tackled the problem. As noted above, other than ArcGIS Archiving, there’s not a product level solution. If you feel you need to take on this task yourself hopefully the post will have proved somewhat helpful.
N.B. After initially published we found that there IS a product-level solution to meet this requirement provided by SSP Innovations. For more info see the following: