locked
Project Server 2010 with SQL Server 2005: OLAP Cube Failure RRS feed

  • Question

  • Hi,

     

    I am having some issues creating the OLAP cubes in our newly migrated Project Server 2010 instance.

    the path I followed for migration was the DB Attach method, so I installed a new server from scratch for the installation.

    As I am using SQL 2005 as an DB Engine (including Analysis Services), I have tried building the cube both with the 2005 and 2008 versions of the Native Client installed, as well as only with the 2005 version, and only with the 2008 version - neither has worked.

    I am assuming that SQL 2008 Native Client has to be installed, as that is what the prerequisites installer installs before you install SharePoint.  In this case, this is the error that I am getting:

    Failed to build the OLAP cubes. Error: Failed to process the Analysis Services database pwa on the ptasql1 server. Error: OLE DB error: OLE DB or ODBC error: Class not registered.

    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 'pwa', Name of 'Resource Type' was being processed.

    Server: The operation has been cancelled.

     

    I have ensure the following permissions is configured:

     

    · The Analysis Services service account has DB_DATAREADER permissions on the ProjectServer_Reporting database

    · The SharePoint Farm Service account as well as the Project Server Administrator account has administrative permissions in the Analysis Services instance

    Is there anything else you guys think I can try? 

     

    Thanks...

    Nico

     

    Sunday, June 20, 2010 3:50 PM

Answers

  • HI Everyone,

     

    Just some feedback on this.  I got some advice from another Microsoft representative to edit the Data Source for the cube in my Analysis Services configuration - the idea was to change this to the SQL 2005 OLEDB provider as opposed to the SQL 2008 OLE DB provider.  The Problem was that this would not work, as everytime you rebuilt the cube the cube building process seems to revert it back to the SQL 2008 provider and the process fails again.

     

    This made me think a bit, and I installed the SQL 2008 Native Client on my SQL 2005 Database Engine and Analysis Services server.  This enabled SQL Analysis Services to connect to the data source using the SQL 2008 Native Client, which it seems to want to do.

     

    Now my cube is building successfully.

    • Marked as answer by Sriram E Wednesday, June 30, 2010 3:11 AM
    Tuesday, June 29, 2010 9:16 PM

All replies

  • Hello Nico,

    Make sure that the service accounts are loaded in the local group MSA$OLAP...   (I cannot remember the exact name of the NT group, but it should have OLAP or something similar in the name)

    Michael Wharton, MBA, PMP, MCT

     


    Sincerely, Michael Wharton, MBA, PMP, MCT, MCSD, MCSE+I, MCDBA www.WhartonComputer.com
    Monday, June 21, 2010 8:03 PM
  • The SSAS 2008 AMO needs to be installed even if you're using SSAS 2005. Try that.
    Tuesday, June 22, 2010 6:31 AM
  • Hi Michael - I have confirmed that they are located in the group SQLServer2005MSOLAPUser$PTASQL1$MSSQLSERVER.

     

    Hi Chad - the SSAS 2008 AMO is installed on the box - still the same error...

    Tuesday, June 22, 2010 8:19 AM
  • Hello Nico,

    The error message is saying that it can't connect.  You must keep in mind that when you are working with analysis server, it is also communicating with the SQL server databases, so be sure that both sides have enough permissions.  

    Some other places to look to see if they are talking are the SQL log files.  I also like turning on SQL Server trace and see if OLAP is getting request to SQL server.

    Also, be sure that the right ports are open, if SQL server is on one server and the OLAP server is on another.

    Michael Wharton, MBA, PMP, MCT


    Sincerely, Michael Wharton, MBA, PMP, MCT, MCSD, MCSE+I, MCDBA www.WhartonComputer.com
    Tuesday, June 22, 2010 1:22 PM
  • OLE DB or ODBC error: I Suspect that Analysis server was unable to contact SQL server.

    So add TCP/IP in the client configuration. (RUN -->Cliconfg -->Add TCP/IP)

    A connection could not be made to the data source with the DataSourceID of 'Project Reporting data source', Name of 'Project Reporting data source'.

    Do you have your Reporting database on different server? If yes, make necessary port open between AS and SQL.

    Check the permission on your Project databases. Check the SQL & Event log.

    An error occurred while the dimension, with the ID of 'pwa'

    Can you remove all the dimensions from OLAP tables, then try to build the cube?


    Thanks Sriram E - MSFT Enterprise Project Management
    Wednesday, June 23, 2010 2:14 PM
  • HI Everyone,

     

    Just some feedback on this.  I got some advice from another Microsoft representative to edit the Data Source for the cube in my Analysis Services configuration - the idea was to change this to the SQL 2005 OLEDB provider as opposed to the SQL 2008 OLE DB provider.  The Problem was that this would not work, as everytime you rebuilt the cube the cube building process seems to revert it back to the SQL 2008 provider and the process fails again.

     

    This made me think a bit, and I installed the SQL 2008 Native Client on my SQL 2005 Database Engine and Analysis Services server.  This enabled SQL Analysis Services to connect to the data source using the SQL 2008 Native Client, which it seems to want to do.

     

    Now my cube is building successfully.

    • Marked as answer by Sriram E Wednesday, June 30, 2010 3:11 AM
    Tuesday, June 29, 2010 9:16 PM
  • Hi Nico,

    A good lesson learnt.  Perhaps you should blog this so that it is searchable, as these forums don't tend to be.  I've have similar problems with PS2007 and SQL 2008 in the past using the correct client, and have blogged this and found it invaluable when I've been on a CU site with a similar issue, I googled/binged the issue and found my own blog - I had forgotten about the issue in the 1st place!


    HTH, Ben [MVP]
    Tuesday, June 29, 2010 10:43 PM
  • Hi Ben,

    Indeed - I am busy getting my blog online again and will start blogging.  Loads of new things to learn about now that 2010 is here... :)

    Wednesday, June 30, 2010 8:17 AM