2007 OLAP Cube building against SQL 2005 Cluster

[Brant Flick]

I've setup an OLAP cube before against a non-cluster SQL setup. I followed
the technet guide (http://technet.microsoft.com/en-us/library/cc197552.aspx),
installed the SQL prereq's on the Project Server, and added the SSP service
account with full admin permissions on the Analysis instance. However, I'm
getting an error:

Failed to build the OLAP cubes. Error: Analysis Services session failed with
the following error: Failed to connect to the Analysis Services server
corpsql03moss\moss. Error: Cannot connect to the repository. Analysis server:
corpsql03moss\moss Error: Provider cannot be found. It may not be properly
installed.

I can connect to the AS instance with the SSP service account and create a
new database. I was unable to add that account to the
SQLServer2005MSOLAPUser$<Servername>$<Instancename> group since it's a
cluster install. I don't know what domain groups were specified during the
install to take it's place. There's only one obvious domain group in our
organization for SQL, and adding the SSP service account to that made no
difference.

Project Server is SP2 with December 2009 CU running on Server 2003 R2 SP2
x64. SQL is 2005 with SP3 on Server 2003 SP2 x86.

I'm using the SQL 2005 Decmber 2008 FP 64bit tools on the Project Server.

 

[Martin Laukkanen]

There are a number of things that you can check;

1. Firstly your Analysis Services instance is a named instance correct? Have
you entered the full name in the Cube Build Settings (I know that the web
page does not allow the full server\instance to be typed correctly).

2. Your repository is it a SQL db or MDB? If SQL ensure that the SSP account
has full access to the database and that as per the article you linked below
your provider connection string is corect (use the Test button). If using
MDB, try using SQL as I'm not sure the impact of the filee share on a cluster.

3. With your accounts used; if your PWA Web Application IIS application pool
account is different to the SSP account (I assume so) try giving that
permissions to the SSAS and repositories manually as you already have with
the SSP account.

4. Finally check your installed components, make sure you download and
install all the SQL features from the December 2008 feature pack
(http://www.microsoft.com/downloads/...d5-013f-49bc-9fc7-77dede4bb075&displaylang=en),
and ensure that you have all of the following installed:
· Microsoft Core XML Services
· Microsoft SQL Native client
· Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider
· Microsoft SQL Server 2005 Backward Compatibility Components
· Microsoft SQL Server 2005 Management Objects Collection


I hope that helps

 

[Brant Flick]

Martin,

After some checking, I had setup my DSO \ RemoteRepositoryConnectionString
using a SQL 10.0 client instead of 9. I was doing some testing with SQL 2008
and didn't notice that it had selected by default.

Time to go back and clean up permissions that I added during testing!

Thanks,

Brant