XL5: Controlling Memory Used by PivotTable RAM Cache

ID: Q129160


The information in this article applies to:
  • Microsoft Excel for Windows, version 5.0


SYMPTOMS

The following example shows how you can use SQL queries and controls in Microsoft Excel to control the amount of data that is stored in a PivotTable RAM cache when the PivotTable is created from data in an external data source.


MORE INFORMATION

The key to optimizing a PivotTable cache is to provide enough data in the cache to make the PivotTable useful while limiting the number of records in the table to avoid a substantial decrease in performance.

The amount of data that can be held in the RAM cache of a PivotTable is limited by the amount of RAM on your computer; that is, the more RAM you have on your computer, the more data you can hold in the cache.

There is no set limit for the number of database records that can exist in a PivotTable cache. In general, the more columns a record contains, the fewer records you can have in the cache.

Depending on your computer's configuration, it is possible, for example, to bring 10,000-100,000 records into the PivotTable cache successfully. However, while it is possible to bring this many records into the cache, the performance of the PivotTable will be negatively affected. For example, it could take several minutes to bring 50,000 records into the cache, depending on the processor on your computer. Additionally, with this many records, every time you query in the database again, there will be a delay.

To make the best use of a PivotTable, you must effectively manage the queries to bring data into the cache. The PivotTableWizard method allows you to specify SQL queries to bring data into the cache. By using these SQL queries and Microsoft Excel controls, you can design an interface so that you can view a large amount of data, without a long delay to bring data into the cache.

Visual Basic Example

When you design your interface, you can create a PivotTable by using the PivotTable command (Data menu) or by using the PivotTableWizard method in a Visual Basic procedure. The following is an example of creating the PivotTable by using a Visual Basic procedure.

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

    Sub CreatePivot()
      ' Dimension variable Pivot1 as PivotTable data type
      Dim Pivot1 As PivotTable

      ' Create PivotTable from external data source NWind
      Worksheets(1).PivotTableWizard _
        sourceType:=xlExternal, _
        SourceData:=Array("DSN=NWind", "select * from Orders"), _
        tableDestination:=Worksheets(1).Range("B6"), _
        tableName:="Pivot1", _
        RowGrand:=True, _
        ColumnGrand:=True, _
        SaveData:=False, _
        HasAutoFormat:=True

      ' Set value of Pivot1 variable equal to new PivotTable
      Set Pivot1 = Worksheets(1).PivotTables("Pivot1")

      ' Set the location of the fields in the PivotTable
      With Pivot1
         .PivotFields("Ship_Regn").Orientation = xlPageField
         .PivotFields("Ship_Cntry").Orientation = xlRowField
         .PivotFields("Employ_Id").Orientation = xlColumnField

         ' Set location and format of Order_Amt field in PivotTable
         With .PivotFields("Order_Amt")
            .Orientation = xlDataField
            .NumberFormat = "$#,##0_);($#,##0)"
         End With

         ' Format resulting table
         .TableRange1.AutoFormat format:=xlClassic3
      End With
   End Sub 
NOTE: In the PivotTableWizard method in the procedure above, the sourceData argument can be used to pass an SQL statement. You can use this SQL statement to effectively control the data in the cache.

Additionally, in the same PivotTableWizard method, note that the SaveData argument is set to the value False. This prevents the RAM cache from being saved with the file, and improves the performance when you open the file. However, when you use the SaveData argument with a value of False, the PivotTable must be refreshed in order to use it.

Limiting Records in PivotTable

In the above procedure, the entire database table is brought into the RAM cache. If the table is very large, then this will take a few minutes. Assume for example that you bring in a 40,000 record table, and that the records in the table are equally distributed among the shipping regions; as indicated by values in the Ship_Regn field. Instead of bringing the entire database table into the PivotTable cache, you can bring in a subset, such as data for just the WA region. To do this, you can use the following value for the SQL statement used as the sourceData argument:

   SourceData:= _
     Array("DSN=NWind", "SELECT * FROM Orders WHERE Ship_Regn = 'WA'") 

Adding Drop-Down Control

To allow access in the PivotTable for other regions, you could place a drop-down control on the worksheet that would display the different regions by using the SQLRequest function in the XLODBC.XLA add-in to enter values in the drop-down. The following is an example of using this procedure:

  1. Create a new module or open the Visual Basic module that you want to use to create the list for the drop-down control.


  2. To establish a reference to XLODBC.XLA, choose References from the Tools menu. In the Available References list, select the XLODBC.XLA (this file is located in the LIBRARY\MSQUERY subdirectory of the Microsoft Excel directory.)


  3. In the Add-Ins dialog box, choose OK.


  4. Select the worksheet on which you want to display the drop-down control. If the Forms toolbar is not displayed, display this toolbar using the Toolbars dialog box (View menu). On the Forms toolbar, choose the Drop-Down button. Drag on the worksheet to create the drop-down.


  5. In the Visual Basic module you used in Step 1, enter the following:
    
          Sub PopulateDropDown()
             Worksheets(1).DropDowns(1).List = _
               SQLRequest("DSN=NWind", "SELECT DISTINCT Ship_Regn FROM Orders")
          End Sub 


  6. From the Tools menu, choose Macro. From the Macro Name/Reference list, select PopulateDropDown, and choose Run.

    The following procedure is an example of how to refresh the PivotTable cache with only the records for the region selected from the drop-down control created above:
    
       Sub RequeryDataBase()
          ' Dimension variables
          Dim Drop1 As DropDown, Pivot1 As PivotTable
          Dim Region As String, SQLString As String, SourceDataArray As Variant
          Set Drop1 = Worksheets(1).DropDowns(1)
    
          ' Set Pivot1 variable equal to PivotTable
          Set Pivot1 = Worksheets(1).PivotTables(1)
    
          ' Set Region variable equal to item selected in drop-down
          Region = Drop1.List(Drop1.Value)
    
          ' Set SQLString variable equal to SQL statement to select only
          ' records where Ship_Regn field equals region selected in drop-down
          SQLString = "SELECT * FROM Orders WHERE Ship_Regn = '" & Region & "'"
    
          ' Set SourceDataArray variable equal to the source data for
          ' the PivotTable
          SourceDataArray = Pivot1.SourceData
    
          ' Select the existing Pivot Table
          Worksheets(1).Select
          Pivot1.TableRange2.Select
    
          ' Refresh the PivotTable cache using the new SQL statement
          Worksheets(1).PivotTableWizard _
            sourceType:=xlExternal, _
            SourceData:=Array(SourceDataArray(1), SQLString)
    End Sub 


To assign the RequeryDataBase macro to the drop-down control, do the following:

  1. Hold down the CONTROL key, and click the drop-down control.


  2. From the Tools menu, choose Assign Macro.


  3. From the Macro Name/Reference list, select RequeryDataBase, and choose OK.


When you select a region from the drop-down control, the PivotTable cache is refreshed based on the region that you select. For additional information, please see the following article(s) in the Microsoft Knowledge Base:
Q104308 XL5: Optimizing Pivot Table Performance


REFERENCES

For more information about the SourceData Property, choose the Search button in the Visual Basic Reference and type:

SourceData Property
For more information about the PivotTableWizard Method, choose the Search button in the Visual Basic Reference and type:
PivotTableWizard Method

Additional query words: 5.00c

Keywords :
Version : WINDOWS:5.0
Platform : WINDOWS
Issue type :


Last Reviewed: October 29, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.