RE: How do I configure Analysis Services for Excel 2003 users?

• From: "Robert Chapman" <RobertChapman@xxxxxxxxxxxxxxxxxxxxxxxxx>
• Date: Wed, 1 Feb 2006 03:00:38 -0800

Well we've finally found the reason for the problem outlined below so I 
wanted to post the solution.  This error is produced due to a problem with 
the 9.0 driver for Analysis Services.  It occured on our machines because 
they had had Office 2003 installed AFTER SQL Server.  The fix is simply to 
reregister the driver, running regsvr32 "c:\program files\common 
files\system\ole db\msolap90.dll" at the command line.  Very simple in the 
end.  

Rob

"Robert Chapman" wrote:

> 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.  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
> 
.

• Follow-Ups:
  • RE: How do I configure Analysis Services for Excel 2003 users?
    • From: Deepak Puri

• Prev by Date: RE: AS2005: Nice implementation of Current Day/Month/Qtr/Yr - any suggestions
• Next by Date: Re: Using 2 fact tables in AS2005
• Previous by thread: RE: AS2005: Nice implementation of Current Day/Month/Qtr/Yr - any suggestions
• Next by thread: RE: How do I configure Analysis Services for Excel 2003 users?
• Index(es):
  • Date
  • Thread

• Windows
• Science
• Usenet

• Archive
• About
• Privacy
• Search
• Imprint