3
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.
Advice is appreciated.
user1521687
Posted 2013-11-27T22:14:46.223
Reputation: 31
Do you need all formulas to refresh, or just certain cells (if edited or selected to be updated)? – Mat Carlson – 2013-11-27T22:56:10.613
All formulas. And the number of rows changes when you choose a different criteria from the Pivot Table drop down. – user1521687 – 2013-12-02T14:43:35.017
3The 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 Carlson – 2013-12-02T17:32:42.263
4When 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. – JNevill – 2014-03-17T15:53:32.080
3I 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 – BOB – 2014-03-28T06:19:15.610
Also consider using an ETL package to do the heavy lifting. I use Talend
– BOB – 2014-03-28T06:29:10.1571I 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. – jpmc26 – 2014-05-09T05:44:19.593