EXPORTING TO EXCEL FROM OWC
Some customers have reported difficulties in exporting data from Office PivotTable Components to Excel. Microsoft has investigated these reports and determined that:
• OWC does not pass the expand state to Excel correctly, so Excel expands everything. Because all rows are set to visible, all data must be downloaded and all empty rows must be filtered out. This can cause a significant slowdown in the export operation. Rectifying this situation is not as easy as it would seem because the two programs handle the expanded state very differently.
• There is also an issue with the formula and the data caches. The OLAP client caches both data and formulas. By default, the cache is limited to 25 percent of available system memory. If that is exceeded, the cache is purged. To continue processing, the same data must be downloaded over and over. This may cause the export operation not to work. Try setting the cache memory higher as described in Microsoft Knowledge Base article 814030.
This cache dump issue is not limited to OWC, but may also occur in Excel PivotTables.
I’ve never liked the fact that pivots are stored in cache, but having decided to do this why limit it to 25%?