Oracle Data Integrator

For my project one of the tasks I need to accomplish is to work out the migration path from an oracle db and excell files to a new oracle db.
I need to design a new application in adf with a process-driven data-model to replace the existing ‘application’, which are excell files with a lot of pivot tables, redundancy, …
The first application, before the excell files, was written in Oracle Forms, but isn’t currently used anymore.

One of the challenges I’m faced with now is the need to migrate the old oracle schema and the excell files to my new oracle schema. The data model has changed a lot according to the previous one, because the data-model is set-up in a process-driven manner.

The past weeks we’ve analysed the existing data and the different strategies we can can follow for this migration path. The migration has to be done only once for all the data that’s stored in the old schema and in the excell files and the best way to do is … indeed that’s the question?

I’ve been reading a lot on technet, on different blogs, in whitepapers, … about the different possibilities there are, the ones I came up with:

  • external tables
  • sqlloader
  • Oracle Migration Workbench (SQLDeveloper plug-in)
  • Oracle Data Integrator
  • Open Source ETL Tools
  • After taking a look at the strengths and weaknesses of every technology/possibility I’ve chosen to have a closer look at ODI (Oracle Data Integrator).

Some of the features I’m really interested in and which are very useful for me are the following:

  • Transformation between source and target
      My source system, the excel files, have a different data structure than my target database. To be able to map the source-data to the target-data I will need a mapping-tool such as ODI to do this mapping in a declarative way.
      E.G. In the excell file the address of a person is stored in one cell, this needs to be mapped to different fields in my target-datasource, …

  • Ability to check the data-flows (via Data Flow Control) between the source- and target-destination and have error-handling and logging for each needed step during the process
    • Data flow control consists of checking the source data of an interface compared to the consistency level required on the target. Data is checked before the data is integrated in the target.
      Detection of an error can give rise to either non-integration of all the data (even correct), or integration of only the correct data. In any case, it is useful to isolate and/or recycle the erroneous data (after any necessary correction).

  • Use existing Knowledge Modules which hold the necessary tasks to extract and load data according to the pre-defined steps in this Module
  • The following important question is … how to get started?

    Firstly I’ve installed ODI after reading the installation guide and then I’ve taken a tour of the ‘getting started guide’ and the demo material that’s included in the ODI install.

    After reading through the ODI User’s guide I’ve noted down the following important decision points:

    • I need a ‘One Shot Interface’ for my migration path because I only need to perform this migration one time, before going to production
    • I need an LKM (Loading Knowledge Module) to extract data from the source database and my excell files
    • When designing Interfaces in Oracle Data Integrator, these Interfaces contain several phases, including data loading, data check, data integration, etc. For each of these phases, you will define:
      - The functional rules (mappings, constraints, etc.) for this phase
      - The Knowledge Module to be used for this phase.

    Secondly I’ve read the threads on ‘otn’ to be able to have an idea of the community that’s using this tool and the people I can asks questions to ;o)

    Thirdly it’s time to get started …

    I will keep you posted on the progress and the ODI-experience … so stay tuned !