Hello,
I have hundred of users going to get the Excel 2007 upgrade. They all use
excel pivots connected to Analysis Services.
All these users are building workbooks with multiple different cubes or
different views of the same cube.
This causes me a big issue because by default all these views will share the
same connection. If you hover a pivot and select refresh, you are actually
set to wait a long time until all of the pivots sharing this connection are
refreshed. Most of the time the user needs only one or two of the pivots, the
other one are here to be used another day.
When I upgrade a 2003 workbook to a 2007, all pivots gets assigned a new
connection, but once in 2007, if you copy paste a pivot, it remains attached
to the connection
How could I have every pivot use its own connection? How to force Excel to
use separate connections?
I must avoid VBA because I have so many business users all over the world. I
do not want to get into a nightmare deploying software to end users.

I also found another big issue, if you copy paste different pivots from
multiples workbooks into a single workbook, if the source pivots all had a
generic connection name like "Connection", Excel keep that name in the
destination workbook and you and up with a big mess because all of these
cubes uses different olap databases and the connection points to only one
database.
To me it is a bad bug. If you try to change the connection name of the
second pivot, it will destroy your first pivot because this name changes
applies to the connection. I would need a "Detach connection" button of some
sort or a general Excel option for pivots like use shared connection or use
individual connections.
This subject will become very hot in a week when all these people will
receive the update through our IT services.