Sign in
 
HomeOnline20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Hours to FTE Conversions

Answered Hours to FTE Conversions

  • Tuesday, May 15, 2012 12:31 AM
     
      Has Code
    Sign In to Vote
    0

    Hello--

    In our implemention we are using resource plans as a way to reserve resources (forecasting).  We are developing a comprehensive set of SQL reporting services dashboards which includes querying data within the projectserver_reporting database.  Our stakeholders want all hourly values converted to FTE, which makes sense.  They also like to eat human flesh, which I'm not as crazy about. 

    Anyway, converting from hours to FTE, especially across a full calendar year makes these queries quite expensive and overly-complicated.  I'm doing the following:

     (SELECT        SUM(uv2.AssignmentResourcePlanWork) AS Expr1
                               FROM            MSP_EpmResource_UserView INNER JOIN
                                                         MSP_EpmAssignment_UserView ON MSP_EpmResource_UserView.ResourceUID = MSP_EpmAssignment_UserView.ResourceUID INNER JOIN
                                                         MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                                                         MSP_EpmAssignmentByDay_UserView AS uv2 ON MSP_EpmAssignment_UserView.AssignmentUID = uv2.AssignmentUID
                               WHERE        (uv2.ProjectUID = uv1.ProjectUID) AND (r1.ResourceUID = MSP_EpmResource_UserView.ResourceUID) AND (uv2.TimeByDay BETWEEN '1/1/2012' AND '1/31/2012') AND 
                                                         (MSP_EpmAssignment_UserView.AssignmentType = 101)) / 160 AS [January 2012 FTE],
                             (SELECT        SUM(uv2.AssignmentResourcePlanWork) AS Expr1
                               FROM            MSP_EpmResource_UserView AS MSP_EpmResource_UserView_12 INNER JOIN
                                                         MSP_EpmAssignment_UserView AS MSP_EpmAssignment_UserView_12 ON MSP_EpmResource_UserView_12.ResourceUID = MSP_EpmAssignment_UserView_12.ResourceUID INNER JOIN
                                                         MSP_EpmProject_UserView AS MSP_EpmProject_UserView_11 ON MSP_EpmAssignment_UserView_12.ProjectUID = MSP_EpmProject_UserView_11.ProjectUID INNER JOIN
                                                         MSP_EpmAssignmentByDay_UserView AS uv2 ON MSP_EpmAssignment_UserView_12.AssignmentUID = uv2.AssignmentUID
                               WHERE        (uv2.ProjectUID = uv1.ProjectUID) AND (r1.ResourceUID = MSP_EpmResource_UserView_12.ResourceUID) AND (uv2.TimeByDay BETWEEN '2/1/2012' AND '2/29/2012') AND 
                                                         (MSP_EpmAssignment_UserView_12.AssignmentType = 101)) / 160 AS [Febuary 2012 FTE],
                             (SELECT        SUM(uv2.AssignmentResourcePlanWork) AS Expr1
                               FROM            MSP_EpmResource_UserView AS MSP_EpmResource_UserView_11 INNER JOIN
                                                         MSP_EpmAssignment_UserView AS MSP_EpmAssignment_UserView_11 ON MSP_EpmResource_UserView_11.ResourceUID = MSP_EpmAssignment_UserView_11.ResourceUID INNER JOIN
                                                         MSP_EpmProject_UserView AS MSP_EpmProject_UserView_10 ON MSP_EpmAssignment_UserView_11.ProjectUID = MSP_EpmProject_UserView_10.ProjectUID INNER JOIN
                                                         MSP_EpmAssignmentByDay_UserView AS uv2 ON MSP_EpmAssignment_UserView_11.AssignmentUID = uv2.AssignmentUID
                               WHERE        (uv2.ProjectUID = uv1.ProjectUID) AND (r1.ResourceUID = MSP_EpmResource_UserView_11.ResourceUID) AND (uv2.TimeByDay BETWEEN '3/1/2012' AND '3/31/2012') AND 
                                                         (MSP_EpmAssignment_UserView_11.AssignmentType = 101)) / 176 AS [March 2012 FTE]

    ...and so on... for each month in the next two years.   These are sub-queries within a larger query--for each project I grab each resource and then convert the hours to FTE.  Those are the outer references within the query.

    There are a few other issues related to these sort of sub-queries that make running these reports burdensome, but I figured I'd start with this one, since it seems like others must have encountered this sort of issue.

    The specific problems with this query are the following:

    1. The query takes forever to run--we have thousands of resources and hundreds of projects.  It's just not scalable.
    2. I've had to create a lookup table (not shown here) that figures out how many working hours there are in each month of each year. 
    3. It's long and actually creates a timeout within Report Builder.  I won't get into my awful workarounds.

    The only positive thing about this, besides that it eventually works, is that I've forced myself to do yoga exercises while waiting for it to return, so I'm so much more balanced.  Although someone saw me doing the downward dog at the office the other day and it was hard to explain.

    Please let me know what you think.

    Reg

     

