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 !

37 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. DevOps is currently a popular model currently organizations all over the world moving towards to it. Your post gave a clear idea about knowing the DevOps model and its importance.

    Good to learn about DevOps at this time.


    best devops training in Chennai | best devops certification course in Chennai | best devops training institute in Chennai | devops training in OMR | devops training in Tambaram | devops training in Velachery

    ReplyDelete
  5. Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.

    Best AWS Training in Chennai | Amazon Web Services Training in Chennai
    Learn Amazon Web Services Tutorial |Best AWS Tutorials For Beginners
    Best AWS Interview Questions And Answers
    Best AWS Training in Chennai | No.1 AWS Training Institute in Chennai Velachery, Tambaram, OMR

    ReplyDelete
  6. Really great post, Thank you for sharing This knowledge.Excellently written article, if only all bloggers offered the same level of content as you, the internet would be a much better place. Please keep it up!
    angularjs-Training in pune

    angularjs-Training in chennai

    angularjs Training in chennai

    angularjs Training in chennai

    angularjs-Training in tambaram

    angularjs-Training in sholinganallur

    angularjs-Training in velachery

    ReplyDelete
  7. This is quite educational arrange. It has famous breeding about what I rarity to vouch. Colossal proverb. This trumpet is a famous tone to nab to troths. Congratulations on a career well achieved. This arrange is synchronous s informative impolites festivity to pity. I appreciated what you ok extremely here.
    Data science course in bangalore | Data Science training with placement in Bangalore

    ReplyDelete
  8. It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.
    Most of ideas can be nice content.The people to give them a good shake to get your point and across the command.

    Java training in Bangalore|best Java training in Bangalore

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

    ReplyDelete
  10. 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

    ReplyDelete
  11. Nice Article.very impressed for this informative
    ExcelR data analytics courses

    ReplyDelete
  12. Thanks For sharing the way you presented is really amazing this helped me to gain a lot knowledge
    Best Software Training Institutes

    ReplyDelete
  13. Much thanks for composing such an intriguing article on this point. This has truly made me think and I plan to peruse more data analytics course in surat

    ReplyDelete
  14. fantastic instruction Data management and analysis are more effective when named ranges are defined in Excel for data server configuration. anticipating more actions.
    Data Analytics Courses in India

    ReplyDelete
  15. Hello Blogger,
    This comprehensive guide simplifies the process of creating an Excel Data Server, making it accessible for users. The step-by-step instructions and clear screenshots ensure an easy setup. Great resource!
    Data Analytics Courses in Nashik

    ReplyDelete
  16. This blog post likely discusses the process of creating an Excel Data Server, a topic that could be of interest to Excel power users and IT professionals. An Excel Data Server allows for the efficient management and retrieval of data, which can be crucial in various business contexts. The post is likely a valuable resource, offering insights and instructions on how to set up and utilize this server effectively. A must-read for those seeking to optimize their data management capabilities within Excel.
    Data Analytics Courses in Delhi



    ReplyDelete
  17. The creation of an Excel Data Server is made easier and more user-friendly by this thorough guide. An easy setup is guaranteed by the detailed instructions and excellent screenshots. fantastic resource
    Data Analytics Courses in Agra

    ReplyDelete
  18. Thank you so much for giving this wonderful tutorial on the Excel data server. It was so helpful for me to be a daily Excel user.
    Visit - Data Analytics Courses in Delhi

    ReplyDelete
  19. good blog
    Data Analytics Courses In Vadodara

    ReplyDelete
  20. Thank you so much for sharing this detailed blog on Creating an Excel Data Server. It has explained my knowledge of Excel.
    Visit - Data Analytics Courses in Delhi

    ReplyDelete
  21. Your step-by-step guide is incredibly helpful, breaking down the process into manageable stages.
    Digital marketing courses in illinois

    ReplyDelete
  22. Thanks for sharing comprehensive tutorial on Creating an Excel Data Server.
    Digital Marketing Courses in Italy

    ReplyDelete
  23. Thank you for sharing excellent and incredible tutorial on Creating an Excel Data Server.
    Adwords marketing

    ReplyDelete
  24. Excellent information. The concept that was explained is very useful and also ideas are awesome, I really love to read such a wonderful article. Thank you for the information.
    Investment Banking courses in bangalore

    ReplyDelete

  25. "Your blog on creating an Excel Data Server is like opening a door to a new dimension of spreadsheet wizardry. Transforming Excel into a dynamic data powerhouse is not just innovation; it's a symphony of efficiency. Your insights are a game-changer for anyone seeking to elevate their data management game. Here's to turning Excel into an unstoppable data maestro!"
    Investment banking as a career in India

    ReplyDelete