Extracting Differences from an ArcSDE Geodatabase

Introduction

FME has the capability to extract differences (or deltas) from an Enterprise Geodatabase (ArcSDE). The key functionality is:

• Reading Transactional Version differences
• Reading Historical Archive differences (i.e., Historical Date/Time and Historical Markers)

This tutorial focuses on extracting differences from a transactional versioned Geodatabase. However, the principles are the same for reading differences from historical archives.
If you are not familiar with Esri Geodatabase versions or archiving, then the following links will be useful:

• Esri Geodatabase Versioning
• Esri Geodatabase Archives

Versioning is only available in the Enterprise Geodatabase (ArcSDE).

Extracting differences (or deltas) from a Geodatabase allows you to replicate or synchronize it with other databases in your organization.

Video

Geodatabase Structure 

A versioned Geodatabase will often have a hierarchical structure as show below:

In ArcPro this will look like:

Transactional Version Differences

The Esri Geodatabase (ArcSDE Geodb) reader parameters dialog has several parameters that control the different data you can extract from versions (see the FME User Documentation for more details). Refer to the image of the Geodatabase reader parameters below.

• Under the Version disclosure panel:
  • Transactional Version: the version you want to read
• Under the Read Version Differences disclosure panel:
  • Check Read Version Differences
  • Baseline Transactional Version: the version you want to compare against

These parameters are also available in FME Data Inspector if you want to visualize the deltas in your geodatabase.

Workspace Example

If you'd like to run the examples discussed in this tutorial, please go to the addendum at the end of the tutorial and follow the example setup instructions.

FME Workspaces for extracting differences are surprisingly straightforward. This example converts water utility data from Ga eodatabase (ArcSDE) to PostGIS. The key part of the workflow is based on the published parameters:

In this example, the data is being read from the Transactional Version: DBO.WaterUpdates3 and compared against the Baseline Transactional Version DBO.DEFAULT.

The following three images from FME Data Inspector illustrate the state of the Esri Geodatabase versions:

Image 1: Original data - DBO.DEFAULTS version

Image 2: Edited water mains data - DBO.WaterUpdates3 version

Image 3: Differences between DBO.WaterUpdates3 and DBO.DEFAULTS versions

What you don't see in the images are the deleted objects, because they have no geometry. However, they are logged in the FME Data Inspector Table Viewer and Feature Information windows:

Notice that the fme_db_operation attribute has been set to DELETE.

fme_db_operation

When you extract differences from geodatabase transactional versions or archives, FME automatically sets the fme_db_operation attribute to INSERT, UPDATE, or DELETE. Most FME database writers support fme_db_operation for incremental updates to the database. This means that it is straightforward to push the deltas into the target database. For more on how to use fme_db_operation, see the article Incremental Database Updates using the FME format attribute fme_db_operation.

Child Version and Data Replication Workflows

The geodatabase reader has a Child Version parameter that can be used to create the next version for editing. In this example, we're reading the differences between DBO.WaterUpates3 and the DBO.DEFAULT versions. If we set the child version to be WaterUpdates4 then FME will create the new version which will then be your starting point for the next round of edits in your geodatabase. This allows you to set up a data replication workflow: extracting differences, creating a new version, undertaking edits in the new version, next round of differences, etc.

Historical Archive Differences

Working with historical archives is very similar to the transaction version described above. The geodatabase reader parameters dialog has several parameters (marked in the parameters dialog image above) that control the different data you can extract from archives:

• Under the Version panel:
  • Transactional Version: the version you want to read - typically sde.Defaults
• Under the Read Version Differences panel:
  • Check Read Version Differences
  • Baseline Historical Marker or the Baseline Historical Date and Time

Gotchas

When extracting differences from a versioned geodatabase, FME uses the concept of a common ancestor, so all differences are based on the common ancestor of the two versions you're working with. For this reason, it's not a good idea to extract differences between different branches in your versioned geodatabase as shown below:

- You're likely to get unpredictable results!
 

Step-by-step Instructions

The following instructions will walk you through how to create a workspace that will read your ArcSDE versions. 

1. Add an Esri Geodatabase(ArcSDE Geodb) Reader 
Open FME Workbench and start a blank workspace. Add an Esri Geodatabase (ArcSDE Geodb) reader to the canvas and open the Parameters.

In the Parameters, browse to your SDE Connection File. Next, enable Remove Table Qualifier and select all of the Tables by clicking on the ellipsis and selecting DBO.

