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 Serverwindow enter a name in the Name field.

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

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 !