Context: Project Server 2010, single-entry mode Timesheets, "Only allow task updates via Tasks and Timesheets." selected.
For example, the following query below looks at the Timesheet Line table, the Timesheet Line User View and the Assignment User view, and returns mismatches between the Assignment's Task UID and the Timesheet Line's Task UID. Currently this query returns fourteen rows. I've run two other queries: one looks for Timesheet Line User View Task UID's NOT IN the Task table (returns 14 rows); the other looks for Assigment User View Task ID's NOT IN the Task table (returns zero rows).
I'm not sure exactly what's causing this, but it seems to be related to certain kinds of edits to a Project Plan that causes Task UID's to change. The changed UID's are correctly cascaded to the Assignment User View, but not to the Timesheet Line User View.
I'm now in the process of reviewing all of my BI Data Connection queries and changing them to use the Task ID in the Assigment rather than in the Timesheet Line user View.
Has anyone else run into this?
SELECT TL.AssignmentUID , ASG.TaskUID AS [ASG.TaskUID] , CF.TaskUID AS [CF.TaskUID] FROM dbo.MSP_TimesheetLine TL INNER JOIN dbo.MSP_TimesheetLine_UserViewCF CF ON TL.TimesheetLineUID = CF.TimesheetLineUID INNER JOIN dbo.MSP_EpmAssignment_UserView ASG ON TL.AssignmentUID = ASG.AssignmentUID AND CF.TaskUID <> ASG.TaskUIDIf I do an INNER JOIN from the Timesheet Line User View to the Task table or view, it drops valid Tasks from the results. Since our Finance folks depend on the Timesheets for internal chargeback numbers, this is a critical issue.
- Edited by TASB Tom Tuesday, April 17, 2012 1:12 PM
TaskUID is not unique within tables or views. You need to select the pair ProjectUID and TaskUID to get the desired results in views and tables. In the other hand, AssignmentUID is unique.
I hope this information helps.
Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner
The TaskUID in dbo.MSP_EpmTask IS a unique key for the MSP_EmpTask table. (The number of rows returned by a SELECT DISTINCT TASKUID on the table matches the number of rows in the table.)
Of course, other tables and views could have multiple occurrences of a given TaskUID, and if your joins are moving from parent to one specific child, you would need to qualify the join.
The issue I'm raising is that the TaskUID found in MSP_TimesheetLine_UserViewCF is NOT ALWAYS a valid TaskUID: the view's TaskUID doesn't always exist in the Task table. If you use MSP_TimesheetLine_UserViewCF's TaskUID in an INNER JOIN to either the Task table or Task view (dbo.MSP_EpmTask or dbo.MSP_EpmTask_UserView), you will end up losing rows from your result set.
In my case, that translates to lost Chargeback $$$.
Fortunately, the TaskUIDs found in the EmpAssignment table and view are correct. So I first INNER JOIN the Timesheet Line table or view to the Assignment table or view when I want to locate a Timesheet line's Task. Doing this, I do not lose any rows from the result set. Don't trust the Timesheet Line's TaskUID.
Some more context from
It's possible for a Task to acquire a new TaskUID. I had a chance to talk to one of the MS Project Server 2010 developers (Brian) at the MS Project Conference in Phoenix last month, and he confirmed that this can happen. Possible scenario: the Project Manager moves a Task in the Task sequence by using Cut / Paste. This deletes the first Task and creates a new copy, generating a new Task UID.
In the PWA_Reporting tables, notice that TimesheetLine does not contain a TaskUID. Instead, it has a UID of the Task Name (TaskNameUID) which is the primary key of dbo.MSP_TimesheetTask. The idea is that you could use this table as a bridge between the TimesheetLine and its associated Task. The Task's UID may change, but its content (including the TaskNameUID) will not.
In theory, this technique should decouple Timesheet Lines from a dependency on Project Plan Task UIDs. Unfortunately, in practice, it's not quite bulletproof. When a TaskUID changes, it appears that the TaskUID in TimesheetTask is not always being updated.
This looks like one of those Project Professional vs Project Server integration issues. Project Professional's Save routine may not know about Project Server's TimesheetTask table, and/or this dependency may have been overlooked in the Publish routine. If they're using a cascading update to maintain referential integrity, this TaskUID will be overlooked because MSP_TimesheetTask does not designate TaskUID as a Foreign Key.
I looks to me like the TimesheetTask table in PWA _Reporting is updated whenever a Timesheet is created, because I can "clean up" an invalid TimesheetLine_UserView's TaskUID with a Delete/Create of the affected (aflicted?) Timesheet without losing any of the time entered. (This implies that a valid TaskNameUID-TaskUID relationship is stored somewhere else in the system, or this would not work.)
The query behind TimesheetLine_UserViewCF depends on dbo.MSP_TimesheetTask for its TaskUID. As things stand now, when a Task's UID changes but its associated Timesheets are not rebuilt, the TimesheetTask table's TaskUID is not updated and the TimesheetLine_UserViewCF view query picks up an outdated TaskUID.
If anyone has seen this issue noted in any KB article, please post as reference here.
Thanks for reading this far. :-)
Sorry, my mistake. Long time ago TaskUID was not a unique key, but you are right. From 2007 on it is.
Good point about this issue, mainly about what happens when the PM cut and paste tasks and the synch process (or lack of) for this view.
Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner
Tom, I concur that the TaskUIDs you identify are definitely missing. Thank you very much for going to all the trouble to post this very helpful reply.
I found that the joins to the Assignment view that you recommended turned out to be quite involved, so thought I would post my version here.
FROM MSP_TimesheetLine_UserView INNER JOIN (MSP_TimesheetLine_OlapView INNER JOIN (MSP_EpmAssignment_UserView INNER JOIN MSP_EpmTask_UserView ON MSP_EpmAssignment_UserView.TaskUID=MSP_EpmTask_UserView.TaskUID) ON MSP_TimesheetLine_OlapView.AssignmentUID=MSP_EpmAssignment_UserView.AssignmentUID) ON MSP_TimesheetLine_UserView.TimesheetLineUID=MSP_TimesheetLine_OlapView.TimesheetLineUID
The above seems to work for me. Doubtless others have a slicker way! Regards, Ian
I can verify that I've also found that TimeSheet TASK_UID do not match TASK_UID in Projects, Tasks or Assignments. It seems that when a TimeSheet is created, a new TASK_UID is assigned to the "TimeSheet Task" Even though the Task Name, Project Name and PROJ_UIDs are the same.
Another, more laborious way to go:
1) From Assignments get ASSN_UID and TASK_UID
2) From TimeSheet Lines get ASSN_UID and TASK_UID (*)
3) Match them :)
Jonathan M Beck
- Edited by Beta Star Wednesday, August 22, 2012 6:31 PM