Hi,
I've got Excel workbooks using Pivot Table linked to MS AS.
I'd like to change the cube which the datasource refer to.
Changing the OQY file doesn't seem to have any impact.
The only way I've found is to pause the AS Server and try to update the
Pivot Table. After a time out, Excel asks for a new connection so I cancel
the server pause and change the cube source.
It's not a clean way at all and it's quite heavy to update a bunch of
sheets.
Any idea ?
Thanks
Bob

The name of the cube is stored with the MDX statement (it is part of the
FROM clause). If you kept a single cube per database (let's say call it
"DATA"), then you could change the both the cube and the database name by
always querying "DATA" and change the database name (which *is* part of the
connectstring, in the oqy file). Possibly one of the Excel newsgroups might
be able to assist you on the internal workings of Excel's object model.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Bob" <Bob (AT) nowhere (DOT) com> wrote

Hi Bob,

There's been various suggestions in past threads of this News Group. I
use an Excel add-in which provides a GUI to edit the pivot table source
server/database/cube info (see thread below). Others have suggested
saving as "XML Spreadsheet", editing the relevant connection info in the
XML, and opening it again in Excel.

http://groups-beta.google.com/group/...rver.olap/msg/
60b127ddd77efb0c?hl=en
From: Deepak Puri <deepak_p... (AT) progressive (DOT) com>
Date: Mon, 04 Oct 2004 21:22:33 -0700

Subject: Re: Using Excel 2000 to access Analysis Services 2000

Actually, the OLAP connection settings of an existing Excel pivot table
can't be changed by editing the .oqy files: such changes will apply when
creating a new pivot table.

The connection string is held in the pivotcaches.connection of the
workbook, so it can be updated via VBA scripts.

This earlier post presents some of the options:

http://groups.google.com/group*s?hl=...f43*db9d.03091
20
121.2918293b%40posting.google.*com

From: Thomas (thomasgre... (AT) hotmail (DOT) com)
Subject: Re: Excel external data source, how do I change it
View: Complete Thread (2 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.ola*p
Date: 2003-09-12 02:21:54 PST

see my previous post regarding "Excel Connection Properties"

There are three ways I can think off:

# brute force - disconnect your computer from the network and try to
update the data in the pivot table, XL will let you change the server
since it cannot find find it

# Modify the .oqy connection file created by XL in Notepad. It should
be located in c:\Documents and Settings\<user name>\Application
Data\Microsoft\Queries

# I put together an XL add-in that enables you (besides some other
things) to change the connection string easily and userfriendly. If
you provide me with your e-mail address I'll send it to you.

(There's supposed to be another way by saving the sheet in XML-format,
I never tried it because of solution # 3)

Thomas

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

And another obvious answer would be to use the MS Excel Addin for Analysis
Services.
http://www.microsoft.com/office/solu...n/default.mspx
In many cases, it's analytic capabilites are superior to Excel by itself.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Not an authorized method but I made it work once....

You can get back behind excel...

Hit ALT + Shift + F11

Search for QuerySource ... there you will see a connection line where you
can change server etc...

You need to have scripting installed however...

"Bob" <Bob (AT) nowhere (DOT) com> wrote