Pivot Tables - Direct vs. Indirect Connection

[syswizard]

Just wandering what implications there are regarding the sourcedata for a pivottable originating directly from an ODBC connection vs. having a data table tied to that connection and the pivot table sourced from the data table.
There's got to be some overhead to the second approach, but doesn't it provide the possibility to clone the pivotcache so that slicers can act independently ?
With the direct approach, you've got two pivot tables pulling the same data, no ...In other words, it's two roundtrips to the database.

[HansV]

You can still clone the pivotcache - create a second pivot table based on the same connection, cut it to a different workbook, then cut and paste it back.

[syswizard]

Here's what I discovered: if you attempt to change the CommandText (SQL) of the PivotCache, Excel creates a whole new connection !!!
That is not good in my case where I want to manage the connections.
That is likely why they created "ChangeConnection" method in 2007. If you do that for both PivotTables, a refresh only occurs for the first table.
Strangely, setting EnableRefresh to False has no effect.

[syswizard]

You can still clone the pivotcache

How exactly is that cloning done ?

[HansV]

Just as I described...

[syswizard]

Just as I described...

Interesting. Are there no VBA methods for doing so ?

[HansV]

Hmmm... I just added two pivot tables based on the same direct connection to a worksheet, and added a slicer to each of the pivot tables. The slicers acted independently of each other, without me having to do anything for it, so I don't see the problem.