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