All Replies

  • Tuesday, May 15, 2012 11:09 AM
     
      Has Code
    Sign In to Vote
    1

    Hi Reg--

    I remember that with the similar kind of requirement in past, I have used the Resource Capacity Vs Denmand report of Project server 2007 repot pack 2 (under Governance Report).
    http://blogs.msdn.com/b/project_programmability/archive/2009/08/20/project-server-2007-report-pack-ii-the-top-reports.aspx
    You can get the data easily but displaying may be a little tricky.

    I used the same for PS2010. Here is the query which gets the data in 2-3 seconds from more than 1000 resources & 100s of projects.
    You may see that you can get the data of Resource capacity data (palnned work across projects) for the selected weeks, Months & years etc.

    if object_id('tempdb..#temp_table1') is not null
drop table #temp_table1;
select isnull( assignment_table.TimebyDay , capacity_table.timebyday) as [Time of Day],
assignment_table.AssignmentWork as [Planned Work],
capacity_table.capacity as [Capacity],
isnull(assignment_table.resourcename,capacity_table.resourcename) as [Resource Name],
isnull(assignment_table.rbs,capacity_table.rbs) as [RBS],
--isnull(assignment_table.[Position Role],capacity_table.[Position Role]) as [Position Role],
isnull(assignment_table.[Project Name],'<capacity  with no project association>') as [Project Name],
isnull(capacity_table.[Year],assignment_table.[Year]) as [Year],
isnull(capacity_table.[Quarter],assignment_table.[Quarter] ) as [Quarter],
isnull(capacity_table.CalendarMemberKeyQuarter,assignment_table.CalendarMemberKeyQuarter )  as [Quarter Key],
isnull(capacity_table.[Month],assignment_table.[Month] )  as [Month],
isnull(capacity_table.CalendarMemberKeyMonth,assignment_table.CalendarMemberKeyMonth) as [Month Key],
isnull(capacity_table.[Week],assignment_table.[Week] )  as [Week],
isnull(capacity_table.CalendarMemberKeyWeek,assignment_table.CalendarMemberKeyWeek ) as [Week Key]
into #temp_table1 
from 
( select ADUV.TimebyDay ,
ADUV.AssignmentWork,
rt.resourceuid,
RT.ResourceName,
RT.RBS,
--RT.[Position Role],
PT.ProjectName as [Project Name],
TDT.CalendarMemberNameYear as [Year],
TDT.CalendarMemberNameQuarter as [Quarter],
TDT.CalendarMemberKeyQuarter,
TDT.CalendarMemberNameMonth as [Month],
TDT.CalendarMemberKeyMonth,
TDT.CalendarMemberNameWeek as [Week],
TDT.CalendarMemberKeyWeek
from MSP_EPMAssignmentByDay ADUV
left outer join
MSP_EPMAssignment AUV
on ADUV.AssignmentUID = AUV.AssignmentUID
left outer join
MSP_TimeByDay_OlapView TDT
on ADUV.TimeByDay = TDT.TimeByDay
left outer join
MSP_EPMProject_UserView PT
on AUV.ProjectUID = PT.ProjectUID
left outer join
MSP_EPMResource_UserView RT
on AUV.ResourceUID = RT.ResourceUID
where
rt.resourceisgeneric = 0
and
TDT.CalendarMemberKeyYear = 2011 and
TDT.CalendarMemberKeyQuarter = 2 and
TDT.CalendarMemberKeyMonth IN (4, 5) and
TDT.CalendarMemberKeyWeek IN (14,15) 
--and RT.RBS in (@RBS)
) assignment_table
full outer join 
(
select 
RDT.Capacity,
rdt.timebyday,
RT.ResourceName,
rt.resourceuid,
RT.RBS,
--RT.[Position Role],
TDT.CalendarMemberNameYear as [Year],
TDT.CalendarMemberNameQuarter as [Quarter],
TDT.CalendarMemberKeyQuarter,
TDT.CalendarMemberNameMonth as [Month],
TDT.CalendarMemberKeyMonth,
TDT.CalendarMemberNameWeek as [Week],
TDT.CalendarMemberKeyWeek
from 
MSP_EPMResourceByDay_UserView RDT
left outer join
MSP_TimeByDay_OlapView TDT
on RDT.TimeByDay = TDT.TimeByDay
left outer join
MSP_EPMResource_UserView RT
on rdt.ResourceUID = RT.ResourceUID
where
rt.resourceisgeneric = 0
and
TDT.CalendarMemberKeyYear = 2011 and
TDT.CalendarMemberKeyQuarter = 2 and
TDT.CalendarMemberKeyMonth IN (4, 5) and
TDT.CalendarMemberKeyWeek IN (14, 15) 
--and RT.RBS in (@RBS)
)  capacity_table
on capacity_table.resourceuid = assignment_table.resourceuid
and capacity_table.timebyday = assignment_table.timebyday
select
(select 
[Capacity]/count(*)
from #temp_table1 ITT
where ITT.[Time of Day] = OTT.[Time of Day]
and ITT.[Resource Name] = OTT.[Resource Name]
group by [Capacity]
)	as [SRS Capacity Calculated Contribution]
,*
from #temp_table1 OTT
    Hope that helps.

    If you found this post helpful, please "Vote as Helpful";. If it answered your question, Please "Mark as Answer" .

    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82




     
  • Tuesday, May 15, 2012 3:11 PM
     
      Has Code
    Sign In to Vote
    0

    Hi Amit--

    Thanks for helping out, but this actually does not solve my problem.  First, I want to look into the resource plans, not into actual planned work.  That's fine, I just end up substituting 'AssignmentResourcePlanWork' where you have 'AssignmentWork'.  However, this still gives me a row for each day and the work is expressed in hours.  I need to view a list of resources, by project, organized by month and in the FTE unit as opposed to hours.  Again, the challenge is that each month has a different number of work hours so the FTE divisor is variable.

    Here is the full query, so you'll be able to run it:

    SELECT        MSP_EpmAssignmentByDay_UserView.ProjectUID, MSP_EpmAssignmentByDay_UserView.AssignmentUID, r1.ResourceName,
                          MSP_EpmResource_UserView_1.ResourceName AS ProjectManager,
