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

Problem Provisioning PWA Site after SP1

Shiny-shiny

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…..

Workarounds:

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. 

Failure to Provision (nooooooo)

Strange events today, and I am a little lost right now.

Sometime over the last two weeks (probably since my VM was switched from one machine to another), site provisioning has failed on my VM.

In this scenario I am creating a new PWA site called Bibblywibbly.  I want this to be a brand new site with clean DBs.  This fails because of mismatching DBs but if i look back through the logs i see:

- Site created

- DBs Created

- Provisioning schema against Published DB begins

- timeouts for 8sec

- Provisioning kicks off a new correlation with same settings

- reuses site

- job fails at DB check

ULS history is here:

PRovisioning Correlation begins:

Starting ProjectPSISharedApplicationTimerJob.Execute for ‘Project Server Service Application’.

Expanded path to C:\Program Files\Common Files and checked it successfully

Baseboard Manufacturer: ‘Intel Corporation’

SQM: Static lib get app data folder

SQM: Finding files like     ‘C:\Program Files\Microsoft Office Servers\14.0\Logs\Project\data??????????.dat’.

SQM: No files to upload.

SQM: Calculating file statistics for upload throttling

SQM: Static lib get app data folder

SQM: Finding files like     ‘C:\Program Files\Microsoft Office Servers\14.0\Logs\Project\data??????????.dat’.

SQM: SqmUploadAllFiles exiting (0x2260)

Provisioning ‘bibblywibbly’: Beginning ‘Create’ operation for PWA instance.

Adding event 6421 (category: Provisioning, product: Project Server) to spam monitoring list

Received SQM session notification

Starting provisioning of site ‘bibblywibbly’.

Adding event 6954 (category: Provisioning, product: Project Server) to spam monitoring list

Creating site for ‘bibblywibbly’.

Adding event 6945 (category: Provisioning, product: Project Server) to spam monitoring list

Reusing http://project.contoso.com/bibblywibbly site for provisioning bibblywibbly

Adding event 8101 (category: Provisioning, product: Project Server) to spam monitoring list

Provisioning ‘bibblywibbly’: Starting database provisioning.

Adding event 6956 (category: Provisioning, product: Project Server) to spam monitoring list

ConnectionString: ‘Data Source=demo2010a;Initial Catalog=master;Integrated Security=True;Pooling=False;Asynchronous Processing=False;Connect Timeout=15;Application Name=”Microsoft Project Server”’    ConnectionState: Closed ConnectionTimeout: 15

SQL Server ‘demo2010a’ version is ‘10.50.1600.1’.

Provisioning ‘bibblywibbly’: Server versions: Primary 10.50.1600.1, Reporting 10.50.1600.1.

Adding event 6974 (category: Provisioning, product: Project Server) to spam monitoring list

