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 !