/* Jan, 2012 */
 (SELECT        SUM(uv2.AssignmentResourcePlanWork) AS Expr1
                               FROM            MSP_EpmResource_UserView INNER JOIN
                                                         MSP_EpmAssignment_UserView ON MSP_EpmResource_UserView.ResourceUID = MSP_EpmAssignment_UserView.ResourceUID INNER JOIN
                                                         MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                                                         MSP_EpmAssignmentByDay_UserView AS uv2 ON MSP_EpmAssignment_UserView.AssignmentUID = uv2.AssignmentUID
                               WHERE        (uv2.ProjectUID = uv1.ProjectUID) AND (r1.ResourceUID = MSP_EpmResource_UserView.ResourceUID) AND (uv2.TimeByDay between '1/1/2012' and '1/31/2012') AND AssignmentType = 101)/160 AS [January 2012 FTE] ,
                               
/* Feb, 2012 */
 (SELECT        SUM(uv2.AssignmentResourcePlanWork) AS Expr1
                               FROM            MSP_EpmResource_UserView INNER JOIN
                                                         MSP_EpmAssignment_UserView ON MSP_EpmResource_UserView.ResourceUID = MSP_EpmAssignment_UserView.ResourceUID INNER JOIN
                                                         MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                                                         MSP_EpmAssignmentByDay_UserView AS uv2 ON MSP_EpmAssignment_UserView.AssignmentUID = uv2.AssignmentUID
                               WHERE        (uv2.ProjectUID = uv1.ProjectUID) AND (r1.ResourceUID = MSP_EpmResource_UserView.ResourceUID)AND (uv2.TimeByDay between '2/1/2012' and '2/29/2012') AND AssignmentType = 101)/160 AS [Febuary 2012 FTE] ,
