XL97: Purpose of the PivotTable Optimize Memory Option

 Last reviewed: February 12, 1998
Article 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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 12, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.