XL2000: PivotTable Updated Slowly with OLAP Data Source

When you pivot or refresh a PivotTable, it takes a long time for Excel to update the PivotTable, and Excel may appear to stop responding.

This behavior occurs when you create a PivotTable in Microsoft Excel 2000 and all of the following conditions are true:

• The data source for the PivotTable is an Online Analytical Processing (OLAP) server.
  
  -and-
• The PivotTable contains a large number of row and column headings.
  
  -and-
• The calculated data for the PivotTable contains empty records for some of the PivotTable fields, making the PivotTable appear sparse.

To resolve this problem, obtain Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a).

To obtain SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base:

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Microsoft Office 2000 SR-1/SR-1a.

A PivotTable that you create from data stored on an OLAP server is updated much more slowly than a similar PivotTable that you create from data stored on another server, such as SQL Server. This behavior is noticeable when you perform an action that causes your PivotTable to be refreshed, such as changing the layout.

Before Excel displays summarized data in a PivotTable report, an OLAP server performs calculations to summarize the data and then returns the summarized data to Excel. An OLAP server returns new data to Excel every time you change the view or layout of the PivotTable or PivotChart report. Whereas a non-OLAP external database returns all the individual source records, and then Excel does the summarizing. Consequently, OLAP databases provide Excel with the ability to analyze much larger amounts of external data.

For additional information about OLAP PivotTables, click the article number below to view the article in the Microsoft Knowledge Base: