title: Speeding up refresh time for multiple PivotTables using a single external OLAP cube
body: <p>I have an Office Data Connection (ODC) pointed at an OLAP cube stored on our company's server. The connection string is fairly straightforward: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <ol>
  <li>Refreshing the OLAP cube</li>
  <li>Running OLAP query</li>
  </ol>
  
  <p>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:</p>
  
  <pre><code>Sheets("Pivot").PivotTables(1).CacheIndex = 1 
  </code></pre>
  
  <p>ends in <strong>Run-time error '1004': Application-defined or object-defined error</strong></p>
  
  <p>Is there a way to get all the PivotTables to share the data source somehow?  </p>
  
  <p>Perhaps by created a local copy of the OLAP cube upon file open or by caching the PivotTable in a different way?</p>
  
  <p>I've been playing with the VBA found at <a href="http://www.contextures.com/xlPivot11.html" rel="nofollow">http://www.contextures.com/xlPivot11.html</a> but it all seems to assume your data is coming from a local source.</p>