I connected my excel to ODBC SQL Database(SQL Server) and now my excel pivot table is getting its data from the sql server VIEW. There is a total of about 1 million plus rows from the QUERY. From the data, I created new columns in excel to perform calculations. Everytime I performed a slicing and dicing from the drop down in Pivot Table, the formulas would need to be recalculate and it takes about 10 minutes for the calculation to be done. Is there a more efficient way to do this, because I cant make a user sit and wait for 10 mins to get the data they need.

Do you need all formulas to refresh, or just certain cells (if edited or selected to be updated)?
– Mat CarlsonNov 27 '13 at 22:56

3

The simple answer is that it is the way it is. Excel wasn't really meant to handle that much dynamic data (originally was limited to 32k rows or so, if I remember right). You may want to check how fast your DB server is sending data back (use Wireshark to see how long it takes to get all of the data, and if there is a long break between packets FROM the server). That may help a lot if the server is actually slow. You could also create a macro to refresh all data from the server into a sheet, then just gather data from the sheet (and turn off auto recalculate).
– Mat CarlsonDec 2 '13 at 17:32

4

When using excel as the interface for a database, you want to push as much of the calculation onto the database as possible. Can any of your formulas be calculated within the query? If not, would it be possible to push the data you need to drive the formulas up into the database so the query can handle it? Is there any way to restrict the number of records you are bringing back? Do you need all of that data for each pivot table view? 1 million is awfully high.
– JNevillMar 17 '14 at 15:53

3

I do the same development. >SQL View through ODBC connection< I also add columns that calculate things like selling price and lookups product groups. The more complexity you build the longer it takes. So as a solution I try to create most of the Calculations and additional columns in the SQL VIEW itself. i.e. I let the SQL server do the hard work. not Excel
– BOBMar 28 '14 at 6:19

1

I understand that it's probably because Excel is what you know, but as a programmer, so many alarm sirens go off in my head at the notion of pulling data from a database and then applying formulae to it in Excel. I suppose that stems from my personal opinion that all spreadsheets are replaceable by relational databases, and that they are better organized and easier to use than spreadsheets. There's no chance of actually doing the calculations via SQL? Relational databases are very good at performing calculations on large quantities of data.
– jpmc26May 9 '14 at 5:44

1 Answer
1

I would meet this requirement using the Power Pivot Add-In. It can combine data from different SQL queries together with Excel tables and other sources.

The resulting model is compressed and held in memory for very fast and scalable Pivot Tables. There are memory limits for any process with 32-bit Office (which most people have), but 64-bit Office is only limited by available RAM.

You can add calculations in Power Pivot using DAX formulas - simple ones are similar to standard Excel formulas, and it has a lot more computing power available if needed.

For complex data transformation requirements I reach for the Power Query Add-In, which can transform and deliver data into the Power Pivot model.