XL97: Purpose of the PivotTable Optimize Memory Option

ID: Q162476

The information in this article applies to:

Microsoft Excel 97 for Windows

SUMMARY

This article explains the PivotTable Optimize Memory option that is available when you create a PivotTable that is based on external data.

MORE INFORMATION

PivotTables in Microsoft Excel 97 offer new memory management capabilities when you query external data. When you query external data in earlier versions of Microsoft Excel, memory errors commonly appear. The memory required by Microsoft Excel to process the query, build the PivotTable, display the data, and interact with the various Open Database Connectivity (ODBC) drivers exceed memory limits.

In earlier versions of Microsoft Excel, PivotTable storage (the cache) is optimized for dimensions (row or column arrays of data) with 256 or fewer unique items. However, this optimization applies only to cache updating operations. Microsoft Excel 97 extends this optimization to PivotTables by determining the number of items for each field prior to initially populating the cache. This option can be made available by selecting Optimize Memory in the PivotTable Options dialog box. You can access this dialog box in step 4 of the PivotTable Wizard. In an existing PivotTable, right-click the PivotTable, and then click Options on the shortcut menu.

Microsoft Excel issues a series of queries via ODBC to the external data source in order to determine how many unique records are in each field. These queries are issued prior to returning the data to the PivotTable. Microsoft surveys the results of the queries when it populates the PivotTable cache to determine whether storage of each dimension can be optimized. The optimization results in additional queries when the PivotTable is updated or its layout is changed; therefore optimization may cause a decline in performance. The degree of performance degradation is a function of the efficiency of the ODBC driver, the performance of the file server that serves the source data, and the size of each dimension list.

For additional information on PivotTable specifications, please see the following article in the Microsoft Knowledge Base:

   Article-ID: Q157486
   TITLE     : XL97: Limits of PivotTables in Microsoft Excel 97

REFERENCES

For more information about optimizing PivotTables, click the Index tab in Microsoft Excel 97 Help, type the following text

   PivotTable memory

and then double-click the selected text to go to the "The system doesn't have enough memory to create the PivotTable" topic.

Additional query words: 97 XL97 8.00 pivot table

Keywords          : xlpivot 
Version           : WINDOWS:97
Platform          : WINDOWS

Last Reviewed: November 4, 1998