First, I am working with various security confines at our organization. A few years back we hit the Row limit for Excel pivots. I discovered a ‘POWERPIVOT’ but am no expert. It got the job done. As we have grown so has the data set the powerpivot uses. The sucker has 60 columns and 18M rows. It a workhorse that is used daily. There are no columns they are willing do get rid of. The data is aggregated to the lowest level possible. The sucker essentially houses our Business Structure, employees, locations, widget numbers/descriptions and chgs/pmts/adjs. Once opened its very fast. The issue is it is taking a while to refresh…half hour…and we refresh it weekly….not the end of the world mind you but I am starting to question if perhaps there is a better way or tool.

The source is a SQL Table refreshed weekly. When that job is done the PowerPivot is refreshed (via VBA) and made available for the user base. It works well since the userbase does not and will not be granted access to the SQL table. I am sort of stuck with Excel. To be honest, I have a BI tool as well, but the pivot quite frankly is the easiest most efficient most understood tool for ‘this base of users’ and their ‘in the weeds’ data needs.

So, not being an expert, other than linking the PowerPivot to the table and refreshing it…are there any suggestions on how I might speed up the refresh…or the time it takes the user to open it. Depending on the user it takes 5 seconds to a minute to open. Once open, its fast. Again, the weekly refresh takes 30 minutes. Note, there was a time it took 90 minutes! They did something..they never tell us what..with servers and one day it was 7 minutes! Other stuff was running faster as well. Over the past year we are up to 30 minutes. I am not trying to be greedy and the refresh occurs off hours but its just time for me to better understand PowerPivots I think.

Any / all comments/suggestions are welcome.

Thanks!

I set up a PowerPivot.