XL98: Purpose of the PivotTable Optimize Memory Option (185325)

MORE INFORMATION

PivotTables in Microsoft Excel 98 Macintosh Edition offer new memory management capabilities when you query external data. When you query external data in earlier versions of Microsoft Excel, memory error messages commonly appear. The memory that is required by Microsoft Excel to process the query, to build the PivotTable, to display the data, and to interact with the various Open Database Connectivity (ODBC) drivers exceeds 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 98 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, press CONTROL and 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 you update the PivotTable or when you change its layout; 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 about PivotTable specifications, please see the following article in the Microsoft Knowledge Base: