From the datawarehouse to the chart (part 1)
After Access lets use Excel as a client for Oracle.
I work for a real estate company where assets managers always want to make charts or calculus based on data stored in the Oracle dataWarehouse. Most od the time they want to do this with Excel because well known this tool. Sometimes they want to use Excel just because its easy to format data and then to put it in a Powerpoint document.
The problem is that we do not want to use VB macro in those Excel files . Those files may be sent by e-mail to clients. Any good firewall will destroy such files.
One solution is to generate files on a server. For instance you install Oracle's client and office on a Windows 2003/IIS5 server and implement complex ASP/ASPX pages which generate Excel files .
You may also use a Excel intepreted ASP page. Both of this solutions are hard to set up and need a lot of code.
There is another solution: lets use MS Query!
First of all your users need:
- Oracle connectivity install and MS Excel on there computer.
- A Data Source Name configure on there computer .
- A login and password which they can use to access the database.
I assume you work with Windows NT/XP and got an Oracle Server.
Here is how to get data from Oracle:
- create a new Excel document.
- in the "data" menu choose "External data" then "new query"
-Choose the appropriate Data Source Name
- Logon
- A wizard will guide you to make your select statement.
- choose return to Excel and validate.
surprise you've got your datas into Excel !
That's all folks! Any comments are welcome!
Next time I will try to expose how to fine tune this feature.