GIS Database Migration Project (Oracle to ArcGIS)

Question

I have been tasked with converting an Oracle Database into an ESRI file geodatabase. The data represents a gas network. The client's Oracle database has over 400 tables, 120 of which are spatial. I'm going to use FME to convert the data into an ESRI File Geodatabase. The client has supplied me with the geodatabase which contains feature datasets and feature classes which are empty (schema only). The geodatabase is a modified version of the ArcGIS gas data model (pdf). Essentially it contains the main feature dataset with a geometric network as well as other data the client is already using in ArcFM.

The problem I have is that the Oracle database contains many more tables than the ESRI schema. There are tables in the Oracle database which have been created which do not have an equivalent table in the geodatabase. Furthermore, for those tables that I can match, there are fields that do not match. For example, in the original database, there may be a table which contains fields such as "street number", "street name", "city", etc and the destination database does not contain these. Furthermore, in the valve table in the original database, there is a field called "Direction To Close" which contains a "C" for Counterclockwise and an "A" for Anticlockwise and some blank values. In the destination database there is an equivalent field called "ClockwiseToClose" which has a domain set for "Yes/No" values.

That is just one field in one table. And as I said, there are over 400 tables. I'll need to determine all of the issues which may arise for all fields in all tables. Obviously, there is a lot of work involved in setting this all up.

Does anyone have any experience in a similar project? Can anyone give a realistic estimate of how long a project like this might take? I'm thinking several months.

Obviously the domains and subtypes in the destination geodatabase will likely cause issues and the data will have to be cleansed in the process. Also, there may be fields which have numeric values in one database and text/categorical in another (with the domains). That is, what might be "A", "B", "C" in the original might have a domain which restricts the values to "1", "2", "3", for example.

What other traps might I come across in a data migration project like this?

Answer 1

I did a project like this where we used a staging db in between. We first documented each and every dataset and field. on both sides. then created a map document for checkoff verification (so we didn't leave anything behind), then we did several pulls into the staging db.
This is where we also created go between tables that would allow us to (as you mentioned) stage in data that needed to be massaged for the domains etc.
We also created a db for tables that had nowhere to go. We also documented tables that were direct loads and others that needed either owner verification, or further development (and what that development would be).
We then created xml sets that would ultimately be our load data.

It did take a team at different points. (no one person knows all about the 400 tables).
Two of us worked probably 300 manhours (plus the team meetings).
This was for a set of probably 60 tables.

This was on older software and not so many tools.

We made a spreadsheet listing each dataset owner/stakeholder.
Then under the dataset we listed each field name it's length and
datatype and a sample of data values (if we could domain it we did).
The domains we kept in another sheet in the spreadsheet.
(for later use in the domain section of arcgis)
If you work fast you might be able to impress them enough to keep you on.
I think with your limitations you might. Prioritize all 400 tables. Start at the top priority and document the incoming data as thouroghly as possible (we had stakeholder meetings to understand what and why certain data were important or how they were used, how accurate they were, how they might be used in arcfm.
And make a cutoff time period then start to create a spreadsheet showing the incoming data, the outgoing data, and map each field to the target field.
It is tough because they are probably going to say the rest is not important enough to pay to finish but at least you would have the big part of it done.

Answer 2

You could use the SchemaMapper transformer to allow you to define feature type (table name), attribute and value mappings in an external table or file for ease of entry/maintenance. Of course with that many tables there is no getting around the amount of work required to define, test and validate the mappings. Several months sounds reasonable, unless it's all you do, then maybe a couple weeks. The work should probably be divided amongst multiple people when you start talking about that many tables.