Just when I thought I'd seen nearly all of the error messages possible when building a cube along comes another one. And the longest yet! I won't bore you all with the full error message but the key piece is:-
and so it goes on for another 3 or 4 pages...
The reason this happened was that the customer has the Project Server databases on a named instance of SQL - lets call this "server1\PS2007". When provisioning the PWA site from their application server they used an alias for the instance - lets call this "SQLServername". Now when they ask for a cube build the servername that gets passed over to Analysis Services for use when connecting back to the Reporting database is SQLServername (and not the server1\PS2007). But the Analysis Serices server does not have the alias set - so does not recognize SQLServername - hence the error. Resolution is reasonably straightforward - just make the alias available to the Analysis Services machine and all is good!
*** Update *** For a slight variation on this regarding 32 and 64 bit aliases - see http://blogs.msdn.com/brismith/archive/2009/10/08/project-server-2007-another-olap-cube-building-gotcha-is-your-alias-32-or-64-bit.aspx
Technorati Tags: Project Server 2007
Hi Bran we get a similar error - but Im not running sql instances. Any suggestions on how we can debug?
Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to process the Analysis Services database p12_new on the srd-mpsepm server. Error: OLE DB error: OLE DB or ODBC error: Cannot open database "ProjectServer_Reporting" requested by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Project Reporting data source', Name of 'Project Reporting data source'.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'p12_new', Name of 'Task Scope Change Status_Task' was being processed.
This sounds like the account running your Analysis Services does not have data reader access to the ProjectServer_Reporting database. Hopefully adding that access should resolve this issue. It could however still be related to the above scenario if you used an alias for your SQL Server even if it isn't a named instance - if the Analysis Services machine could not resolve that alias to the real server name.
I get the same message:
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'XXXX', Name of 'XXXX'.
I am trying to deploy a cube that reads from a DB2 database on AIX using the Microsoft DB2 provider. I can connect from the client or server with the providers, it is just during the process of deployment that I get the error.
I was brought up on AIX, but haven't touched it or DB2 for a while. In my scenario it was a name resolution problem so can all the machines you are deploying to connect through to the AIX server OK?
Brian, I have the exact error as you described above, and I am confident that the alias is my issue. Your fiix says to "just make the alias available to the Analysis Services machine and all is good!" how do I do this? Thanks
You can set this with the cliconfg tool. So on the Analysis Services server hit Start, Run, cliconfg and enter. In the tool on the alias tab click Add, then enter the connection details - so select TCP/IP and then enter the SQL Server name name in the format servername\instancename. You should be able to leave the Dynamically determine port option checked as the SQL Browser should be able to identify the port automatically. Then click OK. Your AS server should then be able to find the db server using the alias - the same as the SharePoint server does. The ironic thing is this even breaks if the AS and DB server are the exact same box - but they just don't know the name SharePoint is using to identify it.
I hope this helps.
Brian, the cliconfg is what I needed. Thanks alot!!
I've the same problem, but connecting to an Oracle database. I solved it by changing on the data source de Impersonan Information to DEFAULT and saving the password in the connection string manager.
Thanks for all !
I hope this helps someone.
Thanks Pepe. That worked for me. I had previouslt had Use the credentials of the current user selected.
"This sounds like the account running your Analysis Services does not have data reader access to the ProjectServer_Reporting database. Hopefully adding that access should resolve this issue. It could however still be related to the above scenario if you used an alias for your SQL Server even if it isn't a named instance - if the Analysis Services machine could not resolve that alias to the real server name"
The solution worked like a charm....
That is really pathetic.. not resolved with 2010.. same problem here and thank you very much for resolving mine with your answer !!!
I think it saved me a lot of time!
You are right that this is still a problem for 2010 Thoams - I'm not sure how easy it would be to discern if the servername was really an alias and to pass over the real name - but glad you found this workaround.
Just wanted to say thanks! I wish I had found this article 6 hours ago.