Increase Speed of OLAP Connected Workbook??

[onelifenoregret]

I have a workbook that my company uses that someone made and I'm trying to make it faster. It took me 5 minutes to open the workbook up yesterday.

There are 44 Caches and 5 Slicers.... This thing is ridiculous. Whoever made it, originally had it set up to RefreshOnFileOpen which I changed so now it opens right up but the data still needs to be updated daily. So here is my question.

What is the best way to speed up the workbook? All of the Tables use the same Data Connection.

Is it faster to make 4 or 5 massive Pivot Tables and then use things like INDEX MATCH to create the rest of the tables?

Is there a way to to find what PivotCaches affect the ActiveSheet and only refresh the Caches as the Sheet becomes Active?

What are the fastest ways to search create tables from other tables, is it INDEX MATCH? Would it be better to use GETPIVOTDATA? I'm pretty lost as far as the best way to improve this workbook. I know to do the basic things ScreenUpdating EnableEvents Calculation etc. but it's not nearly enough.

 

[onelifenoregret]

So is nobody able to help me or is there just nothing that can be done about this?

 

[Jerry Sullivan]

All other factors being equal, you'll improve the speed of refresh by...

1. Having only one data connection (as you already have)
2. Using a query to make that data connection extract the smallest amount of data that is needed.
3. Minimizing the number of PivotCaches.
4. Only refreshing PivotCaches that affect the results displayed to the user.

From your description, reducing the number of PivotCaches would seem to be a good place to start.

When some of those objectives are at odds with each other (is it better to have 2 small PivotCaches or 1 large one), then testing both approaches is a good way to find which is optimal for the typical use of your workbook.

Using formula functions like INDEX-MATCH or GETPIVOTDATA can be useful if you are only extracting a small number of values- but I doubt that using those to create a table of values would be more efficient than using a PivotTable or (ListObject) Table.