Translate topic in your language

Wednesday 6 July 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 !

15 comments:

  1. Thanx so much, really interesting and helpfull i wwish i could have found it earlier! :-)

    ReplyDelete
  2. Very nice post here and thanks for it .I always like and such a super contents of these post.
    Excellent and very cool idea and great content of different kinds of the valuable information's.

    Java training in Bangalore



    ReplyDelete
  3. Thanks For Sharing The information The Information Shared Is Very Valuable Please Keep Updating us The Information shared Is Very Valuable Python Online Course Data Science Online Course Aws Online Course

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. This concept is a good way to enhance the knowledge.thanks for sharing..
    ODI Online Training
    ODI Training
    ODI Corporate Training

    ReplyDelete
  6. useful stuff with detailed practical information
    DevOpsTraining in chennai | DevOps Course in Chennai

    https://www.credosystemz.com/training-in-chennai/DevOps-training-in-chennai/

    ReplyDelete
  7. You have done a great job. I will definitely dig it and personally recommend to my friends. I am confident they will be benefited from this site. data scientist course in kanpur

    ReplyDelete

Note: only a member of this blog may post a comment.