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

Ask a question

Friday, February 24, 2012 9:41 PM

0
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.
- Edited by Teddy Carebears Friday, February 24, 2012 9:44 PM
- Reply
- Quote

All Replies

Friday, February 24, 2012 10:41 PM

0

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
- Reply
- Quote
Saturday, February 25, 2012 6:15 PM

0

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
- Reply
- Quote
Saturday, February 25, 2012 7:51 PM

0
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
- Edited by Hrishi.Deshpande Saturday, February 25, 2012 7:52 PM updated
- Reply
- Quote
Tuesday, February 28, 2012 10:38 AM

0

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
- Reply
- Quote
Tuesday, February 28, 2012 11:06 AM

0

Did you execute sp_updatestats query on reporting database?

Hrushikesh Deshpande – Senior EPM Infrastructure Consultant, www.DeltaBahn.com
- Reply
- Quote
Tuesday, February 28, 2012 5:06 PM

0

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
- Reply
- Quote
Tuesday, February 28, 2012 5:32 PM

0

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
- Reply
- Quote
Tuesday, February 28, 2012 7:01 PM

0
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
- Reply
- Quote
Tuesday, April 17, 2012 11:22 AM

0

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.
- Reply
- Quote
Thursday, May 03, 2012 9:22 AM

0
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
- Edited by Teddy Carebears Friday, June 22, 2012 4:32 AM
- Reply
- Quote