/* March, 2012 */
 (SELECT        SUM(uv2.AssignmentResourcePlanWork) AS Expr1
                               FROM            MSP_EpmResource_UserView INNER JOIN
                                                         MSP_EpmAssignment_UserView ON MSP_EpmResource_UserView.ResourceUID = MSP_EpmAssignment_UserView.ResourceUID INNER JOIN
                                                         MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                                                         MSP_EpmAssignmentByDay_UserView AS uv2 ON MSP_EpmAssignment_UserView.AssignmentUID = uv2.AssignmentUID
                               WHERE        (uv2.ProjectUID = uv1.ProjectUID) AND (r1.ResourceUID = MSP_EpmResource_UserView.ResourceUID)AND (uv2.TimeByDay between '3/1/2012' and '3/31/2012' ) AND AssignmentType = 101)/176 AS [March 2012 FTE] 
                               
                               /*
Continue to do the same for each month, using the correct divisor--code removed temporarily to demonstrate concept */                  
FROM            MSP_EpmResource_UserView AS r1 INNER JOIN
                         MSP_EpmAssignment_UserView AS MSP_EpmAssignment_UserView_1 ON r1.ResourceUID = MSP_EpmAssignment_UserView_1.ResourceUID INNER JOIN
                         MSP_EpmProject_UserView AS uv1 ON MSP_EpmAssignment_UserView_1.ProjectUID = uv1.ProjectUID INNER JOIN
                         MSP_EpmAssignmentByDay_UserView ON MSP_EpmAssignment_UserView_1.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID INNER JOIN
                         MSP_EpmResource_UserView AS MSP_EpmResource_UserView_1 ON uv1.ProjectOwnerResourceUID = MSP_EpmResource_UserView_1.ResourceUID
WHERE        (MSP_EpmAssignment_UserView_1.AssignmentType = 101) AND (MSP_EpmAssignment_UserView_1.AssignmentResourceType = 2) AND (r1.ResourceIsActive = 1)
GROUP BY MSP_EpmAssignmentByDay_UserView.ProjectUID, MSP_EpmAssignmentByDay_UserView.AssignmentUID, r1.ResourceName, 
                         MSP_EpmResource_UserView_1.ResourceName, uv1.ProjectUID, r1.ResourceUID

    Thanks-o-lot--

    Reg

     
  • Tuesday, May 15, 2012 9:57 PM
    Moderator
     
     
    Sign In to Vote
    1

    I'm wondering if you're not making this too complicated. The accuracy level you're trying to achieve, downward dog moves and all ;-) may be unnecessary. For example construction workers might have a 9h day and software developers a 6h day (allowing for support work etc). Therefore I suspect a lookup table of conversion rates for each department or skill is needed or use the max units for each resource.

    Using the dates table and the Time_by_day View monthly work divided by the conversion rate is all you need. So 100h of work for a resource with a conversion factor of 5h/d is 200 FTE. Then of course FTE can be defined differently by different orgs. In some 1 FTE is 8h/day regardless of leave, BAU etc.

    The EPM_TimeByDay table combined with MSP_EpmAssignmentByDay allows calc of assignment work per month. Divide by 8 (or whatever) then maybe divide by max units and there's a workable answer?

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

     
  • Wednesday, May 16, 2012 11:19 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Amit and Rod--

    The combination of both of your answers pushed me in the right direction.  

    Trying to convert to FTE using the capacity query was problematic due to the way the data was returning and I was 'relaxed' enough from my Reverse Butterfly Possum-Stance to figure out how to customize it.  If we had major variations in capacity within our pool then I'd have to.  Instead I took Rod's advice and just created a look-up table with hard coded values that told me the number of working hours in each month, which is exactly the number I needed.   Then I wrote this query--again, borrowing advice from each of you:

    if object_id('tempdb..#temp_table1') is not null
