Wednesday, July 6, 2011

Creating an Excel Data Server

Prepare your Excel spreadsheet

First open up a Microsoft Excel spreadsheet, we will need to define a named range.
 
Step: 1) Open spreadsheet


Step: 2) Select Formulas-->Define Name


Step: 3) Select the range for the data



Define an ODBC Data Source

Step: 1) ODI will use an ODBC connection to natively access Microsoft Excel. Select start-->Administrative Tools-->data Source (ODBC)




 
Step: 2) click on Add select the Excel Driver press ‘Finish’. Specify the data source Name and description. Click on ‘Select Workbook’



Step: 3) Specify the Excel file path Press ok. You should now see your new ODBC Data Source listed in the ODBC Data Source Administrator.




Create a Data Server in Topology Manager

Step: 1) Open up Topology Manager and go to Physical Architecture. Right click on the Microsoft Excel technology and select New Data Server. In the Data Server window enter a name in the Name field.





Step: 2) Go to the JDBC tab. Select the Sun JDBC-ODBC Bridge in the JDBC Driver List.






Step: 3) In the JDBC URL template replace <odbc_dsn_alias> with the name of the ODBC Data Source you specified earlier. I used Excel data Server in this example.


 Step: 4) Click on Test and make sure you get a successful connection.


 
Click OK.

Step: 5) Go to Newly created data server and Right click select New Physical Schema 



Step: 6) Verify newly created Physical Schema.



Create New Logical Schema:

Step: 1) Select Microsoft Excel in Logical Architecture tab Right click and select new logical schema. Specify the name and map it with Contexts.



Create Model for Excel

Step: 1) Go to the Designer Navigator select model tab and create new model for excel.



Step: 2) Specify the Name, Technology and Logical schema.


 
Step: 3) Select Reverse Engineering Tab. Select system table check box and go to Selective reverse Engineering tab.

 
Step: 4) Here select Selective reverse Engineering and Objects to reverse Engineering check boxes.

 
Step: 5) Save it and perform reverse Engineer of this model.

 
Step: 6) Right click on data store select View data.

 Done !

5 comments:

  1. Hi Amit,

    I am half way through the steps you have defined.But when I select Reverse engineer button I get an error "You cannot reverse engineer a File type model.Reverse engineer with Column reverse engineer of the datastore"

    ReplyDelete
  2. Hello,
    Can it be possible for you to share this demo sample spreadsheet to practice these.
    Thanks,
    Venkat

    ReplyDelete
  3. Hi Amit, Very good article, thanks for providing indepth information of Oracle Data Integrator Technology. Please continue sharing.

    -Vasu

    ReplyDelete

  4. I find the required information on Oracle ODI. Thanks for providing your information on
    Oracle ODI online training.

    ReplyDelete
  5. Nice tutorial. The Oracle tutorial was help ful for me. Keep Sharing Tutorials.

    ReplyDelete