OLAP Cube Build Failure

[Kevin]

Because I continue to fail to build the OLAP cube data I thought I would try
to understand how the build request is being executed.

Correct me where I go astray as the error message doesn’t help me understand
which user needs which authority at which connection point.

Error: Error="Analysis Services session failed with the following error:
Failed to connect to the Analysis Services server DHRM-DEV-SQL. Error: Your
permissions on the server computer do not allow you to administer this
Analysis server.

Error: “

Push button for Cube Build on Project Server 2007 box
• Uses the Microsoft Project Server Queue Service
• Connects to the Analysis Services server specified in the Cube Build
configuration
• Uses the SSP user ( in this case)

DSO on the SQL 2005 Server
• SQL Server Analysis Services (MSSQLSERVER) service receives the request
• SQLServiceAccount is the account used on this service
• SQLServicesAccount has db_reader privileges on the projectServer_reporting
database
• Uses the DSO\RepositoryConnectionString and Remote
RepositoryConnectionString to talk to the Analysis Services.

Analysis Services on the SQL 2005 Server
• Uses the SQLServer2005MSOLAPUser$DHRM-DEV-SQL$MSSQLSERVER group with
db_owner privileges to the Analysis_Repository database.
• SSP, being a member of the
SQLServer2005MSOLAPUser$DHRM-DEV-SQL$MSSQLSERVER writes to the
Analysis_Repository database.

Local Machine Admins
• SSP is a local admin on the SQL 2005 server

Environment:
DEV-SQL server running SQL Server 2005 SP2
USLAF-PRJ07 server running Project 2007 + MOSS 2007 + Server 2008

 

[Kevin]

I should mention that SSP is a local admin on the SQL 2005 server, SSP is
also part of the OLAP group and an administrator for the Analysis Services.

 

[Ben Howard]

Is your AS on a separate server to the SQL databases (it looks like it from
the server name) - and is there a single instance of AS there or multiple
instances?
--
Thanks, Ben.

Please rate this post if it helped.
http://appleparkltd.spaces.live.com/

 

[Kevin]

Single instance and it is on the SQL 2005 box.

 

[Gordon]

Hi Kevin,

Have you tried the option with the repository file. Does it fail as well?

Cheers,

 

[Kevin]

Not sure I understand what option you are talking about.

 

[Gordon]

There are two options to configure Data analysis for SQL 2005 documented by
Microsoft.
http://technet.microsoft.com/en-us/library/cc197552.aspx

Option 1: Create the repository by using a SQL Server 2000 Microsoft Jet
database

Option 2: Create the repository by creating a SQL Server 2005 database

Have you tried both

 

[Ben Howard]

I'm sure it's not, but let's just make sure it's not a naming issue.

You said the SQL server was called DEV-SQL, but in the error message it
states DHRM-DEV-SQL. Please confirm you have the correct names configured.
--
Thanks, Ben.

Please rate this post if it helped.
http://appleparkltd.spaces.live.com/

 

[Kevin]

I have executed on the second option as outlined in the "Implementing and
Administering" guide and MSFTs site.
Thanks.

 

[Kevin]

A fair question. The reference and the server name are correct.
DHRM-DEV-SQL.

Is there a test I can run right from the Analysis Server to confirm
authority? Can I log in as the SSP user and execute some command?

Thanks.