Next, enable Read Version Differences. This will allow us to set a Baseline (either a Transactional Version or Historical time if using Archiving), which will be used as the 'parent' of the comparison version.
In order for the 'child' version to be compared against the Baseline, we need to ensure we set the Version on the Reader; otherwise, it will default to the version used in the SDE connection file. Most often, this is the DEFAULT version. Click on the ellipsis next to Baseline Transactional Version and select dbo.DEFAULT. Click OK twice to add the reader.

2. Add Inspectors
Now that we’ve added the data, we will want to inspect our versions using either Visual Preview or FME Data Inspector. To do so, select all three reader feature types by clicking and dragging a box around them. Next, right-click on any one of the three and select Connect Inspectors. 

Sometimes, when setting up the ArcSDE Reader, the Version query option will error out with 'Unable to open the Geodatabase reader because the <DATASET> keyword was not found'. If you encounter this, you can work around it by typing the version name, DBO.DEFAULT.

3. Create User Parameters
When we run the workspace, we want to select which versions to compare; we will use user parameters to quickly switch between versions. In the Navigator window, right-click on User Parameters and select Manage User Parameters (In 2020.2 or older, select Create User Parameter). We will create two parameters, with the following setup:

Parameter 1:

• Parameter Type: Choice
• Parameter Identifier: TransactionalVersion
• Prompt: Transactional Version
• Published: Enabled
• Optional: Disabled
• Choice Configuration: Dropdown
• Choice Values:
  • DBO.DEFAULT
  • DBO.WaterUpdates1
  • DBO.WaterUpdates2
  • DBO.WaterUpdates3
• Default Value: DBO.DEFAULT

Parameter 2:

• Parameter Type: Choice
• Parameter Identifier: BaselineTransactionalVersion
• Prompt: Baseline Transactional Version
• Published: Enabled
• Optional: Disabled
• Choice Configuration: Drop-down
• Choice Values:
  • DBO.DEFAULT
  • DBO.WaterUpdates1
  • DBO.WaterUpdates2
  • DBO.WaterUpdates3
• Default Value: Leave Blank

4. Assign User Parameters
Now that we’ve created our user parameters, we need to assign them to the reader parameters. In the Navigator, expand the [GEODATABASE_SDE] reader and then expand Parameters, and expand Advanced. Right-click on Transactional Version and select Link to User Parameter. 

In the Set to User Parameter dialog, select BaselineTransactionalVersion

Repeat this step with Transactional Version in the Connection Parameters, and set it to the TransactionalVersion parameter. 

  We need to create one more user parameter, but we can do that from the Navigator. Right-click on Child Version Name and select Create User Parameter.

In the Add/Edit User Parameter dialog, click OK as we can accept the default parameters. 
You should now have three parameters linked for the GEODATABASE_SDE reader. 

5. Save and Run Workspace
Save the workspace and run it with Prompt for User Parameters enabled. Now, each time you run the workspace, you can select which versions you wish to compare. 

Addendum: Setting up the example in an ArcSDE Geodatabase

If you want to run the examples described above, you can use the following steps to load the sample data into your own Geodatabase (ArcSDE) environment. Use the package create-differences.zip. If you are not familiar with working with versioned Geodatabases ,then you might wish to review the article on editing version data in ArcGIS.

• Use FME Workbench and the workspace "Load Data to ArcSDE.fmw" to import the GML data into your ArcSDE
  • Load a.CoV_Water_Default_seed_data.gml into the default version of your geodatabase.
• Version these tables – sde.DEFAULT
  Right-click feature class, Manage -> Register as Versioned
• Using ArcCatalog and FME Workbench to create three new versions and load the sample GML data into each version. Use the workspace "Load Data to ArcSDE.fmw" to load the GML data into your ArcSDE
  Right-click the database, Administration -> Administer Database
  • create version WaterUpdates1
  • load b.CoV_Water_Default-WaterUpdates1.gml into version WaterUpdates1
  • create version WaterUpdates2
  • load c.CoV_Water_Default-WaterUpdates1-2.gml into version WaterUpdates2
  • create version WaterUpdates3
  • load d.CoV_Water_Default-WaterUpdates3.gml into version WaterUpdates3

Use the version hierarchy shown in the image:

Note: You can't create all the versions and then load them; you have to create, load, create, load, etc.

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.