More servicesWindows Live
HomeHotmailSpacesOneCare
 
MSN
Sign in
 
 
Spaces home  Applepark LtdProfileFriendsBlogMore Tools Explore the Spaces community

Blog

June 12

Problems with Status Reports

<dry again>

Status reports have been around since Project Server 2003 (or even in 2002? - I can't remember), but I do know that I've had various problems with them in 2007, but haven't been able to specifically reproduce them in order to get a bug raised with Microsoft.

My specific scenario was that when Status Reports where deleted users were still getting reminders to send the reports; in addition other Status Reports were successfully sent but actually never received by the Project Manager!  Both of these issues started after a few false starts with Status Reports, we'd created quite a few for testing purposes and then deleted them. 

As part of the troubleshooting process I started delving into the published database and discovered that deleted status reports are not deleted, but marked as disabled.  In order to clean up the Status Reports I decided to clean up the database using some SQL scripts. 

Here's the caveat - I'm no SQL guy, so though this worked for me there are no guarantees it'll work for you.  Also it's a semi manual task involving two separate steps; someone smarter than me can probably create a single script that does the same job - if you can - I'd like a copy.

The 1st stage finds all of the records that need deleting; the 2nd stage deletes them.

1st stage

The 1st thing to do is to select the published database and then get the UID for the Status Reports that are set to False (i.e., deleted ones) - my DB is called project_live_published

-- define the database ** yours will be different**
use project_live_published

-- get the SR_UID from SR_Reports that have been deleted
select SR_UID from dbo.MSP_SR_REPORTS where SR_IS_ENABLED = 'False' 

Use this output to as part of the next select statement to find all of the Response UIDs (SR_RESP_UID) from the SR_RESPONSES database.

-- get the SR_RESP_UID for the "deleted" reports
select SR_UID, SR_RESP_UID from dbo.MSP_SR_RESPONSES where SR_UID in
('EEBF89E1-6E74-4B88-9CE6-3B7F93EEAC62' ,
'379BA18E-E98E-4FD8-AC83-5D8BF8ABF04A' ,
'87BF5F35-843E-43FC-9EF6-9BB542E8677A' ,
'7332150B-69AD-446A-A946-C08DFC57D3D9') order by SR_UID, SR_RESP_UID

The output from both select statements is then used to select the rows that need deleting in the database.

2nd stage

The following tables are effected;

SR_DISTRIBUTION; SR_SECTIONS; SR_RESPONSES; SR_REQUESTS; SR_REQUENCIES; SR_REPORTS

My SQL script looked like this.

-- define the database
use project_live_published

-- delete the records from SR_DISTRIBUTION
delete from dbo.MSP_SR_DISTRIBUTION where SR_RESP_UID in
('225E71BD-C639-441B-8FD8-1983DBBD15D7',
'086D6E61-EF43-40A8-AF4F-47982373FB88',
'A6EC490C-55EA-4145-9508-59D6749CE29F',
'D5CEEBF8-17DD-4D58-9E58-5B3587269620',
'8D77214C-CA97-4FB1-9251-5BFFDD25B775',
'17F9C2DA-69B5-45AD-9338-66624E6A79E8',
'D2138BA6-3808-434F-A3EA-7BE1BF5AE028',
'43185E36-0459-4BC6-93D1-88FF9E2E2D7D',
'77BD3939-A2D1-4F2D-97DE-9CC99D69C0CA',
'87437EA6-6576-4AF7-8EAC-A9009DD8F217',
'5082CCDE-4683-4356-BE5C-DE4F87A5DAB1',
'25061369-DD4F-4C22-A8F3-DFA4F5FAEA50',
'AF777558-241C-488B-B951-E8A3C1D04FC1',
'C1186BED-CD1B-4F4E-BD75-EE349BCBF0CE',
'1656301F-2463-4900-994D-F3621C051754',
'D82E51E9-A89A-41AF-86E1-0C0071A06647',
'B625269B-A1D6-4353-99FB-11D499AA80CB',
'AAB7359A-92DC-4416-9048-350C48BCFBC2',
'3138136B-1427-41BE-A052-3AAD97C273AD',
'49BB243C-0D97-40F0-AF54-545813FC5CC2',
'3CB7EE4D-3A94-4090-A809-74317AC42A29',
'3F61FB9E-FAE8-418C-BF22-779ACD71CE1E',
'DC628EC6-01D4-4039-A1C1-AAE4737E27FD',
'220CF8EF-6D55-4F6F-869E-AFAE68CF067D',
'F9A3CDF0-A532-44C1-AA30-B10354890A9F',
'39238C2C-F9FA-49F6-BA1C-CB1F8B1EB998',
'65BD1BC4-B386-4E1E-AE39-DF81FDA61397',
'F04C0A57-C166-43B9-BA00-EB7251074764',
'99B8B405-9153-4C36-8A16-AE35C216A224'
)

-- delete the records from SR_SECTIONS
delete from dbo.MSP_SR_SECTIONS where SR_RESP_UID in
('225E71BD-C639-441B-8FD8-1983DBBD15D7',
'086D6E61-EF43-40A8-AF4F-47982373FB88',
'A6EC490C-55EA-4145-9508-59D6749CE29F',
'D5CEEBF8-17DD-4D58-9E58-5B3587269620',
'8D77214C-CA97-4FB1-9251-5BFFDD25B775',
'17F9C2DA-69B5-45AD-9338-66624E6A79E8',
'D2138BA6-3808-434F-A3EA-7BE1BF5AE028',
'43185E36-0459-4BC6-93D1-88FF9E2E2D7D',
'77BD3939-A2D1-4F2D-97DE-9CC99D69C0CA',
'87437EA6-6576-4AF7-8EAC-A9009DD8F217',
'5082CCDE-4683-4356-BE5C-DE4F87A5DAB1',
'25061369-DD4F-4C22-A8F3-DFA4F5FAEA50',
'AF777558-241C-488B-B951-E8A3C1D04FC1',
'C1186BED-CD1B-4F4E-BD75-EE349BCBF0CE',
'1656301F-2463-4900-994D-F3621C051754',
'D82E51E9-A89A-41AF-86E1-0C0071A06647',
'B625269B-A1D6-4353-99FB-11D499AA80CB',
'AAB7359A-92DC-4416-9048-350C48BCFBC2',
'3138136B-1427-41BE-A052-3AAD97C273AD',
'49BB243C-0D97-40F0-AF54-545813FC5CC2',
'3CB7EE4D-3A94-4090-A809-74317AC42A29',
'3F61FB9E-FAE8-418C-BF22-779ACD71CE1E',
'DC628EC6-01D4-4039-A1C1-AAE4737E27FD',
'220CF8EF-6D55-4F6F-869E-AFAE68CF067D',
'F9A3CDF0-A532-44C1-AA30-B10354890A9F',
'39238C2C-F9FA-49F6-BA1C-CB1F8B1EB998',
'65BD1BC4-B386-4E1E-AE39-DF81FDA61397',
'F04C0A57-C166-43B9-BA00-EB7251074764',
'99B8B405-9153-4C36-8A16-AE35C216A224'
)

--delete the records from SR_RESPONSES
delete from dbo.MSP_SR_RESPONSES where SR_UID in
('EEBF89E1-6E74-4B88-9CE6-3B7F93EEAC62' ,
'379BA18E-E98E-4FD8-AC83-5D8BF8ABF04A' ,
'87BF5F35-843E-43FC-9EF6-9BB542E8677A' ,
'7332150B-69AD-446A-A946-C08DFC57D3D9')

--delete the records from SR_REQUESTS
delete from dbo.MSP_SR_REQUESTS where SR_UID in
('EEBF89E1-6E74-4B88-9CE6-3B7F93EEAC62' ,
'379BA18E-E98E-4FD8-AC83-5D8BF8ABF04A' ,
'87BF5F35-843E-43FC-9EF6-9BB542E8677A' ,
'7332150B-69AD-446A-A946-C08DFC57D3D9')

--delete the records from SR_FREQUENCIES
delete from dbo.MSP_SR_FREQUENCIES where SR_UID in
('EEBF89E1-6E74-4B88-9CE6-3B7F93EEAC62' ,
'379BA18E-E98E-4FD8-AC83-5D8BF8ABF04A' ,
'87BF5F35-843E-43FC-9EF6-9BB542E8677A' ,
'7332150B-69AD-446A-A946-C08DFC57D3D9')

--delete the records from SR_REPORTS
delete from dbo.MSP_SR_REPORTS where SR_UID in
('EEBF89E1-6E74-4B88-9CE6-3B7F93EEAC62' ,
'379BA18E-E98E-4FD8-AC83-5D8BF8ABF04A' ,
'87BF5F35-843E-43FC-9EF6-9BB542E8677A' ,
'7332150B-69AD-446A-A946-C08DFC57D3D9')

That worked nicely for me; however, we've just had a change of project managers so we might have to go through the whole process again!

Good luck!

May 01

How to set up permissions so that Project Managers can Save their own projects, and open all others in Read Only mode

 

This question comes up again and again, and I can't remember where I found this information from all those years ago (it applied to Project 2003), but I wrote it down and seem to use it for most clients.  It's easily adaptable for Project Server 2007 - one day I'll write it properly and update it for 2007!

Project Server Security (P2003)

Overview

Project Server Security is complex. There are two types of permissions, five places to set them, and three conditions for each permission (allow/deny/soft deny). Appendix C & D of the Project Server Administrators Guide should be used for further reference.

Permissions

There are two types of permissions, data permissions (what data the user can see) and use permissions (what features the user can use). Each permission is not readily identified as to its type. Microsoft use the terms global for use permissions and category for data permissions. Additionally, they use the term Organisational Permissions, which are a set of permissions set for the whole organisation, and encompasses all of the use (global) permissions plus the 14 data (category) permissions.

The Roles of Security Templates, Groups & Categories

A security template is a predefined list of all the permissions (data & use). This list is ordered by permission area (admin, collaboration etc). Templates should ideally be named the same as groups.

A Group is a collection of users. A single user can belong to multiple groups. The use permissions are determined at the group level, and therefore group membership is the primary factor is determining the functionality that a user has within the system.

A Category is used to define the data permissions (hence Microsoft’s term, Category Permissions), i.e. what data can a user see, what actions can they perform on that data, and which views can they use to see the data. Categories are applied to groups, a single group can have multiple categories applied to it, and a category can be applied to multiple groups.

The permissions applied to each user is therefore a combination of the categories and the group membership.

Alloy/Deny

Three permissions are allowed for any permission. They are ALLOW, DENY, and soft deny. Soft deny (blank) is implicit if neither allow nor deny are selected. If a permission is set to deny in one place the system, then that becomes an absolute deny everywhere in the system, even if the permission has been set to allow elsewhere. If the permission is blank then the decision as to whether to allow or deny the permission is made elsewhere in the system.

Server Configuration Features

This is available in the PWA Admin tab, under server configuration, in the Select the Features That You Want to make Available to Users in Project Web Access section. Either set these use permissions to ALLOW or DENY. It is inappropriate to have blank here. The permissions lists here are the use permissions.

Templates

Use the templates to define a set of permissions for a particular role. The templates contain data and use permissions. Only change a use permission here to DENY if you want to deny a permission for a particular group, that has been globally allowed (via the Server Configuration Features) above. This is the 1st opportunity to set permissions, so these should be set here to allow/deny as appropriate.

Categories

Categories define data permissions, and are further enhanced by data restrictors. Categories are assigned to groups, and a group may have more than one categories assigned to it.

Groups

The use permissions are set by the group. Assign the use permissions by applying the relevant template.

Users

Users are placed in groups. Never assign permissions to a user, troubleshooting the security will become too complex.

Recommendations

Security should be established in the following order

  • Set universal allow/deny in server configuration
  • Define the roles within the organisation, and create a security template for each role
  • Define groups and assign data permissions using the template
  • Define the categories, and assign them to the groups
  • Assign resources to groups

See figure1. below for a graphical view of this.

clip_image002

Initial setup for Acme PLC

The following was set up on the live system for Acme PLC and used as a basis for security.   What I've done here is create categories called GROUP_NAME - See Everything.  Within the category set up you need to select the button that says - See all future Projects/Resources in the Database.

 

Templates

Resource managers - see everything

Execs - see everything

Portfolio managers - mod all (no admin)

Project managers - see everything

Team members - see everything except money

Categories

Resource managers - see everything

Execs - see everything

Portfolio managers - mod all (no admin)

Project managers - see everything

Team members - see everything except money

Groups

Resource managers -> CATEGORY = Resource managers - see everything

Executives -> CATEGORY = Executives - see everything -

Portfolio managers -> CATEGORY = Portfolio managers - mod all (no admin)

Project managers -> CATEGORY = Project managers - see everything - *When you make this assignment for the  category you need to enable the category permission to open all projects in the database, but not to save them*

-> CATEGORY = Project managers

Team members -> CATEGORY = Team members - see everything except money

April 24

Changing the Project Manager

 

During the lifetime of a project it is sometimes necessary to change the Project Manager (due to a leave of absence).  The process to do this has changed between 2003 and 2007. 

There are three areas to be concerned with

1.  Changing the Project Manager; Changing the status manager; replacing any PM resources.

Before you start on any of the above, make sure any updates to the project have been accepted, and that any changes have been fully published.  This results in lots of emails too, so worth making team members aware of what is happening.

1.  Change the Project Manager

This is done in PWA.  Select the project and Edit the Project Properties, and change the Owner to reflect the new Project Manager.

image

2.  If necessary, change the Status Manager.

As the new project manager, open the project in Project Professional.  Insert the Status Manager Column, and change the status manager for each task to be the new project manager. 

image

Note that it is not possible to change all the tasks at once, but it is possible to use CTRL-D to copy the changed status manager field down.

image

3.  If the old PM has assignments on the task, then these need to be changed too.  There are 3 scenarios for the task

a.  The task is completed and has zero remaining work - these tasks will not be effected.

b.  The task is partially completed, therefore has Actual Work and Remaining work values associated with it. 

c.  The task is not yet started.

image

The approach to replacing resources for all three scenarios is the same, and this should be done by using the Replace function within the Build Team from Enterprise Tool.

image

Here we are going to replace Ralls Kim with Allen Tony, by highlighting them both and clicking on the Replace button.  If Ralls Kim already has actual work associated with a task, then the following information box appears.

image

If a task is 100% complete, Project asks if you want to move the work to the new resource.  Note it doesn't tell you which task, and the correct answer is Cancel.

 

image

For Task 1 which already had actual work on it, Project assigns the remaining work to the new resource.  This is shown in the split lower split window for the Task 1.  (in our system here Allen Tony has limited availability so the task has been been split and automatically rescheduled).  The actual work completed by Ralls Kim has remained in place.

image

Finally, Save and Publish the project to make Team Members aware of their new responsibilities.

February 01

How to move a sub Project Workspace to another programme site.

Introduction

This blog details how to move the WSS workspace between sub-sites. This may occur if a project is moved between programmes (setting up a programme is discussed in this  blog here http://appleparkltd.spaces.live.com/blog/cns!EB0688A7A6F04E1D!307.entry 

Process Overview

By default, Microsoft Office Project Professional 2007 creates a project workspace site on Windows SharePoint Services 3.0 when it publishes a project. When a sub-project creates a workspace, the user has the option of also creating the project workspace as a sub-site of the programme. This creates a hierarchy of sites as shown in Figure 1, where Programme1 has two sub projects (A & B), and Programme2 has two sub projects (C & D).

clip_image002

Figure 1 - Programme and PRoject Workspace relationships

If Project C is moved between programmes (due to reorganisation or setup issues) then the WSS site by default  not moved. The following steps show how to move the WSS site. The example will show Project C moving to be part of Programme1 (Figure 2).

clip_image005

Figure 2 - Project C has become part of Programme1

In order to move the Project C sub-site to sit beside Projects A & B it is necessary to perform the following steps.

1. Export the original site to a file

2. Create a new site as a placeholder for the migrated site

3. Import the original site to the new placeholder site

4. Change the site address to reflect the new site in the Manage SharePoint Sites page

5. Delete the original site

Export the original site to a file

On the Project server, open a command window and navigate to the following location

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN

Type in the following command

stsadm –o export –url <url> -filename <filename>

The <url> is the url of the site that you wish to export and the filename is the output file.

When the command has finished there should be a file called <filename>.cmp in the directory.

Create a new site as a placeholder

Navigate to the programme workspace where the new project is to be located, and create a new sub-site from the site actions tab. The site that you create must be a Project Workspace, and for ease, the URL name should match the original URL. Finally check the radio button so that the site is not displayed in the quick launch area.

clip_image008

Figure 3 - Create the new placeholder site

Verify that the new site has been successfully created by accessing it.

clip_image011

Figure 4 - Verify The new site is created – Note the Breadcrumb showing Programme1

clip_image014

Figure 5 - The three subsites now show under the Programme1 workspace

Import the original site into the new site

Once the new site has been created, it is time to import the exported data from the original site. This is a similar process to the original export.

On the Project server open a command window and navigate to the following location

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN

Type in the following command

stsadm –o import –url <url> -filename <filename>

The <url> is the url of the site that you wish to import and the filename is the input file.

When the command has finished, refresh the new project workspace site and check that the new content exists.

clip_image018

Figure 6 - Note the imported content on the new site

Change the site address to reflect the new site in the Manage SharePoint Sites page

In order to link the new site to the project, it is necessary to change the site address. In PWA, navigate to Server Settings | Project Workspaces. Select the relevant workspace and click on Edit Site Address. Modify the address so that it points to the new Project Workspace within the correct programme.

clip_image020

Figure 7 - Modify the site address to reflect the new site

Once the site address has been modified, click on Synchronise to update the permissions on the new site.

Delete the original site

The original site still exists and so it needs to be deleted in order to remove it from the original programme. Navigate to the site, and within Site Actions | Site Settings and then Delete this site. Caution, ensure that you are deleting the right sub-site, and not the programme site.

December 27

Creating the Perfect Plan

Introduction

Okay, so there is no such thing as the perfect schedule, but there are good and bad practices for creating a schedule using Microsoft Project. Some other tools don’t allow some of these bad practices, but Project just goes ahead and lets you do it – this allows lots of flexibility but if you’re not exactly sure what you are doing then it can lead to some “interesting” results. So here’s my list of best practices for using Project, and this might get you a little closer to the perfect schedule. There are a couple of tools which will help with some integrity checking of project plans, either for Project Server or for the client, post a comment or email me if you want further details and prices.

Learn the formula that Project uses to schedule

Project uses the following formula for calculating the schedule; Work = duration * units. Learn it, understand it, do the algebra and enjoy it! Without this understanding you will always be frustrated with Project.

Set the Project Start or Finish date

Instead of placing a constraint date on the 1st task in a project to define when the project starts, set the Project Start date or Finish Date as required.

clip_image002

Figure 1 - Set the Start or Finish date of the project

All tasks should be linked

Following on not linking summary tasks, every task such have both a successor and predecessor, except of course the last task and first task. Thinking about these relationships help you identify the relationships between the tasks.

Minimise the use of constraint dates

Using constraint dates stops Project from dynamically scheduling the project schedule. Instead, learn to use the different task types and scheduling options, including FS, SS, FF and SF types, plus lags times (in terms of days, % of the task durations and –ve / +ve lags). Sometimes it really is necessary to use constraint dates, (e.g. Must Start On), but learn how and when to use them, and read and understand the scheduling messages that Project gives you.

Note that if you manually type in a Start date, or drag a task on the Gantt Chart, then a constraint type of Start No Earlier Than is applied to the task.

clip_image004

Figure 2 - Define the Task Dependency and lag

Instead use deadline dates

Deadlines are very useful, but not often used – so instead of setting a constraint, set a deadline instead, and project will warn you if you’re going to miss the deadline.

clip_image006

Figure 3 - Set the deadline and project will tell you if it won't meet it!

Learn about task types

There are three task types, fixed effort (the default), fixed work and fixed duration. If you don’t understand what they mean, then I suspect you don’t understand the scheduling formula of Work = duration * units either.

Milestones tasks should not have resources assigned to them

Again, I see this with customers and it really is bad form. Milestones should identify a point in a project (often a deliverable, e.g. “code complete”), and hence don’t need resources assigned to them.

Don’t assign resources to summary tasks

In fact, don’t assign anything (costs, resources, % complete etc) to summary tasks. Summary tasks are just that, summaries of the tasks below, so if you manually change any of the values then the values of the summary tasks won’t make sense, so don’t change them!

Don’t link summary tasks

How many times do I see customer’s doing this? It won’t cause corruption if you do, it’s just that really you should only link actual tasks, rather than summary tasks. So instead of linking summary tasks (which are often phases in a project) consider creating a milestone exit task and a start task for each phase.

Project Auditing tools

There are various commercial auditing tools available. The following screen shots give you an idea of their potential.

clip_image008

Figure 4 - Server based auditing tool

October 13

Fun with Analysis Services!

<dry - again!>

Gee'd on by too many nights in hotels, I started briefly messing around with Analysis Services, and I started thinking that there must be so much you can do with this tool than the basics that you get out of the box. 

My main reason for this was that I wanted to get a view of availability in terms of days (rather than the default hours) for specific resources, and in particular, I was interested in people who are over allocated, i.e., have a negative availability.  So I'll show you how to build this type of view, though once you get into it, you'll learn a whole lot more, just as I have.

So, start off with a blank view using the MSP_Portfolio_Analyzer cube.

Then, making sure you have the tool bar showing, select the Calculation button and create a new calculated field.

image

The following formula calculates availability (you can put this as a calculated field in the cube too) and then divides it by 7 (the number of hours in each day as defined in our Enterprise calendars)  

([measures].[capacity] - [measures].[work])/7

image

Click Change and the value will appear in the data section of the Pivot Table.

image

To clear up the decimal points, click on the format tab and type in #,# in the Number field, this rounds the days to the nearest whole day.

image

and puts in the separator for the thousands...

image

Now, add the resources as row fields.  Obviously you may want to apply a filter, probably by RBS, or, if you've set one up, OBS or similar.

Adding a time dimension is required so that we can view over allocations over the coming weeks/months.

image

To highlight the resources with negative capacity, we can format the cells and also group them by sorting them in either ascending or descending order.

image

image

All over allocated resources are grouped on the pivot table (and consequently any pivot chart).

Further more, we can drill into the month and get weekly details, which can be graphed too, so we begin to see by how many days each resource is over allocated in each week.

image

image

Enjoy,  Ben.

August 31

Master Projects

<red plonk>

I have to admit, I do like Master Projects, Microsoft appear to have got these right in this edition of Project Server and overcome the limitations of double resourcing that existing in 2003. 

History

Master Projects have been around for ages, and have generally been used to get a programme type view of all the work across multiple projects.  This was great for a single user, but broke in the 2003 server module.  Master Projects are created by inserting (Insert | Project) existing projects into a new project plan, and thus allow the viewing of multiple projects at once, and hence brings the power of the project client to bear on a programme (filters, views etc).

image

 

PS2007

With EPM2007, Microsoft still use the Master Project concept, no-where do they call them programmes and hence Microsoft don't have a separate concept for a Programme Manager.  However, Master Projects can be used for programme management, but as with all things EPM, the programme needs to be set up correctly in the 1st instance.  I'll run you through that setup for a fictional piece of work.

Set up the Programme Outline...

The "programme manager" is basically a project manager, they have the same permissions etc.  You might want to create a separate Programme Manager group within PS2007 to identify the programme managers, but this is not necessary.  So, in order to create the programme, the programme manager creates the programme (or master project) and then inserts the projects into the programme and creates the necessary WSS sites. 

Create the Programme.

I would start by defining the programme tasks.  For this fictional programme (called Heathrow Programme), I've created a single fixed duration task called Programme Management which has the programme manager allocated 100%.  I've then created a weekly meeting which has the other resources (project managers in this case assigned to it).  The screen shot below shows this.

image

The next task is to Save and then Publish the programme.  Publishing the programme and then the projects in the right order is paramount so that the project WSS sites can be created as WSS subsites.  Note at this stage I've not yet set up any sub projects, but that's okay.

image

If you use WSS, then create the WSS site when you publish the programme.

Once the programme is published, it's time to create the projects.  Do so within Project by clicking on CTRL-N for a new project.  Create the project tasks in the usual way.  Here's one of the projects that will be part of the programme.

image

Then save and publish the project.  When it's published, be sure to select to provision the workspace as a sub workspace of the Heathrow Programme.

image

Repeat this for any other projects that are part of the programme.

Once all the projects are published, close them and return to the master project.

In the master project, click Insert | Project, and select the projects to insert, one at a time. 

image

Once the projects are inserted, they can be expanded and viewed as below.

image

The next part of the process is to save and publish the Master Project again.

That's the set up of the master project completed.

WSS sites

When we published the sub projects, the WSS site was provisioned as a sub set of the Heathrow Programme.  These can be viewed as sub sites within the Heathrow programme.  This allows for all information in the programme to be kept in a single place.

image

Project Centre Views

Within the Project Centre, it is possible to view the master project.  Drilling into the Heathrow Programme allows us to see both the tasks for the programme and the tasks within each sub project. 

image 

There is also the option to show or hide the inserted projects from within the project centre view.

image

OLAP cube details.

When the OLAP cube is built, the Programme is handled correctly as can been seen from the following screen shot.  The Heathrow Programme expands to show the work in the sub projects for each resource.

image

Deliverables.

As discussed in a previous article, deliverables and dependencies can be created between projects.  This links nicely into the programme concept.

Summary

If you plan and take time to set up master projects correctly, they'll provide another level to your project management capabilities.  As usual with Project and EPM, a little bit of planning and forethought will go a long way.

Enjoy,   Ben.

July 31

Deliverables

<budvar>

Moving back to the scenarios, I wanted to give a rounded overview of Deliverables.

Historical implementation of deliverables.

A deliverable is a tangible and measurable result, outcome, or item that must be produced to complete a project or part of a project. Typically, the project team and project stakeholders agree on project deliverables before the project begins  Examples of deliverables would be

  • A set of training materials
  • A prototype product
  • An agreed statement of work

Quite often, a deliverable or set of deliverables are tied into specific project phases, and logically that is a sensible thing to do; however, there is no technical requirement for this.

Historically, we could always define deliverables within our project plan, we'd just call them milestones.  So for example, I could create a project plan to produce trainining materials, the last task of which is Training material Produced, which is a miletone.  Effectively this is our deliverable. 

Project Plan to create training materials

There are at least three limitations to the above (historical) method of defining outputs. 

  1. It is not very easy to see just the deliverables.  You need to create a custom task field and then create a filtered view. 
  2. You need to have access to the project plan in order to view deliverables, so sharing the goals and status of the deliverables with team members, stakeholders etc isn't that easy. 
  3. If you are trying to manage inter-project dependencies based upon deliverables, then a change in the schedule of one project plan will cause a scheduling shifts in other project plans - this is confusing to many project managers and it is time consuming to investigate.

Microsoft's implementation of deliverables.

Now, lets consider Microsoft's implementation of deliverables.   Microsoft have utilised the WSS 3.0 infrastructure to hold the deliverables, and as such deliverables are published as a SharePoint list of committed dates.  So, you cannot create deliverables until the project is published, because it's at the publishing stage that the option to create the WSS site appears. 

If we look at the three limitations defined above, we can see if they've managed to solve the historical limitations

  1. A deliverable name field (and various other fields) are available in the table section of Project, so it's very easy to create a view that shows any deliverables.  Also, though Microsoft don't ship a pre-configured filter, it is very easy to create a filter called Tasks with deliverables that just shows those tasks that have a deliverable attached to them (I'll show you how to do this later)
  2. Deliverables are published as a SharePoint list of committed dates, so anyone with access to the Project Workspace can see the deliverables and dates.  They are displayed in the SharePoint Gantt view, so they appear in a familiar format
  3. Other project managers can create a link to your deliverable, thereby modelling the inter-project dependencies, without causing any scheduling shifts between the project plans

So, given the above, deliverables appear to be a very useful little peice of functionality.

Creating a deliverable from within Project Professional

There are two ways to create a deliverable for a project, the first is to do this from within Project itself, the second is from the Project Workspace.  I'll concentrate on doing this from Project.

Once a project is published click on the Collaborate | Manage Deliverables menu.  This opens a new Deliverables task bar...

From here, it is straight forward to create the deliverables for your project. 

If you link a deliverable to a task, then the deliverable inherits the task name, start and finish dates.  You can change these dates to reflect the committed deliverable dates (as opposed to the task dates).  This allows you to build some "slack" between your published deliverable dates and your internal scheduled dates for doing the work.  The deliverable is shown on the Gantt chart view, and an icon is displayed in the indicators column.

In the above screen shot, a deliverable has been created called Deliver training material to customer, with a deliverable start and finish date of 10/25/07.  The deliverable has been linked to the milestone task of Training material Produced.  Note that if the schedule slips, then the deliverable dates will not be effected.

From within the Project Workspace, the deliverables are displayed as a Gannt chart.  This view of the project deliverables would be the view that stakeholders, team members and other interested parties would see.  Note that in the screen shot below, the Deliver training material to customer deliverable is shown as a milestone, as it starts and finishes on the same day.

 

 

Creating and amending deliverables from within the PWA browser

As previously mentioned, it is possible to create a deliverable from within PWA.  This is useful if you have a role such as a client manager, or programme manager who is responsible to determining the deliverables that the project has to deliver, and wants to detail these on a Gantt type interface.  The project manager is then able to synchronise these deliverables with the project plan.  It's an easy way for the programme manager to collect and detail the deliverables, but I think from a process perspective I wouldn't want to rely soley on this.

To create a new deliverable, click on the New button and fill in the relevent information.  Within this UI the you can add a fuller description and, more importantly, attach a file to the deliverable.  The file could be an artifact such as a customer sign off or similiar.  Once created, deliverables can be amended using same UI. 

All the deliverables are displayed in the Gantt type view, but note, this is not a dynamic schedule, the Gantt view just happens to be one of the views available within WSS3.0, so EPM takes full advantage of it.

Red exclamation mark...

Deliverables are held both in WSS and in the project plan.  Project Professional checks for discrepencies between the project plan and WSS, and if it finds any, it flags these up, and begins to guide the user as to what to do. 

 

Essentially, it makes sense to have both the WSS and project plan deliverables synchronised, so after reviewing the differences between the WSS site and the project plan and making relevant adjustments, the project manager can select to accept all server changes.

Once this has been done, the deliverable is held within the project plan, but it is not linked to any tasks, and is not graphically displayed within the Gantt chart.  Editing the deliverable allows it to be linked to a task,  and once this is done, it is displayed in the Gantt chart. 

Deliverable fields & Filters

Each deliverable has the following fields associated with it, and they can be displayed in the table section of Project Professional.

  • Deliverable Finish
  • Deliverable GUID
  • Deliverable Name
  • Deliverable Start
  • Deliverable Type - indicates whether a task is a deliverable (1), is a dependency on a deliverable task (2), or is neither (0).

The Deliverable Type field is useful because you can create filters and groups on the deliverable type.   To create a filter to see what deliverables the project is going to deliver, create a filter with the following values.

Applying the filter quickly shows us the tasks that had deliverables associated with them.

We can modify the filter to quickly identify those tasks that are due to finish after the deliverable is due to start, ie we are going to fail to deliver these committed deliverables.  Create a filter as per below.

Assuming our project plan changed, and re-working the CBT content took 3 weeks, the relevant area of the project plan would look as per below (note task

If we apply the "Tasks with late deliverables" filter, only task 10 (plus the summary task 1) will show.  This gives us the opportunity to focus quickly on those tasks that we have committed deliverables to but that we are going to be late on.

 

Dependencies on deliverables

So far we have concentrated on pure deliverables, but we can extend this, but creating a dependency on a deliverable, in short, this is a way to manage cross project links, and therefore to manage programmes.  A dependency creates a link from one project, to a deliverable to another project.  In our above example, I've got a project that registers delegates for our CBT, however, I cannot register them until I have uploaded the course material for our delegates perusal.  So, I create another project plan called Register candidates, and then create a dependency from that plan to the deliverable Training Material uploaded to website.   After I have saved the plan, I can select the option to Manage Dependencies on Deliverables

 

And then Add a new dependency

And link it to the relevant task

This External dependency is a type 2 dependency (see dependency type above), and is represented in the Gantt chart as differently to the internal (type 1) dependency.  The default colour is yellow as shown below.

Again, we could create a filter that shows which dependencies are going to cause us issues within the project plan.

Reporting

Deliverables are available within the OLAP cube, so for each Project/Programme etc you can see the number of deliverables.  It is all relatively easy to see so I'm not going to attach any screen shots from the OLAP cube.   This is good from an analytical point of view, but it's not much good if you actually want to see a description of the dependency etc.  Unfortunately, there is nothing out of the box that gives this descriptive information, so SQL Reporting Services becomes your friend here.  Fortunatley, the Project Server SRS Reporting Pack gives a solid example of deliverables reporting (project give and get) so it is worth downloading it from http://blogs.msdn.com/project/archive/2007/01/30/sql-server-reporting-services-report-pack-for-project-server-2007.aspx.  Again, it's relatively simple once the reports are up and running, so I'm not going to attach any screen shots here (they are available as part of the Reporting Pack anyway).

 

Limitations

There are pobably a few more limitations in addition to the ones below, if you can think of any, let me know.

  • If you link deliverables to a task (and I think this is a good idea), then you are limited to one deliverable per task
  • Deliverables are stored as a WSS3.0 list and therefore can only be created once a project is published
  • You cannot create dependencies on a deliverable between tasks within a master project, so in order to use dependencies on a deliverable within a programme, open the individual project file, rather than trying to do it within a master project.

Links

Chris Boyd's Project Programmability blog on deliverables - http://blogs.msdn.com/project_programmability/archive/2007/02/19/working-with-deliverables.aspx

Credits

Trebb Gate & Chris Boyd; plus others I will have forgotten so please accept my apologies.

Thanks -

any enjoy....  Ben.

June 25

Migrating from MSDE to SQL2005

<black sheep>

This blog is a move away from the scenario based blogs on PS2007 that I've been writing, instead it's an overview of a situation that I think will become more common as organisations upgrade their project server databases (and WSS2.0) to SQL2005 (either from SQL2000, or in some cases there is a requirment to do it from MSDE).  Normally this is done by the IT guys, but of course it's not as straight forward as they first think.

This blog comes about from direct customer experience, and I don't necessarily have all the answers to hand, but it describes the approach I took and the pitfalls along the way.  Also, most of it is written from memory so use this as a guide only!

Existing environment - the customer had a single server installation of PS2003 SP2a which had been running for about a year.  The installation used MSDE and WMSDE on the production box, both installed by during the initial installation.  MSDE is used for the Project Server databases, and WMSDE is used by WSS2.0 (the main differences between the two databases is that WMSDE removes the 4GB size restriction)

Desired environment - a two server installation, leaving the existing application server alone, but moving the databases to a dedicated SQL2005 database server.

Installation steps

Step 1 - install SQL on the database server

The first job was to install SQL2005 SP1 on the new server.  This was just a quesiton of following the instructions.  The SQL2005 setup is  different from SQL2000 so if you are not used to it then try it first.  SP2 had just been released, but we decided it was too risky to include this in the equation, so we stuck with SP1.   The SQL2005 install includes Analysis Services, so everything is nicely tied up in one installation and one SP install.  There aren't any instructions for installing SQL2005 for use with ProjectServer 2003, so I've given you a few screen shots to help.

 

At the end of setup UI, SQL is ready to install and it gives a brief summary.

Once the database is installed, it needs to be managed.  You need to install the workstation components in order to install the SQL Server Management Studio.

Once everything is installed, it's time to install SP1.

Step 2 - create a new PS database.

My customer wanted a test environment creating in addition to the live environment, so I created a new DB and then ran the setupdb.cmd command to create the PS tables etc.  This has the added advantage of create the correct roles for ProjectServer.  The standard websps.sql script that setupdb calls need to be modified as the SQL statements between 2000 and 2005 are different, and websps.sql needs to be modified as below. 

    • Line 8781: select WASSN_ID from MSP_WEB_ASSIGNMENTS WAT2 WITH (index...
    • Line 19456: select WASSN_ID from MSP_WEB_ASSIGNMENTS WA WITH (index ...
    • Line 26315: select WASSN_ID from MSP_WEB_ASSIGNMENTS WAT2 WITH (index...

The full details of these modifications can be found in the references section

Once the database is created, you'll need to upgrade it to SP2a.  Again, you'll need to modify websps.sql in the SP2a installation directory as above.

This should give a configured database, and allows us to test the connectivity from our application server to the db server.

If your existing system isn't on SP2a, then I would perform an SP2a upgrade prior to moving any databases.

Step 3 - restore the existing MSDE database

Take the existing database from MSDE and restore it onto the db server.  For this I simply stopped the MSDE service, copied over the database files, and attached them an empty database on the db server.  Very simple indeed.  I've seen posts that say that the database needs to be run in SQL2000 compatibility mode, but this wasn't an issue for me.

Step 4 - use Editsite to configure the ProjectServer database info.

I restarted MSDE on the app server, and then ran editsite.  Just to be sure, I always run editsite to check everything is okay.  It's also a good way to verify database names and role names etc.  I made sure using Editsite and SQL Management Studio that the roles were correct etc, and then edited the existing ProjectServer instance in Editsite to reflect the new db server.  This part of the installation involved lots of checking and tweaking of the roles etc before getting it working.

Step 5 - migration of WSS databases

This should have been simple, as there are various KB articles (referenced) on how to move WSS2.0 databases.  However, I was unsuccesful in being able to move the databases and retain the integrity of the system, I think in part because when you do a WSS2.0 install onto WMSDE lots of decisons are taken for you, and I couldn't access the configuration database once I'd moved it.  So the upshot was to export the WSS data, run wsswiz to remove the ProjectServer templates (I'm not sure if this is exactly necessary but I did it anyway) then remove WSS2.0 from the app server, remove WMSDE, reinstall WSS2.0 and point the installation to the new db server, run wsswiz to reconfigure WSS and PS to talk to each other, install the WSS SP2a upgrade (from ProjectServer SP2a), run PSCOMplus to set the identity for WSS, link PS to the new WSS instance (via the ProjectServer Admin page), and import the WSS data (sites, docus etc) and then link the WSS sites back to the projects.  All in all there is a lot to remember here, and not too much is documented.   How I love WSS!

This gives us a system that is now does exactly what we had before, but on a new database server.

Step 6 - Analysis Services

ProjectServer 2003 needs the following hotfix applied before it will connect to analysis services - KB 905386.  Note the scripts detailed will need to be run on each projectserver database.  There is much to download and update here, so expect it to take a while.  Plus MS can take 4+ hours to get the hotfix to you, so get it a few days beforehand.

Step 7 - Test environment

Using editsite, a test environment was created that used the original database I created using the setupdb.cmd command.  Remember that this database needs updating to SP2a, plus have the analysis services hotfix scripts run against it.  I had big issues here, the existing migrated database would connect, but not the new one, and I kept getting the following error "Cannot connect to <database> on <server> server" .  It came down to resetting the passwords for the SQL users (MSProjectServerRole etc).  Once this was fixed everything worked okay, but it stumped me for a while as I checked and rechecked everything.  Plus the error came back immediatley, and I didn't at first think it was a permissions problem. 

References

SQL statement modifications - http://blogs.lv0.net/mpatest/archive/2005/11/18/182.aspx

Moving WSS databases - http://www.microsoft.com/technet/windowsserver/sharepoint/v2/movewsdb.mspx; KB 905386 

Analysis Services hotfix - KB 905386; http://support.microsoft.com/default.aspx/kb/921116/

SQL2005 and ProjectServer 2003 issue - kb917401

Credits - thanks to Brian Smith, Bill Raymond and Kate Simpson and my customer - they are all unwitting collaborators.

The end

As is usual, 5% of the work above took 90% of the effort, it's always just one or two snags that take the time!

enjoy....  Ben.

May 21

Timesheets - What are they, what do they do?

<Oyster Bay Merlot, NZ>

Introduction

Timesheets are a completely new area for PS2007.  Okay, so PS2002/3 had a button labelled "Timehseets", but the main function was task statusing, rather than a true timesheet system.  So, Microsoft have invested a whole lot of time in this functionality, but the Timesheet functionality is therefore a v1.0 release, so expect lots of learning, some issues and some features that are lacking.  There's a lot to timesheets that could fill a book, so I'm just going to try and give an overview here.  Also, lets just be realistic about timesheets, the technical peice is the easy bit, embedding a timesheet process into any organisation is more difficult.

Timesheets vs Tasks

The 1st thing to understand is that Timesheets are a completely separate process to task updates.  Task updates (or task statusing) updates the task assignment with the relevant details as per the tracking method defined for the project or on the server (% complete, work done and work remaining or hours of work done per period).  Timesheets information is collected so that line managers can verify the hours worked per period, and can include both project work and non project (unverified) work.

Timesheet process

Nobody really likes completing a timesheet (having to complete a timesheet was a contributing factor for me leaving an employer once, long long ago...) so one of the design goals of timesheets is to have the ability to create a timesheet and prepopluate it with a single click.  Imagine it's Friday afternoon, and you have already completed your task updates (as these have been the drivers for your work during the week), then creating a timesheet pre-populates it with the tasks that you have have either actual work or planned work on this week.

Prerequisites

There are several things that need to be set up before you implement timesheets.  These are all available under Server Settings | Time & Task Management.

Timesheet Periods

Timesheet periods require defining before timesheets can be created - these are created in Server Settings | Time & Task Management | Timesheet Periods.  It's easy to create a bulk set of periods, but remember to use a prefix or suffix in order to make the timesheet period unique, eg TSCY07 for timesheets in calendar year 07.  Once the timesheet periods are defined then you are able to create timesheets.

It is possible to set timesheet periods to closed, when this is done users can no longer enter time for that period.

Administrative Time

Non-working time or adminstrative tasks (holidays, training, admin time etc) are now part of the timesheet function, administrative projects (as existed in PS2003) no longer exist (unless they are imported as part of a migration).  Administrative time is entered as part of the timesheet, and can be set up to require approval from the line manger as required (eg vacation).  Administrative time, when created, can have a category of working or non working.  If the work type of the administrative time category is non working, then a calendar execption is created for the resource, thereby affecting the capacity of the resource.  This is a very neat feature which doesn't appear to be documented anywhere! (no doubt someone will point me to some documentation on the web somewhere).

Creating a timesheet

To create a timesheet, select the correct timesheet period (this will normally be the current period).  Clicking on Create Timesheet will create the timesheet with the default settings.  This setting is within the server settings and is either one of the one of

  • Create with default settings - the timesheet will display the Administrative Time categories marked with Always Display and the option selected for Default Timesheet Creation Mode.
  • Create with tasks - the timesheet will display the resource's current assignments. 
  • Create with projects - the timesheet will display the projects on which the resource has assignments.  No tasks are displayed.
  • Do not autopopulate - the timesheet is blank.

Clicking directly on the Create Timesheet link with create it with the default settings, but it is also possible to select a drop down list to select one of the four options abolve.

I suspect most organisations will set the default setting to be Create with Tasks, and once created the user will be able to modify it to reflect the work they did during the week.  The beauty with Create with Tasks is that it will take the task assignments that you and bring in the actual work, or planned work, and place that work in the relevant day in your timesheet.  Thus, for the majority of cases, a user should only have to add in non-project work (eg administrative time) or unscheduled (or unverified line items) in order to complete the timesheet.

When you have created the timesheet, the user begins to complete the information as required.  Again, organisation processes will determine exactly what is needed.  The important thing to remember here is that users are looking to account for their time in the working week, so we're looking here to fill in 40 hours (or whatever your standard is).

Here is the a standard timesheet created for Kim Ralls (one of the users in Microsoft's sample EPM system).  The important thing to note here is that Kim's planned time from her task assignments has been brought directly into the timesheet.   The example above actually shows that the resource manager in Kim's organisation (Litware) isn't really doing their job very well, as Kim has 24 hours of planned work each day.  Kim would obvioulsy modify the timesheet to reflect the actual work that she did.

Kim can also administrative (or non-project) time as described above.

Options

There are various options for filling in a timesheet, they can be found under the Actions menu and whilst completing a timesheet line.  Generally, these all have to be enabled by an administrator within the Server Settings

  • Creating unverified timesheet items - these can be used to enter time into the timesheet that is neither project time or adminstratrive time.
  • Identifying billable/non billable time
  • Identifying overtime/non-overtime time
  • Identifying additional categories of time - e.g., this could be used to categorise External customer time vs Internal custer time

Obviously, these settings have to be set appropriately by the adminstrator and will require maintaining.

Submitting the timesheets

Timesheets are submitted to your timesheet manager, and this field is defined, not by the RBS, but by a specific Timesheet Manager field held against each resource and editable via the resource centre.  If a resource is set as their own timesheet manager, then the system autogenerates the timesheet approval, otherwise the timesheet is submitted to the defined timesheet manager and they have the opportunity to either approve or reject the timesheet.  If they don't have the perrmissions to approve the timesheet, then the timesheet is marked as acceptable and is routed to the next approver as defined as the timesheet manager of the 1st approver.  If the timesheet manager blank then you will be prompted to enter a user to approve your timesheets; any administrative time will be auto approved. 

Reporting

There are no out of the box reports for timesheets.  There are two options, one is to use Analysis services and create a report that meets your needs, and the other is to develop a report using a tool such as SRS.

Finally, if you don't want to use timesheets at all, then you need to do two things to make them disappear.

  • Make sure the timehseet manager is blank/null for all resources, this stops the system calculating how many timesheets you need to fill in on the homepage
  • Remove the link to the timesheet from the site map.

I hope this has given you an insight to timesheets, there are various options and features that I haven't covered in this blog.  Maybe another time....

Enjoy,  Ben.