Introduction
You’ve already been loading data into different alternatives or versions of the Smallworld Cambridge database using the Smallworld writer. In the previous sections, you updated the Cambridge electricity network.
The GE Smallworld reader can also read data from different alternatives; just set the Alternative parameter on the Smallworld reader in the workbench Navigator. In addition to reading all the selected objects from an alternative, you can configure the reader to return only the deltas (changes) between alternatives (and/or checkpoints). This is very useful if you want to synchronize your Smallworld VMDS with another database and only export the incremental changes.
The following example assumes that the updates from the Database Operations in Smallworld article have been successfully loaded into your Smallworld database. If that didn’t work, you can use the |FME alternative that was pre-configured with the changes
Step-by-step Instructions
Extract Deltas from an Alternative
After you update the electrical network in a Smallworld alternative, you can examine only the differences (deltas) between the baseline and the alternative. This example will export those differences for inspection.
1. Add Smallworld Reader
Open FME Workbench. Start with a Blank workspace on the Main tab of FME Workbench.
Add this reader:
- Format: Smallworld 4/5
- Dataset: localhost:30000
-
Parameters:
- Service: FMENOFACTORY
- Table List: electricity.cable, electricity.customer, electricity.supply_point
- Coordinate System: OSGB-GPS-2015-OSTN15
Uncheck "Use Search Envelope" in the Smallworld 4/5 Parameters dialog.
2. Run and View Data Caches
Run the workspace. You’ll see all the objects from the ***top*** alternative. You won’t see any of the changes you made to the database in the previous exercise.
3. Select an Alternative
In the workspace Navigator, under the Smallworld reader, select Alternative and set:
- Alternative: |fme_updates
4. Run and Inspect
Run the workspace. You’ll see all the objects from the ‘fme_updates’ alternative. You should see the electric network, including the changes you made to the database in the previous exercise.
5. Export Changes
Back in the workspace Navigator, under the Smallworld reader, set the following reader parameters:
| Export Changes from Baseline: | Yes |
| Baseline Alternative: | | |
Note the '|' or pipe character represents the ***top*** alternative.
Or you can use:
| Export Changes from Baseline: | Yes |
| Baseline Alternative: | |fme_updates |
| Baseline Checkpoint: | begin |
The 'begin' checkpoint was the first checkpoint in the |fme_updates alternative before any changes were added. You created this checkpoint in step 2 of the previous exercise: Database Operations in Smallworld.
6. Run and Inspect
Run the workspace. You’ll see only the deltas between the ‘***top***’ alternative and the ‘|fme_updates’ alternative.
The Smallworld reader automatically adds and sets the fme_db_operation attribute when you export changes so you could use these features to update other databases that FME supports.
7. Save Workspace
Save the workspace: smallworld7-complete.fmw
Exporting incremental changes this way can synchronize a Smallworld VMDS with an Oracle, SQL Server, or Esri Geodatabase.
Sync Smallworld with another database
The Smallworld reader allows you to extract differences between alternatives and checkpoints. The reader also sets the 'fme_db_operation' attribute to the appropriate value: INSERT, UPDATE, or DELETE. This makes it relatively straightforward to write to other databases and add only the changes, enabling incremental updates. For more on using 'fme_db_operation' for incremental updates, see the Tutorial: Updating Databases.
Below are two example workspaces: the first seeds the database, the second extracts the differences from Smallworld and uses fme_db_operation to update the target database. These should help you understand the incremental update process. You may need to adjust the Smallworld reader parameters to align with the Alternative and Baseline Alternative/Checkpoint in Smallworld.
1loadpostgis.fmw
2updatepostgis.fmw
Advanced Task—WHERE Predicate
The Smallworld reader supports WHERE predicates to select a subset of data.
In the Workbench Navigator pane, find the Smallworld reader parameters.
Set these parameters to select only cables whose status is "Accepted":
Export Changes from Baseline: No
WHERE Clause: [Electricity] cable where Status = "Accepted"
Run the workspace. Inspect the output. Only the ‘Accepted’ cables are exported.
Summary
This article demonstrates how to extract deltas from your Smallworld database by comparing the current alternative with a baseline or checkpoint alternative. This automatically sets the fme_db_operation attribute, allowing you to synchronize your Smallworld VMDS with another database. For more on using fme_db_operation, see the article Updating Databases.
You've also seen how to include a simple WHERE predicate on your Smallworld reader.