Monday, July 28, 2014

#projectserver Generic Resources with 0% capacity skew FTE calculations in resource plans

** Update **
Having looked at the data, the general rules are now 

  • Dont use TAP flag unless it is explicitly for the Team Assignments functionality (which no-one uses anyway)
  • And if you want to use Generic Resources on Resource Plans using FTE planning, make sure (for now) that they have some capacity, and design this out of your capacity planning reports.

Generic Resource Capacity

in times gone by I have picked up the habit of using the "Team Assignment Pool" flag on Generic Resources to avoid setting the Max Units to 0% and still have zero capacity for the resources in the RDB.  This was a throwback to the heady days of PS2007 and its complete lack of stability (0% resources used to cause havoc when using the Assign Resources dialogue in MS Project).

It looks as if this is a habit I am going to have to break.

Today I have found a new issue in Resource Plans on PS2013 where setting the Team Assignment Pool OR setting Max Units to 0% on a resource has a negative effect on the FTE calculation.


  • Create three generic resources (r1, r2, r3)
  • set R1 with Max Units = 0% and Team Assignment Pool is No
  • set R2 with Max Units = 100% and Team Assignment Pool is Yes
    These are the two trouble settings
  • Set R3 with Max Units = 100 and Team Assignment Pool is No
    this one should work fine
Now create a new project and assign these three resources to the Resource Plan
  • Set Timescale = Weeks
  • Set units = FTE
Now enter one FTE for each resource across the periods and save

Now, when switching Units to Hours you will see two of the resources (R1 and R2) will have more hours per week than R3.  In my environment, R3 had 40 hours per week and R1/2 had 54 (no idea where 54 came from)

So this takes us into some further thinking on this:
  • How on earth is the FTE calculation working this out?
  • Does PS now assume Generic Resources do not have capacity elsewhere in the tool (such as the little used and mostly redundant Resource Availability chart, MSP_EpmResourceByDay capacity data, OLAP, etc) as this will have a significant bearing on how we use Generic Resources going forward.

In my mind Generics are buckets of planned work.  They do not have capacity in most scenarios.  They may (or may not) be used as Team Assignment Pool resources (if the functionality was useful).  They should not be included in Capacity calculations but MUST contribute towards the total Team demand.  I have never found another useful "capacity" based solution for these guys, so why on earth do we continue with capacity numbers for them.

So, a word to the wiser.  If you want to use Resource Plan FTE scheduling, leave your generic resources with capacity or see strange results.

I am going to go find out whether i need to update my capacity planning reports now :(

I decided to do a quick check on what exactly 2013 is doing from a Generic Capacity perspective these days

  • The use of Team Assignment Pool removes the resource completely from the MSP_EPMResourcebyDay table so no capacity can be held.  This is probably a tad extreme and could break some reports where joins don't allow for this
  • Use of 0% Max Units zeros capacity in the ByDay table as you would want, and doesn't seem to cause any issues with assigning resources (I didn't expect it would any more, but it's always good to check).  
  • User of >0% Max Units adds capacity to the resource data as you would expect.

Tuesday, July 22, 2014

#ProjectOnline #ProjectServer #PowerQuery missing timesheet report

This has been painful

Project Online reporting has been a fairly significant limitation of the Online experience for me so far.  Besides the complexity in building the oData stream, the fairly fundamental limitations of the data filtering of the URI's has been a major falling down point for me.

To digress for a moment, most reports people want are timephased in some way, either Actual Work by period, planned work by period, or timesheet data by period.  All of these reports lead to a "time by day" feed.  Now the thing with timephased reports is you are usually looking for a "last three months, next three months" view (or similar).  Also the thing with the Time By Day feeds is that they are HUGE!

Add these things together and you really need a way of limiting your data feed to avoid extremely lengthy refresh times.

Easy enough I hear you say.  Just put a timebyday ge '2014-01-01T00:00:00' select statement in your URI before adding it into your PowerPivot data model.

Yup, that's sweet. However when you come to change the date parameter, two things will happen

1) the table relationships are broken
2) any links within the sheet (pivots, calculations, custom columns in the model) will have to be recreated

Basically - the report breaks or needs to be maintained by someone "with experience" on a periodic basis.

Enter PowerQuery with PowerPivot

The purpose of this blog

So with the above findings for standard PowerPivot limitations, we look at the requirement for a "missing timesheet" report.

1) It should show the status of timesheets for last week
2) it should highlight where a timehseet hasnt been created
3) it should show the hours currently in the timesheet and the timesheet status (where created)
4) It will be run weekly so shouldn't require maintenance, and should refresh fast
5) It should include resource level custom fields (Department, Team, Employment Type)

