Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

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

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

  • Comments 22

****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 

Leave a Comment
  • Please add 4 and 8 and type the answer here:
  • Post
  • Still seeing the first error (Application Server needs to have Analysis Services DSO Component installed), even though I have the native client, mgmt objects OLEDB and Backwards Compatibility features installed.  Mind you, this is on Windows 2008, and I did read that Microsoft decided to remove DSO control in XML 6.0.  From the following site:

    http://blogs.msdn.com/xmlteam/archive/2007/03/12/upgrading-to-msxml-6-0.aspx

    2.      DSO document(MSXML2.DSOControl.6.0) is not available with MSXML 6.0. This feature is not used by many customers.

    Example: When tried to create  MSXML2.DSOControl.6.0 object, the following error occurs:

    429:ActiveX component can't create object

  • Hi Chris,

    All of my testing is on Windows Server 2008 too.  Also we have some acronym overload as the DSO we need is Decision Support Objects from the Backward Compatibility feature - DSO in the MSXML world was Data Source Objects and has nothing to do with cube building.  So I am not sure why you see that error unless there is a permissions issue on your server.  Process Monitor may be able to track what is not being found.

    Best regards,

    Brian.

  • I ran SQL trace on AS, but nothing is shown.  Did the Process Monitor trace on my project server - gets to the point of checking my .NET security permissions then closes all threads down:

    ProjectServerOlapCubeGenerator.exe 4472 CreateFile C:\Windows\assembly\GAC_MSIL\Interop.Dso\12.0.0.0__71e9bce111e9429c\Interop.Dso.dll SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 ReadFile C:\Windows\assembly\GAC_MSIL\Interop.Dso\12.0.0.0__71e9bce111e9429c\Interop.Dso.dll SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 ReadFile C:\Windows\assembly\GAC_MSIL\Interop.Dso\12.0.0.0__71e9bce111e9429c\Interop.Dso.dll SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 CloseFile C:\Windows\assembly\GAC_MSIL\Interop.Dso\12.0.0.0__71e9bce111e9429c\Interop.Dso.dll SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 TCP Send MSSMOSSA02.wer.hshs.org:62927 -> wermosscl.wer.hshs.org:1627 SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 TCP Receive MSSMOSSA02.wer.hshs.org:62927 -> wermosscl.wer.hshs.org:1627 SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 Thread Create SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 TCP Disconnect MSSMOSSA02.wer.hshs.org:62927 -> wermosscl.wer.hshs.org:1627 SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 Thread Exit SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 CloseFile C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\security.config.cch SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 CreateFile C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\security.config.cch.4472.937465076 NAME NOT FOUND

    ProjectServerOlapCubeGenerator.exe 4472 CloseFile C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\enterprisesec.config.cch SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 CreateFile C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\enterprisesec.config.cch.4472.937465076 NAME NOT FOUND

    ProjectServerOlapCubeGenerator.exe 4472 CloseFile C:\Users\mossssp\AppData\Roaming\Microsoft\CLR Security Config\v2.0.50727.312\security.config.cch SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 CreateFile C:\Users\mossssp\AppData\Roaming\Microsoft\CLR Security Config\v2.0.50727.312\security.config.cch.4472.937465076 NAME NOT FOUND

    ProjectServerOlapCubeGenerator.exe 4472 Thread Exit SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 Thread Exit SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 Thread Exit SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 Thread Exit SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 Thread Exit SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 Thread Exit SUCCESS

    ProjectServerOlapCubeGenerator.exe 4472 Thread Exit SUCCESS

    I  had read a thread that before the encrypted connection is made, a call is made to crl.microsoft.com to check for revoked certs, but not sure if that could be part of it.  Thoughts?

  • Discovered that those errors are ambiguous to what is happening - just above those was a call to the .NET runtime engine (c:\windows\syswow64\mscoree.dll) which was the wrong version - it needed the 32-bit version as you have discerned in other posts.  

    I worked around this by installing SQL client tools from SQL 2005 install CD and patching with CU10.  After deleting the original AS database and re-creating using the SQL Native Client 10.0 in the DSO Repository/RemoteRepository fields, the cube build was successful.

  • Good news, and thanks for the feedback Chris.  It looks like we will get a revised XMO package in a new feature pack for SP3 of SQL Server 2005 sometime before the end of the year (timescale not guaranteed yet).

    Best regards,

    Brian.

  • From http://technet.microsoft.com/en-us/library/cc197379.aspx '...Note: Office Project Server 2007 supports

  • The writers have done a great job on revising the 2005 document to address 2008 – thanks Efren. 

  • Hi Brian,

    I started using visual studio 2008 to develop Project Server 2007 and I have some doubts with the credentials of web services. I find attached  an example of how I do it in Visual Studio 2005 and I think it would be like visual studio in 2008 but does not work.

    In Visual Studio 2005:

    wsresource.Resource myresouce = new wsresource.Resource();

    wsresource.Credentials = CredentialCache.DefaultCredentials;

    myresouce.Url = "http://server/pwa/_vti_bin/psi/resource.asmx";

    ¿In Visual Studio 2008:?

    WebSvcResource.ResourceSoapClient resource = new WebSvcResource.ResourceSoapClient();

    ¿resource.ClientCredentials.Windows.ClientCredential = CredentialCache.DefaultCredentials;?

    ¿resource.Url….?

  • Hi vserrano,

    If you add a web reference rather  than a service reference then things should work as they did in 2005.  From the Add Service Reference dialog choose the Advanced option at bottom left, and then there is the Compatibility option of Add Web Reference.

    I hope this helps,

    Best regards,

    Brian.

  • Thank Brian. I have more questions:

    Let me know if I can facilitate the formulas of the fields BCWS, BCWP, ACWP to see how the estimated Project.I attached the formulas that I have been able to intuit.But do not serve that many times the values do not match for Project. (eg

    40504.139712 should be in my calculation and departs 41313.36753450).

    The formulas that I was using for testing are:

    - Budgeted Cost of Work Scheduled =BCWS = Planned%

    Completed * Planned Cost  (All Resource Cost + Fixed Cost)

    * Budgeted Cost of Work Real cptr = BCWP =% Completed * Planned Cost (All Resource Cost + Fixed Cost)

    * Real Cost of Work Done CRTR = AWCP = ActualCost + ¿FixedCost?

  • Hi vserrano,

    The descriptions of all earned value fields are given in the Help File - if you click Help and search for Earned Value the fields are described quite thoroughly.  Most of these fields are based on a status date so this may be affecting your calculations.  

    Best regards,

    Brian

  • Even running SQL2008 and AS2008 on the same box as the Project Server, I hit big issues with this.  Eventually resolved by installing the 2005Expresstools and then SQL2005CU10 on the server, and everything then the cube built!

    What a 'mare!

  • Hi Ben,

    Yes, not a good story here.  Once SQL Server 2005 SP3 is out (maybe this month?), and a refresh of the Feature Pack then this will get much easier - and we will refresh our documentation.  Sorry for the pain.

    Best regards,

    Brian.

  • I tried following the latest Technet instructions and your tips here for a Project server that had previously been using SQL 2000 and was just migrated to a new SQL 2008 server. Even though I installed (several times) the versions from the SQL 2005 December 2008 feature pack, I received the "Provider cannot be found. It may not be properly installed." until I also installed the SQL 2008 feature pack version of the Microsoft SQL Server 2008 Native Client. The articles suggest that this isn't needed, but it helped me. This is for a 32bit App server on W2K3 connecting to a x64 2008 SQL server on Win2008.

  • Hi James,

    I think the native client for 2008 comes in to play when you have your repository in SQL 2008. Sorry the article is a little misleading, and thanks for the feedback.

    Best regards,

    Brian.

Page 1 of 2 (22 items) 12