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:-
- Error="Analysis Services session failed with the following error: Failed to process the Analysis Services database OLAPCube on the SQLServername server. Error: OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.; 08001.
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 'EPMPilotMicron', Name of 'Task Is Milestone' was being processed.
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 "server1PS2007". 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 server1PS2007). 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