XL5: Controlling Memory Used by PivotTable RAM Cache

• 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

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
   
   

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.

For additional information, please see the following article(s) in the Microsoft Knowledge Base:

   ARTICLE-ID: Q104308
   TITLE     : 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

   PivotTableWizard Method