Forum Discussion

Akash_Mathew's avatar
Akash_Mathew
Icon for Microsoft rankMicrosoft
Aug 09, 2023

Clear OLAP Pivot table cache

I am trying to clear the pivot cache from an OLAP pivot table.

Tried the following VBA Code

 

Private Sub Workbook_Open()
    Dim xPt As PivotTable
    Dim xWs As Worksheet
    Dim xPc As PivotCache
    Application.ScreenUpdating = False
    For Each xWs In ActiveWorkbook.Worksheets
        For Each xPt In xWs.PivotTables
            xPt.PivotCache.MissingItemsLimit = xlMissingItemsNone
        Next xPt
    Next xWs
    For Each xPc In ActiveWorkbook.PivotCaches
        On Error Resume Next
        xPc.Refresh
    Next xPc
    Application.ScreenUpdating = True
End Sub

This works for Non OLAP pivot table but gives run time error for OLAP pivot table.

Any way to clear cache from OLAP pivot table using VBA or an addin? Any leads will be appreciated

excel
Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Aug 12, 2023

    Akash_Mathew 

    Clearing the pivot cache of an OLAP pivot table is a bit different from a traditional pivot table because OLAP pivot tables retrieve data directly from a data source (like an Analysis Services cube) rather than storing the data within the Excel workbook's cache. As a result, you cannot refresh or clear the cache in the same way you would for non-OLAP pivot tables.

    In the case of OLAP pivot tables, you need to focus on refreshing the data itself, rather than trying to clear a cache that does not exist. The code you provided is attempting to refresh a nonexistent cache, which is why you are encountering runtime errors.

    To refresh an OLAP pivot table's data, you can use the .RefreshTable method. Here is how you could modify your code to refresh the OLAP pivot tables:

    Vba code:

    Private Sub Workbook_Open()
    Dim xPt As PivotTable
    Dim xWs As Worksheet
    Application.ScreenUpdating = False
    For Each xWs In ActiveWorkbook.Worksheets
        For Each xPt In xWs.PivotTables
            If xPt.SourceType = xlExternal Then ' Check if the pivot table is an OLAP pivot table
                xPt.RefreshTable
            End If
        Next xPt
    Next xWs
    Application.ScreenUpdating = True
End Sub

    In this code, the SourceType property is used to check whether a pivot table is an OLAP pivot table (xlExternal) before attempting to refresh it.

    Keep in mind that refreshing data in OLAP pivot tables may involve authentication and connectivity to the data source. Make sure the users running the code have the necessary permissions and connectivity to refresh the data from the OLAP source.

    My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI. The text and the steps are the result of various AI's put together.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Aug 12, 2023

      NikolinoDE 

      Perhaps you are right, I have no idea how PivotTable cache works with OLAP connection, but it exists. You may create PivotTable based on data model and check the cache as

      Sub CheckPivotCache() 
 
 ' Determine if PivotCache has OLAP connection. 
 If Application.ActiveWorkbook.PivotCaches.Item(1).OLAP = True Then 
 MsgBox "The PivotCache is connected to an OLAP server" 
 Else 
 MsgBox "The PivotCache is not connected to an OLAP server." 
 End If 
 
End Sub

      I guess mechanisms are different for cached PivotTable and data model PivotTable. Perhaps in the latest we try not to keep data in data model with file. Have no idea how to do that with VBA.

Resources