Description
 

This example is from a customer, the Australian Bureau of Statistics, Canberra, and uses a mix of FME, Python and Oracle to perform a quarterly data load.

"The end result and aim of the system is to provide spatial data on a production level database for in-house users with minimal disruption to their work flow.

We source the majority of our data from the Public Sector Mapping Agency (PSMA), which supplies updates to most themes quarterly.

The requirements are that the data is pieced together on a development database, then moved to an acceptance database for testing by the key clients. Once the testing has finished it can be moved into the production database.

All tables need to have their release date in their name, and have views showing full or part of the data. FME has enable us to build a system that manages this from start to finish."

Details

The whole process is a multi-stage system, requiring the use of FME startup and shutdown scripts.


The first part of the system uses FME to read source data and write it to Oracle. The exact table names differ depending on the date, but FME is set up to create only generic table names. This way the workspaces don't need to be edited each quarter.


To finalize the data, a combination of Python and PL/SQL is used. An FME workspace calculates the required name and kicks-off the script to rename the tables, create a generic synonym, create specific views, create indexes, and set-up roles and privileges.


The script is an FME Python shutdown script, that uses the python module "cx_Oracle" to call Oracle stored procedures that do the work.


As well as all this, FME is used to load some metadata into the database, including a form of log that records the results of the various workspaces.

Workspace

There are a number of workspaces for the basic data loading, including one for each of the different states in Australia:



This is the workspace for the state of Victoria:



The next workspace in the process starts off with a Python startup script. The script checks to see if the first workspace ran successfully and aborts if it in any way failed:



An abort causes an email to be sent to the administrator for further action:



Assuming all has gone well, a Custom Transformer determines what names the generic tables should be renamed to:



...and kicks-off the Python shutdown script which does the actual work:

Results

The result of all this is a set of specifically-named tables, with generic synonyms to point to them so users do not have to keep changing their workflows.

It also updates an Oracle-based log of FME processes: