none
Duplicate Timesheet Resource RRS feed

  • Question

  • Hi,
    At a customer site (2010 no SP or CUs yet) there is a particular user who's had his name changed (maybe corrected?) in AD. It synced back fine to PS. When looking in the Reporting DB, the resource name (MSP_EpmResource) was successfully changed, but a new record was created in MSP_TimesheetResource with a different ResourceNameUID. That was over a month ago and the new timesheets were created with the new ResourceNameUID.
    I suppose the above works as expected, however the customer has an internal report that generates virtual TS for all records, to identify which ones are missing/not submitted/etc... In this case false positives are created as there are 2 ResourceNameUIDs for the same resource.
    Is there a way to merge the Timesheet Resources into one? Or should I go directly in the Reporting DB, transfer all TS records to the new ResourceNameUID and delete the old one?
    Wednesday, January 18, 2012 6:49 PM

Answers

  • The TimesheetResource, TimesheetTask and TimesheetProject tables work like this (the resource name table is used in the example below - the others work the same way)

    The table contains the following fields of interest (along with a few more which isn't)

    ResourceNameUID, ParentResourceNameUID, ResourceUID - all of which are GUIDs, and ResourceName containing the name of the resource.

    First timesheet: a new line in MSP_TimesheetResource is created with a fresh GUID for ResourceNameUID and ParentResourceNameUID, the name of the resource in ResourceName and the ResourceUID found in the non-timesheet tables - e.g.: MSP_EpmResource.

    When introducing a resource name change (or a task or project name change), the following happens:

    New timesheet with a new resource name: a new line in MSP_TimesheetResource with a fresh GUID for ResourceNameUID, a link to the previous entry for the resource by way of putting the old ResourceNameUID value in the ParentResourceNameUID field, the new name in ResourceName and - this is where most people go wrong - a completely new and un-related to anything GUID in the ResourceUID.

    This means that in order to create a link between the Timesheet Name tables (resource, task or project) you have to first traverse the "tree of parent links" up to the entry where ResourceNameUID and ParentResourceName contain the same GUID - that line, and only that line, contains a ResourceUID that have a corresponding GUID in the EPM tables - i.e.: the non-timesheet tables.

    This is by design apparently in order to retain a snapshot of the names as they existed when the timesheet was created.

    Here's an example of a UDF which will take the "ProjectNameUID" and retreive the ProjectUID for further linking to the non-timesheet tables.

    You'll have to create one of these for task and resource name lookup as well

     

    create function [dbo].[FindUltimateProjectUIDForPN](@projectnameuid uniqueidentifier)

                    returns uniqueidentifier

    as

    begin

                    declare @ppn uniqueidentifier, @puid uniqueidentifier;

     

                    select @puid=ProjectUID, @ppn = ParentProjectNameUID

                    from ProjectServer_Reporting.dbo.MSP_TimesheetProject

                    where projectNameUID = @projectnameuid;

     

                    while @@rowcount > 0

                                    select @puid= t2.projectUID, @ppn = t2.ParentprojectNameUID

                                    from ProjectServer_Reporting.dbo.MSP_Timesheetproject t1

                                    inner join ProjectServer _Reporting.dbo.MSP_Timesheetproject t2

                                                    on t1.parentprojectnameuid = t2.projectnameuid

                                                    and t1.projectnameuid = @ppn

                                    where t1.projectnameuid <> t1.parentprojectnameuid;

                   

                    return @puid;

    end

    Hope this helps.

    /Lars Hammarberg, DovreGroup AB (formerly known as Camako)


    //Lars Hammarberg www.camako.se Gold Certified Partner
    • Marked as answer by Simon Roy Sunday, January 22, 2012 7:50 PM
    Sunday, January 22, 2012 2:46 PM

All replies

  • The TimesheetResource, TimesheetTask and TimesheetProject tables work like this (the resource name table is used in the example below - the others work the same way)

    The table contains the following fields of interest (along with a few more which isn't)

    ResourceNameUID, ParentResourceNameUID, ResourceUID - all of which are GUIDs, and ResourceName containing the name of the resource.

    First timesheet: a new line in MSP_TimesheetResource is created with a fresh GUID for ResourceNameUID and ParentResourceNameUID, the name of the resource in ResourceName and the ResourceUID found in the non-timesheet tables - e.g.: MSP_EpmResource.

    When introducing a resource name change (or a task or project name change), the following happens:

    New timesheet with a new resource name: a new line in MSP_TimesheetResource with a fresh GUID for ResourceNameUID, a link to the previous entry for the resource by way of putting the old ResourceNameUID value in the ParentResourceNameUID field, the new name in ResourceName and - this is where most people go wrong - a completely new and un-related to anything GUID in the ResourceUID.

    This means that in order to create a link between the Timesheet Name tables (resource, task or project) you have to first traverse the "tree of parent links" up to the entry where ResourceNameUID and ParentResourceName contain the same GUID - that line, and only that line, contains a ResourceUID that have a corresponding GUID in the EPM tables - i.e.: the non-timesheet tables.

    This is by design apparently in order to retain a snapshot of the names as they existed when the timesheet was created.

    Here's an example of a UDF which will take the "ProjectNameUID" and retreive the ProjectUID for further linking to the non-timesheet tables.

    You'll have to create one of these for task and resource name lookup as well

     

    create function [dbo].[FindUltimateProjectUIDForPN](@projectnameuid uniqueidentifier)

                    returns uniqueidentifier

    as

    begin

                    declare @ppn uniqueidentifier, @puid uniqueidentifier;

     

                    select @puid=ProjectUID, @ppn = ParentProjectNameUID

                    from ProjectServer_Reporting.dbo.MSP_TimesheetProject

                    where projectNameUID = @projectnameuid;

     

                    while @@rowcount > 0

                                    select @puid= t2.projectUID, @ppn = t2.ParentprojectNameUID

                                    from ProjectServer_Reporting.dbo.MSP_Timesheetproject t1

                                    inner join ProjectServer _Reporting.dbo.MSP_Timesheetproject t2

                                                    on t1.parentprojectnameuid = t2.projectnameuid

                                                    and t1.projectnameuid = @ppn

                                    where t1.projectnameuid <> t1.parentprojectnameuid;

                   

                    return @puid;

    end

    Hope this helps.

    /Lars Hammarberg, DovreGroup AB (formerly known as Camako)


    //Lars Hammarberg www.camako.se Gold Certified Partner
    • Marked as answer by Simon Roy Sunday, January 22, 2012 7:50 PM
    Sunday, January 22, 2012 2:46 PM
  • Hi Lars,

     

    Thanks for the reply. That confirms what we've seen. The customer report will be modified to use the correct ResourceUID string instead of the TS one.

     

    Thanks again,

    Simon

    Sunday, January 22, 2012 7:52 PM