• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Pivot Caches in PowerPivot

V

Vivek D

Member
I created a dashboard based on Power Pivot data. I have about 18 pivot tables in the dashboard and on running the code below, I was pretty surprised to find that there are 18 pivot caches present in the workbook.

Code: 
Sub CountCaches()
  MsgBox "There are " _
      & ActiveWorkbook.PivotCaches.Count _
      & " pivot caches in the active workook."
End Sub

I usually create a new pivot table by copying an existing one as that is supposed to help avoid creation of multiple pivot caches but looks like Power Pivots work a different way.

To confirm, I created a new pivot based on an existing pivot and count increased to 19!

Are things different with PowerPivot or am I doing something wrong?
 
Check your file size after each Pivot Table is created from Data Model. Does it bloat file size significantly? My guess is that it shouldn't after initial increase. As it should refer to same Data Model and is basically empty (or very small) cache.

Where as, in regular Pivot Table, as PivotCaches.Count increases (be it same source or not), you should observe noticeable increase in file size. As it stores entire table each time.

If using Excel 2013, try using following add-on. It may help with Data Model compression/file size if that's your concern.
http://www.microsoft.com/en-us/download/details.aspx?id=38793
 
You must log in or register to reply here.
Back
Top