Background

A start up company and pioneer in the “dot-com” universe provided products and services oriented to kids recreational and educational activities.  The company had been in business for three years and utilized share-ware technologies for managing their data.

Problem

Each business functional area (ie Customer Service, Checkout, Credit Card Authorization, Order Management & Fulfillment) maintained their own data stores independent of one another.  We led the design of a new, integrated information architecture.  However, the source data of each functional area had to be cleansed, transformed and merged together into the common architecture or the project would be a failure.  The common architecture was being designed on the fly, and the migration was causing several changes as more was learned about the source data.

Solution

Given the large volumes of source data, neither the source nor target systems could accommodate the volume of processing that this conversion would require.  Instead, we built an ETL server that was dedicated to running the entire data migration. 

We could not stream the uncompressed source data to the ETL, because the network had to dedicate most of its resources to on-line customer access.  Instead, we extracted the source data into flat files which we then compressed and transferred to the ETL hub.

With Oracle being the target database technology, PL/SQL was selected to build the transformation and cleansing logic.  We utilized DBMS_ALERT to communicate between ETL modules so that components of the load could be run in parallel, maximizing the transformation timeframe. 

When conversion was complete, we would extract the data from the ETL hub, compress and transfer to the target platform.  Lastly, we leveraged Oracle Loader Direct Path to expedite the load of the data into the target database and when all data was loaded, we built the indexes and enabled all constraints.  We could reserve the enabling of constraints until the end of the load process because the conversion processes in the ETL hub also validated the constraint rules, ensuring that the constraints would be supported in the target.

Benefits

Centralized the Storage, Security and Administration of the Company’s Information Assets

Saved the nearly $200k cost of ETL Software and Related Implementation Costs

Elimination of faulty, primitive system to system data interfaces

Improved Data Quality Control