none
OLAP database build error - Project server 2010 RRS feed

  • Question

  • Error message:

    ===== Initiating OLAP database build process =====
    [6/14/2012 2:11 PM] Cube build request message has been added to the Project Server queue
    ===== Verifying and running pre-build server event handler =====
    [6/14/2012 2:11 PM] Verifying and running pre-build server event handler
    ===== Determining database and OLAP database structure =====
    [6/14/2012 2:11 PM] Cube build initialization started
    [6/14/2012 2:11 PM] OLAP database and cube structure was determined successfully
    ===== Building database and cubes =====
    [6/14/2012 2:11 PM] Cube build session started
    ===== Process Completed =====
    [6/14/2012 2:11 PM] Failed to build the OLAP cubes. Error: Failed to create the Olap database: DevelopmentOLAP. Error: Either the 'DOMAIN\SRV_SPFARM' user does not have permission to create a new object in 'SPSQLCLSTR', or the object does not exist.

    I verified that the SRV_FARM account has dbcreator and secadmin permission on the SQL server.  The sharepoint/project servers all use sql alias called SP2010DB, however when I tried that name for building it errored saying it couldnt even connect to a SQL server.  So I'm pretty sure I have the write SQL server name listed and security set.  Any help appreciated.


    -Chad

    Thursday, June 14, 2012 6:24 PM

Answers

  • Hi Chad,

    This is the first time or was it working before?

    Did you add 'DOMAIN\SRV_SPFARM'  as Analysis Server Administrator using SQL Server Analysis Services


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    Thursday, June 14, 2012 7:05 PM
    Moderator
  • also assign Read permisisons to Analysis Service account on Project Server Reporting database


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    • Marked as answer by philldogger Thursday, June 14, 2012 9:49 PM
    Thursday, June 14, 2012 9:20 PM
    Moderator
  • I think, you should add alias configuration on SQL server

    Try editing database source pointing to  alias entry and process the cube from AS

    data soruce will be re-updated if you try to build the cube from PWA


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    • Marked as answer by philldogger Thursday, June 14, 2012 9:48 PM
    Thursday, June 14, 2012 9:45 PM
    Moderator
  • OK its working now...thanks for the help Hrishi.  I had set the alias on the SQL/AS server, then give the AS service account read access to the different project reports DBs (we have multiple PWAs with report DBs).  Build from AS was succesful and also from PWA too.

    -Chad

    • Marked as answer by philldogger Thursday, June 14, 2012 9:48 PM
    Thursday, June 14, 2012 9:48 PM

