Query multiple database platforms from Excel

[deepakvenkatesan123]

Each week I have to prepare a "Master Summary" report in excel that
takes data totals from 6 other database systems including multiple
Oracle instances, SQL Server and MySQL databases. The data from each
is independently queried, grouped, pivoted, sliced, diced and totaled
upstream using dozens of brio reports and sometimes progressively more
complex excel spreadsheets. Each brio report connects to the
respective database needed and runs custom SQL queries against it to
get the totals required for that region/system. The reports (there are
literally dozens) must be manually run then the data totals need to
either be exported to excel for further processing and hand copied
into the Master Summary or eyeballed and typed into the excel
spreadsheet! This whole process takes an entire week to complete. My
question is: is there a better way to do this? Is there some program
or feature that I'm not aware of that can:

1) connect to and query multiple databases to get data totals
regardless of vendor
2) perform functions/formulas/manipulation on that data regardless of
what database it is coming from
3) combine the data from a many, many sql queries into a single
summarized spreadsheet that makes sense

?? Thanks in advance for your feedback.
-Deepak

 

[Jim Thomlinson]

Selecting
Data | Import External Data | New Database Query
should allow you to connect to any database that you have the (ODBC) drivers
for. since you indicate that you are using Brio to create reports then you
may have to replicates some of that logic in your spreadsheet...

 

[Jim Thomlinson]

Selecting
Data | Import External Data | New Database Query
should allow you to connect to any database that you have the (ODBC) drivers
for. since you indicate that you are using Brio to create reports then you
may have to replicates some of that logic in your spreadsheet...

 

[Ron Coderre]

This may be a good situation for the QueryMaster file.
It has examples of ODBC-less connections to various databases.
If you already have (or can get) the Data Source information,
perhaps you could adapt it to your query needs.

It's available at Debra Dalgleish's website:
http://www.contextures.com/excelfiles.html#External

Does that help?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[deepakvenkatesan123]

thanks for the feedback guys. I came across this dashboard tool and am
having some success with it: http://www.getfirefly.net/ Ron, I will
look into the tool you provided as well thank you sir.
-Deepak