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 !


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

  2. That was a great message in my carrier, and It's wonderful commands like mind relaxes with understand words of knowledge by information's.

    python training institute in marathahalli
    python training institute in btm
    Python training course in Chennai

  3. Thanks you for sharing this unique useful information content with us. Really awesome work. keep on blogging
    Devops Training courses
    Devops Training in Bangalore
    Best Devops Training in pune
    Devops interview questions and answers

  4. I am really very happy to find this particular site. I just wanted to say thank you for this huge read!! I absolutely enjoying every petite bit of it and I have you bookmarked to test out new substance you post.
    Data science training in bangalore

  5. 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

  6. Amazing Article ! I have bookmarked this article page as i received good information from this. All the best for the upcoming articles. I will be waiting for your new articles. Thank You ! Kindly Visit Us @ Coimbatore Travels | Ooty Travels | Coimbatore Airport Taxi


  7. All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.
    microsoft azure training in bangalore
    rpa training in bangalore
    best rpa training in bangalore
    rpa online training

  8. This is really impressive post, I am inspired with your post, do post more blogs like this, I am waiting for your blogs.
    Devops Training in Chennai | Devops Certification in Chennai

  9. 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

  10. This comment has been removed by the author.

  11. Hi, I really loved reading this article. By this article i have learnt many things about Hyperion Planning topic, please keep me updating if there is any update

    oracle Hyperion Online Training
    Hyperion Training
    ODI Online Training
    ODI Training