Provisioning ‘bibblywibbly’: Databases specified for site ‘ProjectServer’ are as follows: Published: Name = ‘bibblywibbly_Published’, State = NotCreated Draft: Name = ‘bibblywibbly_Draft’, State = NotCreated Archive: Name = ‘bibblywibbly_Archive’, State = NotCreated Reporting: Name = ‘bibblywibbly_Reporting’, State = NotCreatedAdding event 6975 (category: Provisioning, product: Project Server) to spam monitoring listConnectionString: ‘Data Source=demo2010a;Initial Catalog=master;Integrated Security=True;Pooling=False;Asynchronous Processing=False;Connect Timeout=15;Application Name=”Microsoft Project Server”’    ConnectionState: Closed ConnectionTimeout: 15Creating database ‘demo2010a.bibblywibbly_Published’.ConnectionString: ‘Data Source=demo2010a;Initial Catalog=master;Integrated Security=True;Pooling=False;Asynchronous Processing=False;Connect Timeout=15;Application Name=”Microsoft Project Server”’    ConnectionState: Closed ConnectionTimeout: 15Created database ‘demo2010a.bibblywibbly_Published’.Creating database ‘demo2010a.bibblywibbly_Draft’.ConnectionString: ‘Data Source=demo2010a;Initial Catalog=master;Integrated Security=True;Pooling=False;Asynchronous Processing=False;Connect Timeout=15;Application Name=”Microsoft Project Server”’    ConnectionState: Closed ConnectionTimeout: 15Created database ‘demo2010a.bibblywibbly_Draft’.Creating database ‘demo2010a.bibblywibbly_Archive’.ConnectionString: ‘Data Source=demo2010a;Initial Catalog=master;Integrated Security=True;Pooling=False;Asynchronous Processing=False;Connect Timeout=15;Application Name=”Microsoft Project Server”’    ConnectionState: Closed ConnectionTimeout: 15Created database ‘demo2010a.bibblywibbly_Archive’.Creating database ‘demo2010a.bibblywibbly_Reporting’.ConnectionString: ‘Data Source=demo2010a;Initial Catalog=master;Integrated Security=True;Pooling=False;Asynchronous Processing=False;Connect Timeout=15;Application Name=”Microsoft Project Server”’    ConnectionState: Closed ConnectionTimeout: 15Created database ‘demo2010a.bibblywibbly_Reporting’.Provisioning database demo2010a.bibblywibbly_Published as Published.
you can see above that hte provisioniong job has just created the DBs and has applied the schema to PUBLISHED DB
NOW THIS IS WHERE IT ALL GOES BADConnectionString: ‘Data Source=demo2010a;Initial Catalog=bibblywibbly_Published;Integrated Security=True;Pooling=False;Asynchronous Processing=False;Connect Timeout=15;Application Name=”Microsoft Project Server”’    ConnectionState: Closed ConnectionTimeout: 15
repeats for 8 seconds then a new correlation kicks in
Provisioning ‘bibblywibbly’: Beginning ‘Create’ operation for PWA instance.
leading to
Provisioning ‘bibblywibbly’: 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.Adding event 7070 (category: Provisioning, product: Project Server) to spam monitoring listProvisioning ‘bibblywibbly’: Failed to provision databases. An exception occurred: Provisioning failed because the databases specified did not match..Adding event 6993 (category: Provisioning, product: Project Server) to spam monitoring listProvisioning ‘bibblywibbly’: Database provisioning failed.Adding event 6958 (category: Provisioning, product: Project Server) to spam monitoring listFailed to provision site bibblywibbly with error: Microsoft.Office.Project.Server.Administration.ProvisionException: Failed to provision databases. —-> Microsoft.Office.Project.Server.Administration.ProvisionException: Provisioning failed because the databases specified did not match.     at Microsoft.Office.Project.Server.Administration.PsiServiceApplication.EnsureDatabases(ProjectProvisionSettings provset, SPSite pwaSite, String adminName, String adminEmail, ProjectDatabaseStateType& originalDatabaseState, Guid& adminGuid)     —- End of inner exception stack trace —-     at Microsoft.Office.Project.Server.Administration.PsiServiceApplication.EnsureDatabases(ProjectProvisionSettings provset, SPSite pwaSite, String adminName, String adminEmail, ProjectDatabaseStateType& originalDatabase…

New Job, New Demo Machine and BI tomfoolery

So today is my first day in my new role, working for TPG - The Project Group as an EPM Consultant in the UK.  Exciting time for me.

The new demo rig is now running pretty smoothly, having overcome some of the teething problems with an SSD based machine.  I know this is relatively new tech but come on, isn’t it about time this stuff was just - you know - easier!  this is the 21st century chaps.

The new machine spec is as follows:

Lenovo W520 Quad Core i7-2720QM 2.2 GHz + 16GB Ram

Crucial C300 SSD Boot drive + Crucial M4 SSD Data drive in the bay for VMs

Trusty Samsung 1TB USB3 7200 RPM drive for emergencies

Virtualisation

So now I am just mulling over my virtualisation options.  I’ve for VMware workstation VMs but the licencing costs just seem high for what it offers, so I am going to have a (another) look at VirtualBox to see how that flies.  I guess that’ll be something to keep me busy in the airport lounge or hotel bar one evening.

BI tomfoolery

So, the new role starts here.  Today’s been experimenting with BI yet again, especially around scorecards and KPI’s.  I’ve been trawling through the recent white paper (so you don’t have to!) 

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=27136

which is actually a very informative read, even with the few glaring errors in the instructions (well come on its over 100 pages).

Next I intend to set up some of my own sample metrics and a dashboard suite as a sample offering.  Obviously to do this I am going to have to buy a great big whiteboard for the office, which the wife is obviously going to be really chuffed with, but needs must.

Right, time to brush down a suit for tomorrows workshop.  Bring on day 2!

Rejuvenate

It’s time to resurrect my tumblr blog. This will now become my primary project server bloglet. Tumblr rules

Site Collection wont delete (orphaned record) - Sharepoint 2007