Project Server 2007 and SQL Server 2008 – Error Messages With Data Analysis Cubes(OLAP)

****Update**** – The December 2008 release of the Feature pack for SQL Server 2005 at http://www.microsoft.com/downloads/details.aspx?FamilyID=536FD7D5-013F-49BC-9FC7-77DEDE4BB075&displaylang=en means that loading the Management Objects Collection from this release will resolve the problems outlined below. 

*** Update *** There is now an official document that says some of this and more – See my latest blog for details – http://blogs.msdn.com/brismith/archive/2008/11/21/project-server-2007-technet-document-now-live-for-using-sql-server-2008.aspx

 I haven’t seen the official support announcement for Project Server 2007 and SQL Server 2008 (*** Update – http://technet.microsoft.com/en-us/library/cc197379.aspx – thanks Husain. but keep reading as this doesn’t fix the OLAP issues***) or the TechNet documentation but they should not be far away – but I have been trying to build cubes on my servers and seeing issues.  I have had no problem at all with SQL Server 2008 as a database engine but couldn’t build a cube.  Also many colleagues had no issues with OLAP on 2008!  It can’t just be me can it?  My farm was a simple two server affair – one App/WFE and a separate SQL Server.  The main error I was getting was:

Failed to connect to the Analysis Services server brismithSQL08. Error: Cannot connect to Analysis Services version ‘10.0.1600.22’ (or Analysis Services version ‘10.0.1763.0’ if you have loaded the Cumulative Update 1 for SQL Server 2008)

This error looked like one fixed in CU3 for SQL Server 2005 and relates to the 2005 Analysis Management Objects not being able to connect to SQL Server 2008 Analysis Services.  I decided to load the latest Cumulative Update for SQL Server 2005 which would also contain this fix – so went for CU10.  The strange thing was that on my x64 application server I couldn’t load the x64 hotfix as it said nothing to fix – but the x86 one was happy to load – and resolved my issue!  Digging deeper I established that the thing it upgraded was client tools that I had installed with the Business Data Catalog Editor which comes with the SharePoint SDK (and contains SQL Express) – and is x86 only.  So going backwards I tried other alternatives.  Loading the client tools from a full SQL Server 2005 installation worked.  If you don’t have these to hand another option (without needing to load the DB engine of SQL Express) is to load the Microsoft SQL Server 2005 Express Edition Toolkit Service Pack 2 and then the CU10 hotfix.  Hopefully the official documents will give a cleaner way to achieve this but for now this may help unblock some tests, so thought I would share.

Along the way I also discovered some other “gotchas”.  As my repository was in SQL Server 2008 I needed to load the 2008 version of the Native client from the Microsoft SQL Server 2008 Feature Pack, October 2008.  But you also need to have the 2005 version as it is a pre-req for the Management Objects.

If you haven’t installed the Decision Support Objects from the Backwards Compatibility Components expect a very clear error:

Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: The Application Server needs to have Analysis Services DSO Component installed.

If you don’t have the native client (or the right native client) expect this error:

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

And finally if you don’t have the Management Objects Collection (for Analysis Management Objects – AMO) then expect this one:

Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server BriSmithSQL08. Error: ActiveX component can’t create object

These errors assume that all permissions are set correctly and the setting of these does not differ from the 2005 instructions.  You may also see these errors in other circumstances too – I’ve just noted some obvious occurrences based on missing components.

I’m guessing my colleagues that have had no problems may have been on a single server – or at least had extra components loaded on the application server which meant the AMO was already at the required level.  I was just unlucky…

Useful links