All replies

  • Hi Chad,

    This is the first time or was it working before?

    Did you add 'DOMAIN\SRV_SPFARM'  as Analysis Server Administrator using SQL Server Analysis Services


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    Thursday, June 14, 2012 7:05 PM
    Moderator
  • Hi there,

    Have you seen this TechNet link for configuring SQL AS:

    http://technet.microsoft.com/en-us/library/ee662108#section4

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com

    Thursday, June 14, 2012 7:27 PM
    Moderator
  • yes i was missing the farm account in Analysis Server Administrator and once I added it I can see that it now created the database, but it now has a new build error:  (windows firewall is turned off...fyi)

    ===== Initiating OLAP database build process =====
    [6/14/2012 3:52 PM] Cube build request message has been added to the Project Server queue
    ===== Verifying and running pre-build server event handler =====
    [6/14/2012 3:52 PM] Verifying and running pre-build server event handler
    ===== Determining database and OLAP database structure =====
    [6/14/2012 3:52 PM] Cube build initialization started
    [6/14/2012 3:52 PM] OLAP database and cube structure was determined successfully
    ===== Building database and cubes =====
    [6/14/2012 3:52 PM] Cube build session started
    ===== Verifying and running post-build server event handler =====
    [6/14/2012 3:52 PM] Verifying and running post-build server event handler
    ===== Processing OLAP database =====
    [6/14/2012 3:52 PM] Process OLAP database session started
    ===== Process Completed =====
    [6/14/2012 3:53 PM] Failed to build the OLAP cubes. Error: Failed to process the Analysis Services database DevelopmentOLAP on the SPSQLCLSTR server. Error: OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [53]. ; 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 'DevelopmentOLAP', Name of 'Timesheet Line Status' was being processed.
    Server: The operation has been cancelled.

     


    -Chad

    Thursday, June 14, 2012 7:58 PM
  • Good to know that we are progressing

    Are you using SQL server alias?

    You can check connectivity using UDL file

    Create text file and rename extension of the file from .txt to .udl

    Double click on the file and select provider tab

    And Select SQL server analysis services (OLEDB 10.0)

    Enter analysis server name and click on test connection


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    Thursday, June 14, 2012 8:04 PM
    Moderator
  • All the sharepoint/project servers use a sql alias called SP2010DB as mentioned in my OP.  From what server do you want me to run the udl test?

    -Chad

    Thursday, June 14, 2012 8:07 PM
  • Have you added the SQL alias (using both x32 and x64 cliconfg) on the SQL Server \ SQL AS Server also?

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com

    Thursday, June 14, 2012 8:12 PM
    Moderator
  • My bad, I missed that part

    Try UDL from Project Application Server


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    Thursday, June 14, 2012 8:15 PM
    Moderator
  • 32bit alias is set on all sharepoint/project servers.  x64 is not.

    32bit and x64 alias are not set on the actual SQL/AS server.  Nervous to set an alias on the actual SQL server...is that necessary?


    -Chad

    Thursday, June 14, 2012 8:17 PM
  • Here is the udl test, and when i hit Yes to continue it is successful.  If use the sql alias as the datasource it fails altogether.


    -Chad

    Thursday, June 14, 2012 8:22 PM
  • Thats ok.

    Are you able to list databases by expanding catalog to use?


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    Thursday, June 14, 2012 8:24 PM
    Moderator
  • yes


    -Chad

    Thursday, June 14, 2012 8:27 PM
  • Thats good.

    Is windows firewall disabled on Database server?


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    Thursday, June 14, 2012 8:30 PM
    Moderator
  • Firewall had appropiate rules in place for SQL, however I disabled it and got the same error running the build.

    The sharepoint/project servers all have 2008 ADOMD.net installed, however the SQL/AS servers do not.  Does that matter?  And what about the sql aliases being used?  Did you see my reply to Paul:

    __________________

    "32bit alias is set on all sharepoint/project servers.  x64 is not.

    32bit and x64 alias are not set on the actual SQL/AS server.  Nervous to set an alias on the actual SQL server...is that necessary?"


    -Chad

    Thursday, June 14, 2012 8:42 PM
  • I think since UDL connection is working you are ok with alias stuff. I will let Paul to re-confirm that.

    Error: Failed to process the Analysis Services database DevelopmentOLAP on the SPSQLCLSTR server. Error: OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server.

    Which server is this "HYT00"?


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    Thursday, June 14, 2012 8:56 PM
    Moderator
  • we dont have a server with that name....so i'm thinking that error name shows up for any environement.

    -Chad

    Thursday, June 14, 2012 8:58 PM
  • When you have configured alias, did you select TCP\IP and Named instance? TCP\IP listed on top

    Let us try to process the cube from AS

    Connect to AS, select the cube DevelopmentOLAP , right click and select process.


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    Thursday, June 14, 2012 9:02 PM
    Moderator
  • alias on sharepoint/project server is only using TCPIP and not Named Instance.  Only using 32bit cliconfg as well.

    Processing the cube from the SQL/AS server gives this error:

    OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [53]. ; 08001.


    -Chad


    • Edited by philldogger Thursday, June 14, 2012 9:15 PM
    Thursday, June 14, 2012 9:14 PM
  • Did you check permission on reporting database for 'DOMAIN\SRV_SPFARM'  account?


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    Thursday, June 14, 2012 9:18 PM
    Moderator
  • also assign Read permisisons to Analysis Service account on Project Server Reporting database


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    • Marked as answer by philldogger Thursday, June 14, 2012 9:49 PM
    Thursday, June 14, 2012 9:20 PM
    Moderator
  • Do you see any SQL server error logs related to OLAP cube build?


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    Thursday, June 14, 2012 9:21 PM
    Moderator
  • I looked at the properties of the Project Reporting datasource in the cube and it is trying to use the alias SP2010DB which will certainly fail to run on the SQL/AS server since that server itself doesnt have any aliases set for it.  Like I said before however I'm nervous to set an alias on the actual SQL server.  Will this break any datasources we that use SPSQLCLSTR?


    -Chad

    Thursday, June 14, 2012 9:35 PM
  • I dont thik so, since alis is an additioanl entry for exisiting SQL server

    UDL connection works for both the server right?


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    Thursday, June 14, 2012 9:37 PM
    Moderator
  • the UDL test was run from the project/sharepoint server.  It was successful using SPSQLCLSTR, and unsuccessful using the alias SP2010DB most likely because we use kerberos and the service accounts dont have delegation to the alias name SP2010DB, just SPSQLCLSTR

    -Chad

    Thursday, June 14, 2012 9:41 PM
  • I think, you should add alias configuration on SQL server

    Try editing database source pointing to  alias entry and process the cube from AS

    data soruce will be re-updated if you try to build the cube from PWA


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    • Marked as answer by philldogger Thursday, June 14, 2012 9:48 PM
    Thursday, June 14, 2012 9:45 PM
    Moderator
  • OK its working now...thanks for the help Hrishi.  I had set the alias on the SQL/AS server, then give the AS service account read access to the different project reports DBs (we have multiple PWAs with report DBs).  Build from AS was succesful and also from PWA too.

    -Chad

    • Marked as answer by philldogger Thursday, June 14, 2012 9:48 PM
    Thursday, June 14, 2012 9:48 PM
  • Glad I could help

    Thanks for being patient


    Hrishi Deshpande – DeltaBahn Senior Consultant
    Blog | < | LinkedIn

    Thursday, June 14, 2012 9:50 PM
    Moderator