Thursday, December 15, 2011

Very important note around timephased actual reporting via MS Project Server 2010


There has been an interesting post on Technet recently regarding reporting on timephased actual work with Project Server 2010.  The link is above, but the key phrase is below:

When you protect user updates, timesheets always maintain exactly what the user entered for timephased work, and the scalar values between timesheets and the project plan are always consistent. However, the timephased distribution of the scalar value may be slightly different in the project plan, as it is geared toward future work planning and not maintenance of actual work historical values. The scheduling engine may adjust some timephased actual work to keep the plan consistent.

So, in English, this basically means that if you want EXACT timephased actual work reporting when using Timesheets and Single Entry Mode, you MUST Report from the Timesheet Subsystem, NOT MS Project assignment-level actual work.    The plan should only be used for the scaler-view of Actual Work and the REMAINING work (although of course the timephased remaining work may also not be 100% accurate if the actual work timephased has moved and the plan has not been properly rescheduled.


Wednesday, November 9, 2011

Timesheet solutions in Project Server 2010 - #2 - the rest

In a previous post I talked about assumptions and the reality of data access and security when configuring a simple "hands off" timesheet solution in 2010, and some concerns around functionality that is made available to users by default.

The other areas of concern for me were:

3 - Limitations of creating projects from templates via PWA 
4 - curious behaviour of the auto-publish function when calculating Actual Cost  

So here's some more little tidbits of information that I hope may help...

Limitations of creating projects from templates via PWA(Project.CreateProjectFromTemplate method)

As part of my solution I wanted to allow a two phase project to be created, with Phase 2 tasks disabled when the project is produced to avoid these tasks being available via the Timesheet function without the need to work via MS Project.  Then I wanted the new phase tasks to be activated at a given point in the schedule ideally via PWA.

To do this I...
- Created a MSP template and saved it to the server with the apppropriate task structures, codes and with the deactivated tasks.
- Created an EPT with the new template as the default MSP template
- Created a Project Schedule view in PWA that included the Active and Publish fields for data management

At this point I created the plan in PWA via the EPT and guess what.  All the tasks were active.


After a little digging I found that this was by-design operations as the PSI call for createProjectfromTemplate did not include any reference to the Active field.

Although not a disaster, the next finding kinda nailed me.  When I opened the plan for editing in PWA I could not edit either the Active or Publish fields, so at this point there was no mechanism to allow me to either deactivate or unpublish (sic) the tasks I want hidden from the timesheets.

My target process for htis was
- Project created in PWA
- Fill in fields via PWA PDP
- Publish
- users add tasks to timesheet

The process at this point became:

- Create project in PWA
- Complete fields via PWA PDPs
- Save and close
- open for edit in Pro

- Deactivate the phase 2 tasks
- users add tasks to timesheet

This process means that twice as many people as initially expected require MS Project and those users have to interface with two elements of the solution rather than one.

>>>> Fast forward one month >>>>>

Announcing the hotfix KB2596498 October 2011 CU for Project Server
QFE - 30887 - Creating a new project from a template ignores the Active task flag

This QFE would appear to be addressing the exact issue I have come across.  However I dont believe it is (yet) addressing the lack of edit on the Publish or Active fields via PWA, but I shall be testing shortly.

At this point I think my creation process can use PWA Only, and when moving to Phase 2 the user will need to use MSP to close down Phase 1 and activate Phase 2.... more to follow (again)

Curious behaviour of the auto-publish function when calculating Actual Cost   

Truth be told, I can't actually remember what this point related to.  However there are a few findings to chew over that are fresh in my mind having seen them yesterday.

Auto-approval "skipped for optimization"
the Rules Engine for approvals that allows autopublish only works when the plan is not locked for editing.  At this point if your rule runs to autopublish the task assignment to the plan, this will process to 20% in the queue and get the status "Skipped for optimisation".  to me the correct behaviour would be to enqueue this until the plan is available for editing, or cancel it if a subsequent record change came through to be saved.

I am still risk-assessing this, as I believe that if the task assignment is NOT resubmitted with more updates at a point when the plan is checked in, those actual hours on the specific update that was skipped for optimisation are lost/never processed to the plan, unless you open and publish via MSP (again, tbc).

Get >= August CU to use this function
So far I have seen multiple examples of this rule processing failing to auto-publish, except where august CU or greater is installed.

Things to consider when looking at a streamlined, minimum intervention Timesheet system:

- There is no circumventing the Build Team option to allow resources to see plans, but at least you can do this via PWA
- You MUST implement affective closedown processes for tasks and projects to streamline the Add Task menus
- You will probably need to do some ribbon customisation to lockdown some functions on the Timesheet and Task ribbons.
- If you want to work with deactivated tasks in your project template, get October 2011 CU for Project Server (to be tested)
- if you want auto-approval and auto-publish rule engine functions to work get SP1+August 2011 CU for Project Server at a minimum
- Dont expect to work with task level Publish and Active fields via PWA as these are read-only

good luck!

Monday, September 19, 2011

Problem Provisioning PWA Site after SP1

** UPDATE2:  OK so I didn't reboot before that previous note.  This has now resolved the issue.  Reboot WAS tried prior to June CU

* UPDATE:  After installing June CU2011 Server Rollups (SPF & SPS+PS) I am still seeing the same issues as before.  More research to do.....

Since my rig had Project Server 2010 SP1 packages installed I have seen PWA provisioning fail when creating a brand new PWA Site with clean DB names etc.

Note:  This is NOT a restore scenario:

Firstly I see the following, which is what I would expect:

09/19/2011 15:45:59.32 OWSTIMER.EXE (0x0C30)                   0x05C4 Project Server                 Provisioning                   6974 Information Provisioning 'zzzzzzzzzzzzzzzzz': Server versions: Primary 10.50.1600.1, Reporting 10.50.1600.1. 47871661-d3d4-46fa-9c73-f5faf328a37d

09/19/2011 15:45:59.35 OWSTIMER.EXE (0x0C30)                   0x05C4 Project Server                 Provisioning                   6975 Information Provisioning 'zzzzzzzzzzzzzzzzz': Databases specified for site 'ProjectServer' are as follows: Published: Name = 'zzzzzzzzzzzzzzzzz_Published', State = NotCreated Draft: Name = 'zzzzzzzzzzzzzzzzz_Draft', State = NotCreated Archive: Name = 'zzzzzzzzzzzzzzzzz_Archive', State = NotCreated Reporting: Name = 'zzzzzzzzzzzzzzzzz_Reporting', State = NotCreated 47871661-d3d4-46fa-9c73-f5faf328a37d

Then a little while later prior to the above job completing I see a second PWA Provisioning job is initiated, that reuses the PWA Site url configured by me and then shows the following in ULS:

09/19/2011 15:49:59.61 OWSTIMER.EXE (0x18CC)                   0x2238 Project Server                 Provisioning                   6974 Information Provisioning 'zzzzzzzzzzzzzzzzz': Server versions: Primary 10.50.1600.1, Reporting 10.50.1600.1. 27bbee07-9442-48ce-af3e-eb7dabaf80ea

09/19/2011 15:49:59.95 OWSTIMER.EXE (0x18CC)                   0x2238 Project Server                 Provisioning                   6975 Information Provisioning 'zzzzzzzzzzzzzzzzz': Databases specified for site 'ProjectServer' are as follows: Published: Name = 'zzzzzzzzzzzzzzzzz_Published', State = NotEmpty Draft: Name = 'zzzzzzzzzzzzzzzzz_Draft', State = Empty Archive: Name = 'zzzzzzzzzzzzzzzzz_Archive', State = Empty Reporting: Name = 'zzzzzzzzzzzzzzzzz_Reporting', State = Empty 27bbee07-9442-48ce-af3e-eb7dabaf80ea

Shortly after this, guess what:

09/19/2011 15:49:59.95 OWSTIMER.EXE (0x18CC)                   0x2238 Project Server                 Provisioning                   7070 Critical Provisioning 'zzzzzzzzzzzzzzzzz': One or more of the databases already contains schema. When editing or creating a Project Server instance, you may specify: * Four databases that do not exist * Four existing, blank databases * Four existing Project Server databases of the same version from the same installation. Combinations of blank, new, and existing databases are not allowed. 27bbee07-9442-48ce-af3e-eb7dabaf80ea
Subtle but important difference here... this second provisioning job that appears to have been initiated part way through the initial DB provisioning job now sees that the Published database = NotEmpty state.

Hypothesis here is that the first correct provision job has begun to build the  DB schema, and then been kicked out by the erroneous second job, which finds a mismatch set of databases and bombs out.


So far it appears that the June CU resolves this issue, but I am retesting this now.  be back after the break

Friday, September 9, 2011


Sometime over the last couple of weeks - whilst running some BI-focused EPM Demos - I have come to realise that sometimes no manner of demo will get people interested when they just want to jump to the good stuff, the charts and graphs, the decomposition tree, indicators and traffic lights and KPI's etc etc

Sometime's they just want the shiny-shiny

So with this in mind I've given my blog a spring clean and a spruce up.  Time is pushed at the moment but I intend to attempt to post as much as I can over the coming months.  As ever, there will be some trouble shooting and some how-to's that I hope people can get benefit from.

My SharePoint_Config Database fall over go bang

The other day I was tibnkering on my new demo rig (I love SSD's) and the whole OS Drive locked up on my, causing me to hard-crash the machine.

Uh-ho i thought... my VM was running at crash point... better check it over.

Lo and behold, my rig was dead on reboot.  Upon review my config_db was suspect - go figure - and I had to try and get it back (as I was going to lose quite a lot of work if i didn't).

So after a little trawl around I found the following.  I hope it helps someone else in the future:

Run this in SQL:

               alter database “Sharepoint_config” set emergency

Set DB to single user mode 

Run this in SQL to find the SPID for the connection to the Config_DB


disconnect from SQL
ensure SQL Browser service started
open SQL Mgt but dont connect to the server
New Query

               Kill SPID# from SP_WHO2 (where # is the SPID noted previously)

use Sharepoint_config
dbcc checkdb ("sharepoint_config", repair_allow_data_loss)

Run the following SQL

               SET MULTI_USER

Your DB should now be back and happy

Timesheet solutions in project server 2010 - #1 Security black holes and foibles
This is my first outing into a full blown stand-alone timesheet solution.  Yes, I’ve done the full bottom-up and top-down configurations before but never JUST timesheets.
The aim of this solution is to
- minimise the administrative overheads associated to running an EPM solution
- minimise “Project Owner” involvement in the process and thus minimise MS Project Pro licence needs
- Capture historic data (actual work and cost)
- A fixed list of “activities” per project, duplicated for work performed pre project and during the project
- Allow anyone to work on any project within their particular group/team/dept
- provide some insightful BI and reports off the back
The entry point in for this solution is the [magical] new option of automated publishing via rules, delivered as part of SP1, without which we would not be moving forward with this.
Voyage of discovery:
During the first two days of testing, I have come across the following challenges:
1 - Data Security on the Insert Row functions in timesheets (“Insert Task” and “Add Yourself to a Task)
2 - Deactivating unwanted Timesheet Add Line functions (Insert Row | Create New Task)
3 - Limitations of creating projects from templates via PWA (Project.CreateProjectFromTemplate method)
4 - curious behaviour of the auto-publish function when calculating Actual Cost  
What follows in this post is a deep-ish dive into the world of data security on Timesheet Insert Row options

1 - Data Security on the insert row functions - ”Insert Task” and “Add Yourself to a Task
I started this process as I wanted to ensure that the menu options for these functions was scalable enough to be able to take the number of projects I wanted to throw at it, but also to confirm that I could get projects OUT of the list again at the appropriate time, retaining control throughout the project lifecycle.
These options are really important to me in this scenario as I need team members from certain departments/teams to be able to add tasks from the dept/team projects into their timesheets and begin to assign time, as we will not be individually resourcing tasks.  We will also not be scheduling tasks as such so the ability to re-add a task (insert task function) is also key.
My starting assumption here was that the projects from which you can select tasks would be the projects you have permission to see (with appropriate Category permissions for the functions) via My Tasks so to prove this I temporarily elevated the My Tasks category to see ALL projects.  I then created some dummy tasks in a handful or projects.
At this point - if the rule is true - I would be able to add any of these tasks to my timesheet via “Add yourself to an existing task” function.  Of course on testing this is not the case.  
How “Add yourself to an existing Task” works:
It turns out that the list of available projects form which you can pull tasks onto the timesheet is governed solely by the projects that include you in their Project Team, NOT by the application security model (which of course includes the switch “The user is on the project team” but we wont mention that).  
Although I understand the logic here, as the ability to perform this task is a category permission, it seems strange that this is not governed by category data access rules.  My working assumption is that this would also require Build Team from Enterprise permissions over the projects in order to allow the resource to assign themselves to the project team which all sounds logical and achievable within the constrains of application security in my head, but I dont develop software so…..
the agreed workaround for this is that each dept/team resource is assigned to the project team as needed, and then the team members can pick whichever tasks they need.
Note : Tied functions 
As far as I can tell so far, this function is tied to the Create new task function by category feature “Create new task or assignment” so you get either or both, which I REALLY REALLY REALLY don’t want as I need the task list is fixed and controlled across all projects (thats engineering for you) and we will be auto-approving task reassignment changes via rules.  As far as I can tell so far, my only option is to deactivate this through custom code which I am currently experimenting with.  More to follow (I hope) 
Note:  Removing Projects and tasks from the list:-
This permission/option DOES conform to the Security driven approach for locking down/closing projects.  However it appears that it DOES NOT take account of projects that have no remaining tasks open for update.  The Project and Summary Tasks will still appear in the list of available projects and tasks, but there will be no low-level tasks if these are all closed for update. T
How “Add Task” works
The Add Task function allows you access to existing Task Assignments to re-add them to your timesheet when they have “dropped off”.  This in itself is fine and dandy for me but how does it handle closed tasks and projects as if it has all my assignments in there the project list is going to get big QUICK (as I am very busy)
During testing of the project closedown procedure I notice that using [again] Category level security to attempt to close a project (using Deny) does not impact the list of projects and tasks on display.  This can only be done using the “Close Tasks to Update” function as far as I can see so far (and the project will only be hidden from view once there are no further tasks to be updated)/
A brief note on Closing Tasks and Projects:
In order to stop users accessing closed tasks during the lifespan of the project, you should use the Close Tasks to Update function as tasks are closed.  
However you are also recommended to have a strong regime for closing projects down with a DENY category once everything has been completed, to ensure that the project is removed from Timesheet Inset Row functions. 

Thursday, June 9, 2011

Return of the data-geek. EPM OLAP Cube Custom Measures

I thought i'd put rogether a quick post on Cube customisation, as this is something that I am constantly asked about. Now I am not an MDX expert at all, but there are some useful results you get from some pretty simple MDX expressions.

Firstly, custom measures are done in the OLAP Cube Database Management section of the Project Web App Server Settings page.  Hightlight the cube you want to customise and then click Configure.

Once in this section you need to scroll down to the bottom and decide which cube you want to add the measure to in the OLAP database.  Generally in most scenarios, the Portfolio Analyser cube is the best target, depending on the data you wish to interrogate.  Once the cube is selected, click Insert.

OK, so you've got a decent OLAP report (in excel, PerformancePoint Services or the like) showing data by month, but you been asked for a nice S-curve report showing Baseline, Work and Actual Work on a cumulative curve.  As with all useful things, its short but complex query that is needed.

This example would give you a cumulative WORK calculation, specifically from the earliest date possible.
>  sum(periodstodate([time].[(all)]),[measures].[Work])

This example is a little more simple and would do the same but only over this year
>  sum(periodstodate([time].[Year]),[measures].[Work])

This example is the associated Actual Work S-Curve but allows for there to be NULL values where no actual work exists - in the future - rather than the S-Curve flatlining after the last reporting period.
> iif([actual work]=0,NULL,sum(periodstodate([time].[(all)]),[measures].[actual work]))

Capacity Heatmap (Remaining capacity percentage)
A nice simple one for Excel Services conditional formatting, calculating the % of capacity that is remaining based on the Work values.
> ([Capacity]-[work])/[capacity]

Filtering a measure based on a dimension member to derive custom cube measures

imagine the scenario.  On your report you want to put the following onto a stacked column chart
- Actual Work spent on Project Priority 1 projects
- Actual Work spent on Project Priority 2-4
- Resource Capacity (as a line)

note:  Your priority field is a flat Lookup Table that doesnt have this "P2-4" grouping built in

In this scenario to easily derive these values:
1 - Create a measure for Actual Work spent on P1 projects: 
ActWorkP1                   ([measures].[actual work],[Project Priority_Project].[Priority 1])
2 - Create a measure for Actual Work spent on "everything else" besides P1 Projects
ActWorkP2-4                [Actual Work]-[ActWorkP1]

This may seem like an edge case and you can of course use a pivot table to cross analyse and filter for these values, but what this DOES give you is an interesting OLAP based source for KPI's....

Next target:  I am currently looking at how I can derive a custom measure of the actual work done on one "Administrative" task across any given timesheet period....

More to follow!