Speeding up pivots!

Hi,

I add about 100,000 rows of data into an Excel 2007 worksheet each month.

I then use pivot tables to summarise my findings.  The pivots are automated so I don’t need to use the REFRESH feature each time new data is added.  That said, it’s really slow because my pivots are based/highlighted on 1.2 million rows (i.e. 100,000 anticipated rows for each month of the year).  As we’re only up to April, only 400,000 rows have been utilised but the surplus were highlighted so that the new months (i.e. May, June etc) feed into the pivot when new data is added.

Is there any way I can speed this workbook/pivots because it can’t handle the 1.2 million rows?  At present, I have turned off the macro (the one that automates the pivot) because it’s stalling big time.  When I do manually refresh, I have to refresh each pivot in the workbook which takes ages!  If you have any macro code/tips, it would be greatly appreciated!

Cheers,

Baron

Get the data in a database, OLAP cube it, and pivot the cube.

OLAP cube it?  Would this be done in Excel? 

Are there any pivot cache options?

No, it is a separate data store.

As said, you need to move to a different program.  Currently Excel 2007 only supports 1,048,576 rows.  Before the year is over you'll reach the limit of Excel.

You need to move to a database option, like Access.

I think OLAp cubes are a bit over the top.

Just use MS Access or any other database you have (you can link via ODBC if it is available)

Do all your logic (queries etc) in the database then link to the data using the data import function in excel...

The data will be stored in the database but you can do your pivot tables in excel.

BTW Access also has a pivot table function too.

The big advantage of OLAP cubes is the pre-aggregation, saves Excel tons of work.

Hi Baron,

tell me a bit more. Not least, how do you receive the 100,000 rows of data each month? (a textfile, an excel sheet from someone else.. ?)

I will leave for vacation shortly, but if you can provide a little more info on especially the structure of the data received, I'll gladly look into it once I'm back. The situation as I've seen it so far is very similar to some of my own jobs, I might have a few ideas..

Best regards,

Hi Jesper,

The files are sourced from supplier excel spreadsheets.

Regards,

Baron

Hi Baron,

Had such problem. About 150 000 rows each month. Access in that case is useless, because it have limit of base size (*.accdb file cannot be larger 2 GB).

Solved problem by Excel 2010 and Powerpivot addin. http://www.powerpivot.com/

Its combination of OLAP technology and Excel, so it speeds up pivot greatly, and there is no limit on rows count.

Best Regards.

Hi Baron,

It was a rather long vacation, now I'm back

I don't think you even have to add the data into the file where you maintain your pivottable(s). If you so wish, you could just add them in separate sheets, thus avoiding the limit of app. 1,000,000 rows.

If you use the pivottabelguide Alt +D +P (if you are running an english version), you can specify several areas of data, not necessarrily from the same file. That solves part of what would be a problem, bearing in mind you expect roughly 1,200,000 observations in a year.

The other part.. speeding up - well actually I don't think there's a whole lot to do. It just is an awful lot of data you want to read and summarize in one or more tables, but instead of having to refresh tabels manually, and to avoid the time-killing function 'refresh on open', you could write the simplest possible macro containing only a 'ActiveWorkbook.RefreshAll' command. That does it for me, I can then concentrate on something else, and get back to my pivottables once finalized. Otherwise, you would have to spend time and effort to put data in some sort of other 'container' be it access, olap-cubes or whatever.

best regards,

Jesper