To do this we need to grab data from the following feeds
1) Timesheets
2) TimesheetLines
3) TimesheetActualDataSet (the time by day data)
4)  Resources

To do this we will use PowerQuery to support the rolling filtering

PowerQuery timephased filtering

There are two really handy functions in PowerQuery that allows you to filter on rolling periods:

( DateTime.FixedLocalNow() ) works as a todays date marker

#duration(n, 0, 0, 0) allows you to stipulate an elapsed number of days (or hours, minutes, seconds) where n is the number of days in question

add these two items together and you can set up a filter on the Time by Day table to show you records that exist where time by day is greater than or equal to for the last 15 days and into the future (in this scenario we're not worried about future hours as there shouldn't be much in timesheets except holiday time, and we will join this data out using the timesheet periods data.  We could add an additional filter if we wished)

FilteredRows = Table.SelectRows(TimesheetLineActualDataSet1, 
   each [TimeByDay] >= (DateTime.FixedLocalNow() - #duration(8, 0, 0, 0))),

The step by step is:

  1. install PowerQuery addin for Excel first :)
  2. create a PowerQuery connection to your ProjectOnline instance using either Windows or Organisatinoal account
  3. use the ProjectData URI
  4. Select the oData stream you want from the list provided in the Navigator panel (lets do the TimesheetLineActualDataset)
  5. once query editor opens, select the timebyday column and create a date filter on it (something like AFTER dd/mm/yyyyy) to set up the filter structure
  6. Apply any other data filters required by your report data definition
  7. now select the VIEW tab, and click Advanced Editor

You should see something like this:

    Source = OData.Feed(""),
    TimesheetLineActualDataSet1 = Source{[Name="TimesheetLineActualDataSet"]}[Data],
    FilteredRows = Table.SelectRows(TimesheetLineActualDataSet1, each [TimeByDay] > #datetime(2014, 7, 1, 0, 0, 0))

The date filter you created is seen here:
 FilteredRows = Table.SelectRows(TimesheetLineActualDataSet1, each [TimeByDay] > #datetime(2014, 7, 1, 0, 0, 0))

And you want to change this to something like this:

FilteredRows = Table.SelectRows(TimesheetLineActualDataSet1, each [TimeByDay] >= (DateTime.FixedLocalNow() - #duration(15, 0, 0, 0))),

Once done to your satisfaction, select Home tab in query editor and select Apply and Close.

In Excel the data table you've created with your filtered query should be returned.  At this point i would double check you are happy wit this.

Now Select your PowerPivot tab and click "Add to Model"

Repeat the above steps for your Timesheet and TimesheetLines data feeds.

Sample TimesheetLines Query

    Source = OData.Feed(""),
    TimesheetLines1 = Source{[Name="TimesheetLines"]}[Data],
    RemovedColumns = Table.RemoveColumns(TimesheetLines1,{"AssignmentId", "CreatedDate", "LastSavedWork", "LCID", "TimesheetPeriodStatusId"}),
    FilteredRows = Table.SelectRows(RemovedColumns, each ([TimesheetPeriodStatus] = "Opened")),
    FilteredRows1 = Table.SelectRows(FilteredRows, each [PeriodEndDate] <= (DateTime.FixedLocalNow() ))

Sample Timesheets Query

    Source = OData.Feed(""),
    Timesheets1 = Source{[Name="Timesheets"]}[Data],
    FilteredRows = Table.SelectRows(Timesheets1, each [EndDate] < (DateTime.FixedLocalNow() )),
    FilteredRows1 = Table.SelectRows(FilteredRows, each [Description] <> "Closed")

Now the important bit....

In order to understand who has not created timesheets, normally in SQL we would use some outer join mechanism to a master list of resources who SHOULD be submitting, so as to know who is NOT IN the master set.  This is because until the timesheet is created, the data is not in the database.

We do this by:

  • Creating the Resource datafeed with the appropriate filters to show only those resouorces who SHOULD be submitting a timesheet
  • Add this to the datamodel
  • Create the relationships in the PowerPivot datamodel (I am going to assume you know how to do this)
  • Create a pivot table based on the datamodel to show submitted hours
  • Convert this pivot to a flat structure (classic display mode, no totaling)

  • now on the tab that has your resource table on it, navigate to the right hand side of the table.  You want to create three custom columns, both using vlookup references to the pivot table you just created.  One example is here, you can work the rest out!

    =IF(ISNA(VLOOKUP([@ResourceName],'Submitted Hours Summary'!B$4:E$200,4,FALSE)),0,VLOOKUP([@ResourceName],'Submitted Hours Summary'!B$4:E$200,4,FALSE))

    This looks at the resource name in the table, finds the name in the Pivot Table and returns either the Total Timesheet Hours, or 0 if a record is not found
  • The three columns you need are
    • Sum of Timesheet Hours
    • Timesheet Status - your ISNA value should be "Not Created"
    • Period ending
  • Once done, make a pivot from the Resource table with the extended columns, adding the Resource Name, Status and Hours Total
  • here's what you end up with is something like this after some quick conditional formatting


Wednesday, July 16, 2014

Three tier #projectserver and custom project site creation failure

1x WFE
1x APP
1x SQL

Web Front End Service on WFE
Project Server enabled on APP

so a traditional 3tier scenario.  nothing complicated

When deploying new Web features to the Farm, you are unable to create a Project Site based on a custom template.

Microsoft.SharePoint.SPException: The site template requires that the Feature {17843394-988a-4a4e-9d5e-8b419bdc1340} be installed in the farm or site collection

Now we know this IS enabled at the SC level.  Only un-installing these features enables the site provisioning to successfully complete.  Redeploying and enabling these features then breaks it again

Well... it turns out the features are required on the server where Project Server is enabled.
as these are site features, that means you have two options
1) enable the Web Front end service on the App Server - which seems like a crazy thing to do to me
2) copy the features from /15/Template/Features from WFE to APP - again, mad mad mad.  Why should you need file system coping?

Anyway, option 1 did resolve the problem and I believe option 2 would also.  But surely neither of these solutions is a real world fix

·         CreateWssSiteContent: Creating project site failed! Project Uid=448aa1e5-5e07-e411-9430-005056010737, site URL=http://siteaddress - activated feature at PWA level, site name=site address activated feature at PWA level. Microsoft.SharePoint.SPException: The site template requires that the Feature {17843394-988a-4a4e-9d5e-8b419bdc1340} be installed in the farm or site collection. at Microsoft.SharePoint.Utilities.SPUtility.ThrowSPExceptionWithTraceTag(UInt32 tagId, ULSCat traceCategory, String resourceId, Object[] resourceArgs) at Microsoft.SharePoint.SPWebTemplateElement.VerifyFeatures(XmlNodeList xmlNodeFeatures, SPWeb applyTemplateToThisWeb, SPFeatureScope featureScope, SPFeatureDependencyErrorBehavior featureDependencyErrorBehavior, List`1& featureDependencyErrors) at Microsoft.SharePoint.SPWebTemplateElement.VerifyFeaturesInWebTemplate(SPWeb applyTemplateToThisWeb, SPFeatureDependencyErrorBehavior featureDependencyErrorBehavior, ICollection`1& featureDependencyErrors) at Microsoft.SharePoint.SPWeb.LoadFeatureWebTemplateContent(SPFeatureWebTemplate featureWebTemplate, SPFeatureDependencyErrorBehavior featureDependencyErrorBehavior, ICollection`1& featureDependencyErrors) at Microsoft.SharePoint.SPWeb.ProvisionWebTemplate(SPWebTemplate webTemplate, String webTemplateToUse, SPFeatureWebTemplate featureWebTemplate, Page page, SPFeatureDependencyErrorBehavior featureDependencyErrorBehavior, ICollection`1& featureDependencyErrors) at Microsoft.SharePoint.SPWeb.ApplyWebTemplate(SPWebTemplate webTemplate, Page page, SPFeatureDependencyErrorBehavior featureDependencyErrorBehavior, ICollection`1& featureDependencyErrors) at Microsoft.SharePoint.SPSite.CreateWeb(String strUrl, String strTitle, String strDescription, UInt32 nLCID, String strWebTemplate, Boolean bCreateUniqueSubweb, Boolean bConvertIfThere, Guid webId, Guid rootFolderId, Boolean createSystemCatalogs, Guid appInstanceId, String appWebDomainId) at Microsoft.SharePoint.SPSite.SPWebCollectionProvider.CreateWeb(String strWebUrl, String strTitle, String strDescription, UInt32 nLCID, String strWebTemplate, Boolean bCreateUniqueSubweb, Boolean bConvertIfThere, Guid appInstanceId) at Microsoft.SharePoint.SPWebCollection.Add(String strWebUrl, String strTitle, String strDescription, UInt32 nLCID, String strWebTemplate, Boolean useUniquePermissions, Boolean bConvertIfThere, Guid appInstanceId) at Microsoft.Office.Project.Server.BusinessLayer.AdminProjectSiteAllocator.CreateSPWeb(ProjectSiteConfigurationInfo configInfo, SPSite topSite):

·         WSSCreateSiteFailure (16400). Details: id='16400' name='WSSCreateSiteFailure' uid='b7860ae6-5e07-e411-942f-0050560104c8' projectUID='448aa1e5-5e07-e411-9430-005056010737' workspaceUrl=siteaddresst - activated feature at PWA level/sitename- activated feature at PWA level' projectWorkspaceName='sitename - activated feature at PWA level'. 

Friday, July 11, 2014

Using the RESUME field to reschedule incomplete work #projectserver

Since MS provided the ability to plan only using PWA, this has been a really useful feature for some users.  However there has always been a (certainly from my perspective) perceived issue, in that the ability to reschedule incomplete work into the future (a key component of the planning cycle) was only available in MS Project client.

The other day I was asked by a customer how they could get around this issue.  They use PWA extensively (cost reasons) for planning but were struggling with realising correct resource forecast data as they PM's were not able to re-forecast their remaining work schedule.

As we had recently patched the Project Server to 2013 SP1 + 0614CU I thought I would look at the STOP/RESUME fields in the Project views to see if these were editable, and surprise surprise they were.

The stop/resume fields are how MS Project handles task splits and the "update remaining work to restart after" component of rescheduling.  The Stop field will indicate the last day that progress was added to the task (either via % Complete or Timephased Actual Work edits/status updates) and by default the resume field will be the next day (or minute, in truth)

If you edit the resume field to a date in the future, then the task becomes split and the remaining work moves forward to restart from that date.

Don't ask me to explain multiple or manual splits.  I can't

Anyhoo... the repro

Firstly create a new project, with one task on it with 10d duration for simplicity of querying, and assign one resource to the assignment and publish the schedule

As the user assigned to the task, complete the first day of actual work and submit

As the Project Owner, approve the task.  Then go into Approval History and publish the assignment

Wait until it publishes

Then open the project in PWA for editing.

Set the Resume date to some time in the future, in this example 18th August
Note the finish date changes

Now query the assignmentbyday table and see the work move forward (ignore the nulls in my results, these have been replaced in my query to make it easier to discern between 0's and 8's!)

Notice that the planned work now has a gap between the 1d actuals assigned and the Resume date of 18 August


Tuesday, July 1, 2014

#projectserver SQL to calculate running totals by month (work, actuals, Baseline)

When calculating running totals (for S-Curve/BurnUp reports) I have had to transform the periodic data returned from SQL into running totals in a pivot or used the running value SQL RS command to transform the data.

So, fast forward to happy SQL2012 land, and we have some useful script commands that can now be used.  Lets face it, our SQL boxes are usually packed to the brim with RAM and Cores these days so why not use them for what they're built for!

The following script calculates the running totals by month by project for all the normal useful measures. I am in no way saying this is an optimised script, it's just my first kick out at this.

In all honesty you'd probably want to remove the _userview references and go to the tables.  

Enjoy, ignore, laugh at the naivety to your hearts content

--SQL Script to return running totals (RTx) for Projects by Month
Select p.ProjectName

select a.Projectuid,dateadd(month,datediff(month,0,a.timebyday),0) as month

,sum(a.assignmentwork) over (PArtition by a.projectuid ORDER BY dateadd(month,datediff(month,0,a.timebyday),0) ) as RTAssnWork

,a.AssignmentWork as work

,sum(a.AssignmentActualWork) over (PArtition by a.projectuid ORDER BY dateadd(month,datediff(month,0,a.timebyday),0) ) as RTAssnActualWork

,a.AssignmentActualWork as Actwork

,sum(a.AssignmentBaseline0Work) over (PArtition by a.projectuid ORDER BY               dateadd(month,datediff(month,0,a.timebyday),0) ) as RTAssnbaseWork
,a.AssignmentBaseline0Work as Basework

from MSP_EpmAssignmentByDay_UserView a 
Group by a.projectuid,dateadd(month,datediff(month,0,a.timebyday),0), AssignmentWork,AssignmentActualWork,assignmentbaseline0work

left join msp_epmproject_Userview p on data.projectuid = p.ProjectUID

group by p.ProjectName,data.month,data.RTAssnWork,data.RTAssnActualWork,data.RTAssnbaseWork

order by p.ProjectName, data.month