RefreshTable vs PivotCache.Refresh

[moorey]

Hi Guys,

My questions relate to pivot tables in excel 2003 that are extracting data
from OLAP.

Through testing i have found that there are major differences in execution
time by changing either the activesheet or the syntax from pivotcache.refresh
to refreshtable. Yet on MSDN there is no explanation defining the differences
between these 2 commands and why/whether sheet activation would have any
impact on the execution time.

I was wondering if someone could explain the underlying mechanics for each
of the 2 different lines of syntax and whether sheet activation could impact
performance. Any help you can offer would be much appreciated.

Kind Regards
Moorey

 

[Tim Williams]

You're much more likely to get suggestions if you post your test code and
timings.

Tim

 

[moorey]

No problems Tim,

I have a workbook that contains 17 pivot tables all connected to OLAP cubes.
I have a refreshPivots subroutine that goes something like this:

Sheets("1").PivotTables("PivotTable1").PivotCache.Refresh
Sheets("2").PivotTables("PivotTable1").PivotCache.Refresh
Sheets("3").PivotTables("PivotTable1").PivotCache.Refresh
etc

The cubes that these pivots are based on are extraordinarily large,
unfortunately my mandate is to fix these apps and not the underlying data
source of which recommendations have already been made. When i convert the
syntax from the above to:

Sheets("1").PivotTables("PivotTable1").RefreshTable
Sheets("2").PivotTables("PivotTable1").RefreshTable
Sheets("3").PivotTables("PivotTable1").RefreshTable
etc

I get about a 50% increase in performance

However when i add the extra syntax to activate the sheet (i.e
Sheet("1").select and then the Refresh command) i get an additional 10%
performance.

Now it could be that having run through this in testing the pivotcache is
held in memory by the cube and all additional refreshes are receiving
performance benefits as a result. However i have only cached the first 3
pivots in the 17 and the performance was readily seen across all 17 pivots.
What once took close to 1hr and 45mins to run is now taking about 35 minutes.

All i want to understand is what is causing that net performance increase.
As it stands there is very little detailed information on how information is
cached for pivots when connected to an OLAP data source. What i wish to
ccomprehend is what happens when a developer refreshes the pivot cache versus
what happens when we refresh the table??? The additional question about
activating sheets is secondary to understanding how the cache operates with
an OLAP data source.

If you need any other ifno or if i haven't explained my question clearly
please don't hesitate to let me know.

Regards
Moorey