Hi,

I have been preparing a set of cubes in Analysis Services 2005 to be viewed
using an Excel 2003 front end and am struggling with deployment issues.
Excel reports have been designed as pivot tables based on AS cubes, linking
through the OLEDB 9.0 driver for Analysis Services. The pivots run perfectly
on the machine they were designed on (which is also the server). However
when attempting to refresh or alter the pivots on any other machine this
returns the error 'Initialization of the data source failed.'

All these other computers are set up with SQL Server developer edition and
can all access and alter the cubes via BIDS. They are also all using the
OLEDB AS 9.0 driver and can create the OLAP cube data source through Excel
however they fail at the final point when the data is trying to be retrieved,
returning the error above.

Using VBA to retrieve the connection string for a pivot shows it is, by
default:

OLEDB;Provider=MSOLAP.3; Cache Authentication=False;
Persist Security Info=True; User ID="";
Initial Catalog=OpsReporting; DataSource=WLDN0163639;
ImpersonationLevel=Impersonate; Location=WLDN0163639;
Mode=ReadWrite; Protection Level=Pkt Privacy;
Auto Synch Period=20000; Default Isolation Mode=0;
Default MDX Visual Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non
Empty Threshold=0;SQLQueryMode=Calculated;Safety Options=2;Secured Cell
Value=0;SOURCE_DSN_SUFFIX="Prompt=CompleteRequired ;Window
Handle=0x702C6;";SQL Compatibility=0;Compression Level=0;Real Time
Olap=False;Packet Size=4096

with the server name being WLDN0163639 and the AS database being
OpsReporting.

Surely it must be that the connection string is incorrect but I seem to have
a shortage of understanding as to what each of the properties within the
connection string do and what all potential options are as I have never had
to alter the defaults till now. The most obvious candidate is the UserID,
however all the alternatives I have tried (e.g. adding €œIntegrated
Security=SSPI€�) have failed.

We are using Windows Authentication (all our computers are on Windows and
none are connecting from outside the company) but I am not clear on how the
security works remotely with AS and especially what alternatives there are
for ways of connecting (i.e. types of connection strings). Logon accounts
for SQL Server and Analysis Services are both set to Local System as this
seems to be the only way to get them to work. I am uncertain how to tell if
AS is using TCP/IP or Named Pipes (or something else?); SQL Server is using
TCP/IP in all the DSNs which works fine but I have seen comments about AS2005
having problems using http. I am not sure how to go about changing this in
AS if it is an issue and am also unsure about using IIS. I have looked at a
whole range of possible solutions (mentioned in other threads) such as
changing ports but they all seem to be a shot in the dark; I think I may be
missing something simpler here?

Appreciate any assistance.

TIA,

Rob