Querying big databases with Excel

Question

Is it possible to use Excel's pivot table as a kind of query builder against a database, without copying the entire database into Excel? I know that with the External Data Sources feature, you can connect to any ODBC database and create a pivot table out of the results from any SQL query. But at least with MySQL / ODBC, it seems to do this by copying the entire result set onto the local machine and then running the queries locally.

Is there a way to get the same functionality where the queries actually happen on the server, say by using Microsoft SQL server? My problem is that I want to be able to analyze data using pivottables, but the whole database table is too big to load into excel.

Answer 1

Even if you did copy the entire database (which would be incredibly large most likely), pivot tables are slow. It would take a lot of time to just get the pivot table to go through all of the data and display anything initially (I'm working with a pivot table about 30k rows and 20 columns, and it takes over 10 minutes to just load the file).

It is impossible to use a pivot table with anything other then a defined range of Excel values or a query, but the entire result set needs to be fetched first. Even if it were possible to use a pivot table directly with the database, pivot tables need to go through each row of data to parse it - effectively downloading the whole database anyways.

If you need Pivot Table functionality in your queries, look at the PIVOT and UNPIVOT commands for MS SQL, or if you're using MS Access, try using a cross-tab query to get you started. As a last resort, just filter out the rows and columns you don't need from the database before you start your pivot table.