none
Cumulative Timephased Data in PS 2013 DB - wrong, or just misunderstood? RRS feed

  • Question

  • PS 2013 July 2014 CU (on premise)

    I'm trying to query the database to get a portfolio level burndown report - but the data I'm getting back from the new cumulative tables doesn't match what's in the project.  In fact, the numbers don't make any sense at all - the BaselineRemainingCumulativeWork should never go UP from one month to the next, right? (I guess it could if you baselined additional tasks at a later point, but that's not the case with this project, and it doesn't show up that way in the burndown report in PS) Has anyone else tried writing a query against this?  Am I just missing something?  I've read the Field References and tried this using both the Assignment and Task Tables, but the data is not matching the data in the burndown report for a single project, nor does it actually make mathmatical sense.  The numbers are off by thousands!

    Here's the super basic query that I would hope would give me the right information. (it's just pulling baseline work, and it's filtered to a single project right now to make it run faster while I'm troubleshooting)

    SELECT
    MSP_EpmProject_UserView.ProjectName,
    MSP_EpmTaskBaselineCumulativeByDay_UserView.TaskBaselineRemainingCumulativeWork,
    MSP_EpmTaskBaselineCumulativeByDay_UserView.BaselineNumber,
    MSP_EpmTaskBaselineCumulativeByDay_UserView.TimeByDay

    FROM
    MSP_EpmProject_UserView

    INNER JOIN
    MSP_EpmTask_UserView ON
    MSP_EpmTask_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID

    INNER JOIN
    MSP_EpmTaskBaselineCumulativeByDay_UserView ON
    MSP_EpmTaskBaselineCumulativeByDay_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID

    WHERE
    MSP_EpmTask_UserView.TaskIsActive =1 AND
    MSP_EpmProject_UserView.ProjectName like '%Project Name%' ANDMSP_EpmTaskBaselineCumulativeByDay_UserView.BaselineNumber =0

    Amy thoughts on this would be appreciated!


    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/



    • Edited by ElliJ Thursday, September 4, 2014 2:52 PM
    Wednesday, September 3, 2014 7:53 PM

Answers

  • Well, I can certainly confirm that there is a missing join in the view definition for the MSP_EpmAssignmentBaselineCumulativeByDay_UserView view - it appears to be missing the BaselineNumber join.

    Specifically, the WHERE statement after the CROSS JOIN is:

        A.ProjectUID = B.ProjectUID AND A.AssignmentUID = B.AssignmentUID AND B.TimeByDay <= A.TimeByDay

    it should also join A.BaselineNumber = B.BaselineNumber

    That said, if you test the data for the cumulative data on a project with only a single assignment, the data looks correct once you fix that join.  However, projects with multiple assignments (like ya do) seem to have the cumulative data multiplied by the number of assignments in the project.  I am opening a case with MS and requesting a hotfix.

    Hope that this helps someone out there.


    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/

    • Marked as answer by ElliJ Tuesday, September 16, 2014 5:19 PM
    Tuesday, September 16, 2014 5:19 PM
  • Hi ElliJ,

    I have not worked with the cumulative values before. But perhaps you need to exclude summarytasks? Perhaps that leads to the high values?

    Please update the thread when you get Feedback from Microsoft.

    Regards

    Christoph


    Christoph Muelder | Senior Consultant, MCTS, MCSE | SOLVIN information management GmbH, Germany

    • Proposed as answer by Christoph Muelder Thursday, September 18, 2014 6:41 PM
    • Marked as answer by ElliJ Thursday, September 18, 2014 7:17 PM
    Wednesday, September 17, 2014 1:11 PM

