Data Migration from Oracle to MS Access Case Study

The Client

The client is a publicly traded multinational shipping company that follows a seven-year storage rule for records that contain financial data.

Migrating applications from Oracle to Microsoft is part of the client’s plan to reduce costs.

The Challenge

The client wanted to retire an old financial system because it was used only once a month at most and was very expensive to maintain.

However, the system could not be fully shut down because access to the financial data had to be maintained for seven years. As well, invoices needed to be accessed by users for historical and auditing purposes.

In order to keep the current system running, five servers needed to be maintained which included weekly scheduled backups, annual licensing costs, and staff time.

Key Challenges

– There was no source code provided.

– The data was inconsistent.

– Validation and testing of 61,781 invoices and 144,511 attachments was required.

The Process

  1. Map out all key fields stored in the database.
  2. Create queries to migrate data to Excel.
  3. Create MS Access database.
  4. Migrate Excel files to Access.
  5. Create queries and forms for Access UI.

How Optimus Helped

We chose MS Access as the target platform because it is a low-cost tool that is available within the client’s environment. It required minimal training and allowed for easy implementation of a database and forms.

Since a source code wasn’t provided, we mapped out all of the system’s data, from its UI to the 114 tables used in its Oracle database schema, to locate all relevant data. We then created our own SQL scripts to extract all of the data into Excel files.

The recorded data for all of the invoices varied with some records being duplicated. In MS Access, we removed any duplications and created search queries to ensure the data’s quality.

Multiple validation scripts were used on the source and target environments. We thoroughly compared the old system’s UI invoice results and MS Access to ensure the data was accurately extracted.

We reduced the overall server’s infrastructure services from five servers to a single 8GB USB eliminating maintenance and annual licensing costs and reducing the overall cost by a significant margin. In short, we met the client’s needs with a simple and low-cost solution.