Latest Contributions by BruceHarold

A colleague brought me this problem, a utility customer with large, versioned enterprise geodatabases wished to maintain what amounts to replicas synchronized daily (overnight).  Geodatabase replication was not feasible (I took his word for this, something to do with geometric networks, but in any event if the target was a feature service definitely the case).  The target was to be in Web Mercator and not the source low-distortion coordinate system. Normally I relish every opportunity to pull a ChangeDetector transformer out of my hat as its is a very flexible, fast way to derive INSERT, UPDATE & DELETE change sets that are then efficient to write.  The problem in this case though was data scale, reading the data into my ETL workspace would take hours (please don't take this as a general statement about Data Interoperability ETL workspaces, it's just enterprise geodatabases are busy things and reading very large batches of data can take time, and this database had over 10 million features). Time to visit a little known feature of Data Interoperability's enterprise geodatabase reader (GEODATABASE_SDE short name) - reading version differences.  Here is how to use it. You're going to need a version that is a direct child of Default that you never edit, it doesn't matter what other versions you have but only edits posted to Default will propagate to the mirror.  I call my child version 'Deltas'.  Your initial target system (geodatabase, database or feature service) must be a copy of Default. Your daily workflow is to get your edits into Default then compare version differences with Deltas.  If you think about it, after a daily edit post Deltas is a view of the database one day before Default.  That means its 'older', or an 'ancestor' of sorts (made my head spin too at first that a child is a logical ancestor but bear with me). When adding the geodatabase reader to your ETL workspace, set the Read Version Differences property and use a connection to Deltas as the transactional common ancestor.  I know, its weird, but it works. When the reader executes the features returned will be in the context of edits needed to make Deltas look like Default, and will have a format attribute fme_db_operation set to INSERT, UPDATE or DELETE. Now all you have to do is apply the differences.  I'll walk you through the sample workspace pictured below. The reader uses the option to merge all selected feature types using a * wildcard.  This lets you use a single reader for the entire set of versioned feature classes - did I mention how powerful this option is?  There is always a format attribute fme_feature_type available to let you see what source feature class anything came from.  The AttributeExposer lets me access fme_db_operation and an attribute FACILITYID, a unique key within each feature type that lets me support update operations.  If you don't have such a key field you will need to handle updates as delete/add pairs which I haven't modeled here. Inserts and updates can go directly to the target database or feature service but deletes are a bit tricky.  The FeatureReader reads one Deltas feature at a time with a SQL where statement that selects by ObjectID and you must also set the accumulation mode to Merge Initiator and Result to get the attributes from the deleted features onto the feature (they come through as null otherwise - they were deleted!). Then the data goes to the writer, which has FACILITYID set as the match column. After your synchronization completes the simplest way to be ready for the next day's run is to drop and recreate the Deltas version.  You could automate this with a shutdown script like in this article. In the customer system about 2000 edits across dozens of feature classes were 'posted' to the target in less than 10 minutes.  A sample ETL workspace is in the blog download.  Comment here for any clarifications. ... View more