PivotTable component connecting to OLAP server cube appears to stop responding in Office XP and in Office 2003 (814030)


The information in this article applies to:

  • Microsoft Office XP Web Components
  • Microsoft Office XP Professional
  • Microsoft Office XP Small Business
  • Microsoft Office XP Standard
  • Microsoft Office XP Students and Teachers
  • Microsoft Office XP Developer

SYMPTOMS

When you try to add data to a PivotTable component that is connected to a large OLAP cube (*.cub), the data may not be added, the Web component may appear to stop responding, and the processor activity may indicate 100 percent utilization.

Note The same operation in an earlier version of Microsoft Office Web Components (OWC) may complete in about three minutes.

CAUSE

This problem occurs because the Microsoft Office XP Web Components and Microsoft Office 2003 Web Components use many more calculations than Microsoft Office 2000 Web Components, so it generates much deeper calculation stacks that must be cached. When the formula cache exceeds the specific maximum cache size, the whole formula cache is emptied. When this occurs, a decrease in performance occurs.

WORKAROUND

To work around this problem, increase the client cache size. To do this, see the following macro example and notes:

Note The client cache size is a property you can set in the connection string or as an OLEDB property. The default value is 25, and this is equal to 25 percent of the available physical RAM. Microsoft provides programming examples 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 article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, see the following Microsoft Web site:

http://www.microsoft.com/partner/referral/

For additional information about the support options available from Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS 

With ActiveWorkbook.PivotCaches _ 
   .Add(SourceType:=xlExternal)
   .Connection = _
      "OLEDB;Provider=MSOLAP.1;Data Source=adatum;" & _
      "Connect Timeout=60;Initial Catalog=FoodMart;" & _
      "Client Cache Size=25;Auto Synch Period=10000"
   .CommandType = xlCmdCube
   .CommandText = Array("Sales")
   .MaintainConnection = True
   .CreatePivotTable _
      TableDestination:= Range("A3"), _
      TableName:= "PivotTable1"
End With

Notes about Client Cache Size

  • You can also specify the cache size that will be used on the client by using the Client Cache Size connection string property in the Properties dialog box for an OLAP server in the OLAP Manager. We do not recommend that you use the unlimited (0) cache setting when you use Microsoft SQL Server 2000 Analysis Services Service Pack 2. Instead, we recommend that you upgrade to SQL Server 2000 Analysis Services Service Pack 3 (SP3) or a later service pack.
  • Client Cache Size settings:
    • 0: No limit on the cache memory that is used on the client. Using this setting may make it possible for your query to approach the same speed as a query that uses an earlier version of the Office Web Components.
    • 1-99: The percentage of physical memory to be used for caching.
    • >=100: The cached memory in kilobytes.

MORE INFORMATION

The Analysis Services client (Pivot Table Service) caches both data and formulas. These are handled by two different caches. The data cache stores data as it was received from the server. The formula cache stores the result of specific calculations, so a re-calculation is not required the next time someone asks for the same data. After the cache size exceeds a certain amount of memory on the client (25 percent by default), the data and formula cache are both cleared. This means that any precalculated or pre-fetched values are lost and must be re-evaluated or requested from the server again.
Modification Type:MajorLast Reviewed:5/4/2006
Keywords:kbPivotTable kbstoprespond kbprb KB814030
©2004 Microsoft Corporation. All rights reserved.