none
Project Server 2007 - "Assignment Timephased" cube error - Null value is eliminated by an aggregate or other SET operation

    Question

  • Hi there,

    I am having a big problem at a client. The person who installed and administered a project server 2007 cube has left the company and I am supposed to replace him. The problem is that he did not present me at all the cube from project server 2007 and I am getting cube failures or long processing times and after that failures.

    Last manual processing of "Assignment Timephased" cube finished after 32 hours with only 540.000 lines (?!?!?) processed and with the error bellow.

    Internal error: The operation terminated unsuccessfully.

     OLE DB error: OLE DB or ODBC error: Warning: Null value is eliminated by an aggregate or other SET operation

    Can you please help me with an advice or a hint ? That is the error and it is strange to have such an error since the date gets into the database through a project server interface which validates the data first.

    The processing time and the fact that I can not get it built without an error is killing me.

    Assignment Timephased error



    Friday, February 24, 2012 9:41 PM

All replies

  •  

    Hello Teddy,

    1. Execute following query on SQL server master database and share the result

     

    DBCC tracestatus (4121,4101)

    2. Execute SP_updatestats  command on Project Server Published and reporting database. This command will update database statistics which helps to reduce cube build time

    Are you using SQL Server 2005 or 2008?


    Hrushikesh Deshpande – Senior EPM Infrastructure Consultant, www.DeltaBahn.com

    Friday, February 24, 2012 10:41 PM
  • Hi Hrishid,

    1. 0 for tracestatus

    4121 0 0 0
    4101 0 0 0

    2. Statistics updating is in progress while I am writing this message

    3. Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) which was just installed 2 weeks ago after we saw the problems with the cube 


    Able was I ere I saw Elba

    Saturday, February 25, 2012 6:15 PM
  • Hello Teddy,

     

    Longer it takes to complete SP_updatestats, indicates DB statistics was much needed

    We need to enable those trace flags.

    Execute following query on SQL server master databases and enabled trace flags 4121 & 4101

    DBCC TRACEON (4101, 4121, -1)

    Use following command to verify the status

    0 Indicates Disable

    1 Indicates enabled

    Try building OLAP cube

    Also above settings will be active until SQL server is restarted.

    To configure the settings permanently we need add trace flags in SQL server start up parameters

    1.   Launch the SQL Server Configuration Manager
    2.   Right-click on the SQL Server entry on the rightmost pane and choose Properties
    3.   Select advanced tab
    4.   Change the Start-up Parameters entry
    5.   Add the following to the end of the list: ";-T4101;-T4121"
    6.   Click OK

    Review the detailed information from http://support.microsoft.com/kb/942444  regarding trace flags

    To keep DB statistics and indexes updated refer following SQL server database maintenance plan specific to project server

    http://technet.microsoft.com/en-us/library/cc973097(office.12).aspx


    Hrushikesh Deshpande – Senior EPM Infrastructure Consultant, www.DeltaBahn.com


    Saturday, February 25, 2012 7:51 PM
  • Unfortunately I can not notice any improvement. After 3 hours of waiting the processing status of OLAP Cube remains at 50% in the PWA.

    Able was I ere I saw Elba

    Tuesday, February 28, 2012 10:38 AM
  • Did you execute sp_updatestats query on reporting database?


    Hrushikesh Deshpande – Senior EPM Infrastructure Consultant, www.DeltaBahn.com

    Tuesday, February 28, 2012 11:06 AM
  • Stats are up to date in each database. Traces are also on. There are 7 hours since I started the cube and it is still processing.


    Able was I ere I saw Elba

    Tuesday, February 28, 2012 5:06 PM
  • Hello Teddy,

    DO you see any error message like tempdb is full? Do you have free diskspace available on SQL data drive?

    How big is your reporting database? any idea in the past what was the timeframe of cube building job? Before you observed long duration.

    Try to reinstall following components on Project Server

    1.Microsoft SQL Server Native Client

    2.Microsoft SQL Server 2005 Management Objects Collection

    3.Microsoft SQL Server 2005 Backward Compatibility Components

     http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11988

    P.S. If get updated version already installed, don't install.


    Hrushikesh Deshpande – Senior EPM Infrastructure Consultant, www.DeltaBahn.com

    Tuesday, February 28, 2012 5:32 PM
  • The Reporting has 6Gb and the Published 14Gb. All database server was re-installed recently and the data is now on SQL Server 2008 R2. TempDb has 12Gb, is not reported as full and there is plenty of space left (over 1Tb).

    Unfortunately as I said there is no info that this cube was ever built before. The person who left the company told me that at the begging the building process took over 4 hours but I am not sure whether to trust him or not since he never could prove me that the cube was at least once built. 


    Able was I ere I saw Elba

    • Proposed as answer by Aqueel Ahmed Tuesday, April 17, 2012 11:13 AM
    • Unproposed as answer by Aqueel Ahmed Tuesday, April 17, 2012 11:13 AM
    Tuesday, February 28, 2012 7:01 PM
  • Hi Teddy,

    I am having the same kind of problem with cube build getting failed with in an hour with the error in ASSIGNMENT TimePhased cube.

    Scenario : Can build the cube in a single minute with no basic fields ( work , cost , Baseline etc). Can build the cube with custom fields selected. But when added WORK , COST , Baseline etc cube gets struck and throws assignment error.

    I Suspect the issue is with some corrupted tasks in few project plan for which you see "NULL" value for Resource owner ID. You can find those tasks by running the following command using Published Database.

    SELECT     dbo.MSP_ASSIGNMENTS.TASK_NAME, dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_ASSIGNMENTS.RES_UID_OWNER, dbo.MSP_ASSIGNMENTS.RES_UID,
                          dbo.MSP_ASSIGNMENTS.ASSN_START_DATE, dbo.MSP_ASSIGNMENTS.ASSN_FINISH_DATE
    FROM         dbo.MSP_ASSIGNMENTS INNER JOIN
                          dbo.MSP_PROJECTS ON dbo.MSP_ASSIGNMENTS.PROJ_UID = dbo.MSP_PROJECTS.PROJ_UID
    WHERE     (dbo.MSP_ASSIGNMENTS.RES_UID_OWNER IS NULL)

    this will list the tasks having Resource Owner ID as NULL. Strange to see most of them will be Milestones / and wiered part could be with work values too.

    I believe they should not be listed into Assignment table cos they are milestones.

    Well, currently i am working with my project managers to fix such corrupted tasks by assigning it to a resource - > publish - > removing the resource - > re- Publish .

    Hope fully, once done should fix my cube build faster.

    Never thought installing SP1 will be a night mare !!!!! Let me know if it helps you.

    Tuesday, April 17, 2012 11:22 AM
  • The problem is still pending and there is not much time to allocate to this problem for the moment. I have changed the schedule and I run now the cube once a week during weekends. For the first runs it took 75 hours but it was processing all the way. I have created some indexes based on the execution queries of Assignment TimePhased and I manage to obtain a "fantastic" improvement and it now takes "only" 45 hours. But the problem is far from being solved.

    Able was I ere I saw Elba


    Thursday, May 03, 2012 9:22 AM