All replies

  • I've even simplified this by not joining it with any other tables and running it SQL Studio,  and the result is the same.  I'm beginning to think that the math used in the code to creae the view is not summing correctly...

    SELECT
    MSP_EpmTaskBaselineCumulativeByDay_UserView.ProjectUID,
    MSP_EpmTaskBaselineCumulativeByDay_UserView.TaskBaselineRemainingCumulativeWork,
    MSP_EpmTaskBaselineCumulativeByDay_UserView.BaselineNumber,
    MSP_EpmTaskBaselineCumulativeByDay_UserView.TimeByDay

    FROM
    MSP_EpmTaskBaselineCumulativeByDay_UserView

    WHERE
    ProjectUID like '%GUID%' AND

    MSP_EpmTaskBaselineCumulativeByDay_UserView.BaselineNumber=5

    ORDER BY
    MSP_EpmTaskBaselineCumulativeByDay_UserView.TimeByDay


    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/

    Wednesday, September 3, 2014 8:45 PM
  • I have more observations, though this whole thread seems to just be a conversation with myself...

    The taskbyday work is just wrong - it seems to "double" the work for the underlying assignments.  So, I've switched over to the AssignmentByDay table, and the truth is that I don't know how you would get a cumulative work total for TimeByDay, but the MS query which defines the cumulative tables in the 2013 database are certainly NOT working correctly.

    Is anyone else able to test this?  Anyone?  Bueller?


    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/

    Friday, September 5, 2014 4:01 PM
  • Well, I can certainly confirm that there is a missing join in the view definition for the MSP_EpmAssignmentBaselineCumulativeByDay_UserView view - it appears to be missing the BaselineNumber join.

    Specifically, the WHERE statement after the CROSS JOIN is:

        A.ProjectUID = B.ProjectUID AND A.AssignmentUID = B.AssignmentUID AND B.TimeByDay <= A.TimeByDay

    it should also join A.BaselineNumber = B.BaselineNumber

    That said, if you test the data for the cumulative data on a project with only a single assignment, the data looks correct once you fix that join.  However, projects with multiple assignments (like ya do) seem to have the cumulative data multiplied by the number of assignments in the project.  I am opening a case with MS and requesting a hotfix.

    Hope that this helps someone out there.


    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/

    • Marked as answer by ElliJ Tuesday, September 16, 2014 5:19 PM
    Tuesday, September 16, 2014 5:19 PM
  • Hi ElliJ,

    I have not worked with the cumulative values before. But perhaps you need to exclude summarytasks? Perhaps that leads to the high values?

    Please update the thread when you get Feedback from Microsoft.

    Regards

    Christoph


    Christoph Muelder | Senior Consultant, MCTS, MCSE | SOLVIN information management GmbH, Germany

    • Proposed as answer by Christoph Muelder Thursday, September 18, 2014 6:41 PM
    • Marked as answer by ElliJ Thursday, September 18, 2014 7:17 PM
    Wednesday, September 17, 2014 1:11 PM
  • That's an excellent point, Christoph!  It does appear to be the issue with the Task cumulative baseline table - once you filter out the summary tasks, the math lines up just right!  Great catch!

    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/

    Thursday, September 18, 2014 3:16 PM
  • Hi Elli

    great to hear that I could help.

    I hope, that was before you opened the Support call

    Kind regards

    Christoph


    Christoph Muelder | Senior Consultant, MCTS, MCSE | SOLVIN information management GmbH, Germany

    • Marked as answer by ElliJ Thursday, September 18, 2014 7:17 PM
    • Unmarked as answer by ElliJ Thursday, September 18, 2014 7:17 PM
    Thursday, September 18, 2014 6:42 PM
  • Christoph,

    Knowing the fix for that particular table is awesome - but the other cumulative tables still don't add up correctly, and besides, MS still needs to fix the code in the product, right? :) 

    I told them what you found and referenced your reply, thanks a million for your insight! 

    The Assignment cumulative baseline tables still have issues, and I for one can't even query the MSP_EpmTaskCumulativeByDay_UserView - it errors out every time.  I've put all that into my case and I'm working with MS on it.

    Thanks again!


    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/

    Thursday, September 18, 2014 7:17 PM
  • Hi Elli,

    there are several issues with the reporting tables in Project Server 2013.

    We heavily rely on the database for an add-on tool that we developed for timesheeting. We regulary find that the assignment in the reporting table still Shows "unassigned resource" when the resource is assigned correctly according to the published tables.

    The worst issue we just found this week. We had missing data in the reporting tables and decided to do a reporting database rebuild (backup/restore custom fields). Job ran without errors in the Queue but afterwards our reports where just empty. So I needed to publish all the Projects once again using PowerShell. That brought the data back.

    But that will not help for your assignment table issues.

    Kind regards

    Christoph 


    Christoph Muelder | Senior Consultant, MCTS, MCSE | SOLVIN information management GmbH, Germany

    Thursday, September 18, 2014 7:32 PM