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

    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 

Limiting Records in PivotTable

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

Adding Drop-Down Control

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.

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

PivotTableWizard Method

Additional query words: 5.00c

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