Oracle Data Integrator (ODI) – Data Integration Strategy

In the ODI CAB Meeting held during Open World we’ve heard a lot of new insights regarding ODI Suite and several cases.

To sum up the main differentiators of ODI when talking about a Data Integration Strategy:

  • ODI Suite: Data Delivery Services by usage of ESB, the mediator
  • ODI Suite: Orchestration of composite services through Oracle BPEL Process Manager integration
  • Data Profiling & Data Quality: end to end governance, statistical analysis, cleansing of data and prevention of bad data being loaded
  • Unified workflows using knowledge modules
  • Declarative design using only databases and pl/sql, no need for other technologies
  • Datamart to deliver star schema’s
  • Hyperion integration for fast querying
  • Webservices to integrate to third party applications, e.g. for campaigns
  • Metadata Navigator for data lineage (and documentation purposes)

ODI will be incorporated throughout the entire Oracle Stack to enable pervasive data integration.

Pre-packaged data-integration for Apps (Peoplesoft, Siebel, SAP) so all ETL-flows are performed by ODI.

Tips & Tricks when using ODI for your enterprise-wide data integration:

  • Define groups in ODI
  • Use ODI Security Module for creating generic and non-generic profiles and arrange user rights
  • Use Oracle Analytic functions
  • Use naming standards for variables, user functions, …
  • Don’t call pl/sql functionality outside ODI, this would be strong wiring

For information regarding ODI you can download our presentation were we’ve compared OWB, ODI and ODI Suite: The Next Generation of Business Integration: Making the right choice!

Oracle Data Integrator – Part 2 – Working with Excel Data

What have I learned today …

Today I took a closer look at declaring or using my Excel file as a source model for the ODI for which I’ve used the ODI User Guide and the help-documentation in ODI itself.


What do I need to do to get ODI to talk to my Excel file?

  • Create a new ODBC datasource for my Excel files using the Microsoft ODBC Data Source Administrator (run from the Configuration Panel)
  • Make sure you’ve defined a name for the data in the Excel spreadsheet. You can accomplish this by defining a name for the data shown in every worksheet. You have to mark the data in a specific worksheet (select all the rows and columns with data) and choose to insert a name for this data using the insert-menu.

For the configuration of ODI I’ve used different kinds of guidance, help then only the User Guide. To define my ODBC Datasource in the ODI I’ve followed the topic ‘Create a Microsoft Excel Data Server’ (written in the help-documentation of ODI) . This topic has a more detailed approach then the topic ‘Creating a Data Server’ explained in the User Guide.

The steps I’ve performed:

  • Connect to the Topology Manager (first open up the Designer from your Oracle Data Integrator menu)

  • Select Topology > Physical Architecture > Technologies > Microsoft Excel in the tree

  • Right click and select Insert Data Server
  • Fill in the necessary fields in the Definition and JDBC tab, such as the name and the JDBC Driver (sun.jdbc.odbc.JdbcOdbcDriver) and JDBC URL (jdbc:odbc: => the name you defined for your ODBC data source)

  • You can test your connection, test if the connection was succesfull by clicking the test-button in your Data Server properties screen.

The next step we need to perform is to create the physical schema for our Excel file to be able to map this ‘source’ schema to the ‘target’ schema.

  • Right-click on your newly created Data Server and choose to insert a new physical schema:
  • Leave the defaults as is for the Physical schema and choose the wright context in the context-tab which means you need to specify the logical schema you want to use. Specify a name and click apply and ok.

  • Let’s reverse engineer the Excel file into a new model. Go to the designer and choose to insert a new model. In the technology-dropdown choose ‘Microsoft Excel’ and choose your Logical Schema.
  • In the reverse-tab make sure to select the context you want to define the model in.
  • In the ‘Selective Reverse’ tab choose all checkboxes and the data you’ve defined names for in the spreadsheet will be listed. Click OK to reverse-engineering the defined data into your model.
  • In the model-tree you can see the different tables that where created inside the model depending on the data you’ve defined in the excel sheet.

  • In the different DataStores that were created (depending on the data you’ve defined in the spreadsheet) you can check the colums that were defined in your spreadsheet.

The next step is to start creating Interfaces to define the transformation that’s needed from our source-model to our target-model.

Stay Tuned