drop table #temp_table1;
SELECT        SUM(ABDO.AssignmentResourcePlanWork) / StandardHoursByMonth.StandardHours AS FTE, RO.ResourceName, PO.ProjectName, TBDO.CalendarMemberNameMonth, TBDO.CalendarMemberNameYear
INTO              [#temp_table1]
FROM            MSP_EpmAssignment_UserView AS AO INNER JOIN
                         MSP_EpmProject_UserView AS PO ON AO.ProjectUID = PO.ProjectUID INNER JOIN
                         MSP_EpmAssignmentByDay_UserView AS ABDO ON AO.AssignmentUID = ABDO.AssignmentUID INNER JOIN
                         MSP_TimeByDay_OlapView AS TBDO ON ABDO.TimeByDay = TBDO.TimeByDay INNER JOIN
                         MSP_EpmResource_UserView AS RO ON AO.ResourceUID = RO.ResourceUID INNER JOIN
                         StandardHoursByMonth ON TBDO.CalendarMemberKeyMonth = StandardHoursByMonth.TimeMonthOfTheYear AND TBDO.CalendarMemberNameYear = StandardHoursByMonth.TimeYear INNER JOIN
                         MSP_EpmResource_UserView AS ROPM ON PO.ProjectOwnerResourceUID = ROPM.ResourceUID
WHERE        (TBDO.CalendarMemberKeyMonth IN (4, 5, 6, 7, 8)) AND (TBDO.CalendarMemberKeyYear = 2012) And RO.ResourceIsActive = 1
GROUP BY RO.ResourceName, PO.ProjectName, TBDO.CalendarMemberNameMonth, TBDO.CalendarMemberNameYear, StandardHoursByMonth.StandardHours
SELECT        Results.ResourceName, Results.ProjectName,
                             (SELECT        FTE
                               FROM            [#temp_table1] AS ResultsI
                               WHERE        (ResultsI.ResourceName = Results.ResourceName) AND (ResultsI.ProjectName = Results.ProjectName) AND (ResultsI.CalendarMemberNameYear = 2012) AND 
                                                         (ResultsI.CalendarMemberNameMonth = 'April')) AS [April FTE],
                             (SELECT        FTE
                               FROM            [#temp_table1] AS ResultsI
                               WHERE        (ResultsI.ResourceName = Results.ResourceName) AND (ResultsI.ProjectName = Results.ProjectName) AND (ResultsI.CalendarMemberNameYear = 2012) AND 
                                                         (ResultsI.CalendarMemberNameMonth = 'May')) AS [May FTE],
                             (SELECT        FTE
                               FROM            [#temp_table1] AS ResultsI
                               WHERE        (ResultsI.ResourceName = Results.ResourceName) AND (ResultsI.ProjectName = Results.ProjectName) AND (ResultsI.CalendarMemberNameYear = 2012) AND 
                                                         (ResultsI.CalendarMemberNameMonth = 'June')) AS [June FTE],
                             (SELECT        FTE
                               FROM            [#temp_table1] AS ResultsI
                               WHERE        (ResultsI.ResourceName = Results.ResourceName) AND (ResultsI.ProjectName = Results.ProjectName) AND (ResultsI.CalendarMemberNameYear = 2012) AND 
                                                         (ResultsI.CalendarMemberNameMonth = 'July')) AS [July FTE],
							 (SELECT        FTE
                               FROM            [#temp_table1] AS ResultsI
                               WHERE        (ResultsI.ResourceName = Results.ResourceName) AND (ResultsI.ProjectName = Results.ProjectName) AND (ResultsI.CalendarMemberNameYear = 2012) AND 
                                                         (ResultsI.CalendarMemberNameMonth = 'August')) AS [August FTE]
                                                         
FROM            [#temp_table1] AS Results
GROUP BY Results.ResourceName, Results.ProjectName

    Note that the reason I store it in a temp table and then query it again is to flatten it out.  The first query is very fast, but creates a row for each month.  I need the months as columns for my purposes.

    The schema of the table I created looks like this:

    CREATE TABLE [dbo].[StandardHoursByMonth](
	[StandardHoursKey] [uniqueidentifier] NOT NULL,
	[TimeMonthOfTheYear] [tinyint] NOT NULL,
	[TimeYear] [smallint] NOT NULL,
	[StandardHours] [smallint] NOT NULL,
 CONSTRAINT [PK_StandardHoursByMonth] PRIMARY KEY CLUSTERED 
(
	[StandardHoursKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

    My final query still takes about 2 minutes to run (1 minute in this form, but I have a number of custom fields I removed).  I suspect that further optimization would be possible, but not sure how.  I'm generally satisfied with a gazillion percent performance improvement though.

    Thanks for all of your help.

    - Reg Dwight, PCP

    • Marked As Answer by RegDwight Thursday, May 17, 2012 2:49 AM
    •  
     
  • Thursday, May 17, 2012 2:35 AM
    Moderator
     
     
    Sign In to Vote
    0
    Excellent. Thanks for sharing this and good luck with the yoga!

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management