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?