Speeding up refresh time for multiple PivotTables using a single external OLAP cube

2
votes

I have an Office Data Connection (ODC) pointed at an OLAP cube stored on our company's server. The connection string is fairly straightforward: 

Provider=MSOLAP.6;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Reporting Model;Data Source=SQL03;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

Using this cube, I have created an Excel file with multiple PivotTables, one per worksheet. Everything works fine, but when I refresh the PivotTables to get the latest data, each PivotTable refreshes separately i.e., the following appears for each PivotTable using the connection to the cube:

  1. Refreshing the OLAP cube
  2. Running OLAP query

Is there a solution that only requires a single refresh, with all pivottables sharing it? I've tried making all the PivotTables use the same cache, but attempting to set the pivot cache using:

Sheets("Pivot").PivotTables(1).CacheIndex = 1

ends in Run-time error '1004': Application-defined or object-defined error

Is there a way to get all the PivotTables to share the data source somehow?

Perhaps by created a local copy of the OLAP cube upon file open or by caching the PivotTable in a different way?

I've been playing with the VBA found at http://www.contextures.com/xlPivot11.html but it all seems to assume your data is coming from a local source.

1
excelvbaconnection-stringpivot-tablecube

1 Answers

1
votes

The concept of 'cache' for OLAP-based pivots is completely different than for non-OLAP-based pivots. As per https://support.microsoft.com/en-us/kb/234700

Pivots based on OLAP datasources, only the summarized data is returned to Excel, on an as-needed basis. With non-OLAP external databases, all the individual source records are returned, and then Excel does the summarizing.

Consequently, OLAP databases can provide Excel with the ability to analyze much larger amounts of external data.

But also consequently it's likely not possible or desirable to 'cache' the underlying data in Excel without doing some significant aggregations on it. But maybe that's the solution: make up a monster PivotTable with every field you want to report on, and then build a reporting interface that simply references that monster PivotTable using the GETPIVOTDATA function.