XL97: Purpose of the PivotTable Optimize Memory Option (162476)

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.