BACKGROUND:
I have an excel file report that has 7 worksheets with 7 report "sections" within each sheet. This file uses CUBEVALUE formulas to populate standardized tables with data from the Power Pivot model. The model is populated from Power Queries against an MSSQL database. Each one of the 7 sheets x 7 sections = 49 report sections has a dummy pivot table behind it, whose sole purpose is to tie a set of 10 slicers together for cross-filtering within that report section (slicer cubesets are referenced in the formulas). Executing a "Refresh All" on this file is taking about 1.5 min with roughly 75,000 records in the Fact table of a simple star schema model in Power Pivot.
ATTEMPTED SOLUTION:
To optimize the refresh time, I tried looping through each of the 49 pivot tables to set the PivotCache.EnableRefresh property to false before initiating a "Refresh All". This worked like a charm, shaving the refresh time down to 30 sec.
ISSUE:
However, even though I set the PivotCache.EnableRefresh property back to true afterward, the SLICER caches don't seem to be sync'ing back up with the Pivot Caches. For example, if I had originally refreshed the file with July and August data, but then I refresh it for August and September data, my Month SLICER still shows July and August as the available months. But...if I filter the PIVOT TABLES directly, I can see the new months (Aug and Sept) available in the filter selections, so the Pivot Cache seems to be up to date.
HELP NEEDED:
Does anyone know of an efficient way to get the slicer caches to sync back up with their parent pivot caches after disabling and re-enabling pivot cache refresh? Or, is there another way to optimize the Refresh All under these circumstances?
WHAT I TRIED:
I noticed that as soon as I selected a new filter condition either in a slicer or in a pivot field, all the slicers immediately sync'd back up. So, I tried creating a dummy table containing 2 rows with values 0 and 1 and making this table load to Power Pivot. I then created a slicer on this table and tied all 49 dummy pivot tables to this simple slicer. Each time I would refresh the file, I would toggle the selected value in this slicer between 0 and 1, after re-enabling pivot cache refresh. This worked, in the sense that it seemed to force the slicer caches to sync back up with the pivot caches, however this action also brings me almost back to my original refresh time.
