Project Server Issue and Risk Data

Issues and Risks are kind of an oddball in the Project Server data world. Most Project Server data is handled within the context of the **Draft** and **Published** databases. That to a degree is a reflection of the Project client tool making the transition into the server world. 

Issue and Risk data is not tracked directly in the client tool, rather the assumption is made that the PM will use a tool such as SharePoint, spreadsheets, or some other mechanism to track these values. In the Project Server world (since it’s closely integrated with SharePoint) the assumption is that SharePoint Workspaces will be used for team coordination on individual projects, and Issue and Risk data will be stored there.

This causes a disconnect at two levels: not only is the Issue and Risk information not stored with the rest of the working Project Server information, but due to the structure of SharePoint there are potentially many SharePoint lists containing Issue or Risk data with each SharePoint project workspace containing one of each.

This makes accessing the Issue and Risk data directly problematic, especially for a reporting tool like SSRS that likes nice, well defined data connections.

## How Project Server Compensates for the Issue and Risk Disconnect

Project Server handles this problem by acquiring a copy of Risk and Issue data from the appropriate workspaces at project Publish time and placing it in the Project Server Reporting database. This very nicely pulls together that information so it can be more easily reported on, but it has some inherent weaknesses.

In order to centralize Issue and Risk data in a single location, that location has to have a consistent data definition, or schema. On the other hand, SharePoint lists by nature are highly modifiable by those with the appropriate rights. While the structure of the Issue and Risk lists in a project workspace is set to mirror that of the tables in the Reporting database via the workspace template, there’s really nothing to stop a SharePoint designer from tweaking that structure after creation, should they want to do things with their Issue or Risk lists that are not supported out of the box.

This can cause problems.

## Problems with Issue and Risk Data Synchronization

At the time Project Server is going through its Publish process, it acquires Issue and Risk items from the corresponding project workspace, and attempts to synchronize the items with the centralized tables in the Reporting database. This sync is done down to the field level.

So what happens if the fields are changed?

_If it’s a new field that does not exist in the central location_, no harm done, Project Server just ignores it. There is no attempt to add that field to the central location; that could get out of control quickly if each PM decided to have their own set of fields per project.

_If an “out of the box” field is deleted_, then Project Server becomes unhappy because it can’t find the field it wants to sync to. It assumes the list is not really its list, and ignores the items. The problem will show up as an error in the Job queue and the logs, and the synchronization for that list instance will not occur.

## What This Means for Issue and Risk lists

So the moral of the story is, you can add fields to Issue and Risk lists, but you can’t delete them.

*[PM]: Project Manager
*[SSRS]: SQL Server Reporting Services

About
Showing 10 comments
  • Miguel

    Hi Dave,

    Very interesting article, and well-explained. Just a question: Are you talking about which version of Project Server?

    Thanks!

  • Jeff Jacobson-Swartfager

    Dave is mainly focused on Project Server 2010 in this post. This is also the version of Project Server he deals primarily with in his book, Microsoft Business Intelligence.

    However, many of the best practices you will learn from Dave are applicable to Project Server 2007 and 2013 as well.

  • Tim

    Dave, great post! Do you know if there is an out of the box way to update the Reporting database with current Risk/Issue data from the Project Workspaces so that I can have the most up-to-date Risk/Issue data without having to publish every file individually? I’m trying to create a SSRS report for a weekly meeting and would like to be able to update the Reporting database with current Risk/Issue before the meeting.

  • Dave Ply

    There’s really only one way to update the Reporting DB outside of the Publishing process. This is essentially done by doing a full rebuild, and is the usual fallback method when the reporting DB gets out of synch with the Production/Draft DB’s. Here’s a link that documents the process nicely: http://www.epmcentral.com/pjadmin/pjserver10/rebuilddb.php

  • Jeff Taylor

    Does this change in Project Server 2013? I create custom issues fields and have to consolidate all the list data form all sites with a tool called lightning. I was hoping to avoid this setup.

  • Tye Trepanier

    Jeff, The same apply’s to 2013. Project Server needs the Out-of-the-box fields to remain in the Issues and Risks lists. A great test of this is to create a SharePoint Teamsite Workspace, delete the Issues and Risks lists and then create a template. The provisioning of the workspace will fail because Project Server cant find those OOTB fields that it knows are supposed to be there. It’s alright to create additional fields, the system will not have any problem picking those up as long as you reference them in your query. We have done this type of customization for many clients to give them a consolidated Issues and Risks dash board for the entire enterprise.
    I hope this helps.

  • John Budzynski

    What about changing the column name does it have the same effect as deleting the column? Or would an change to the field( name, choices, etc) cause the same problem? So are you better off to just hide any field that is not exactly the way you want and create a new one/

  • Tim Runcie

    Yes, it would impact a sync, however you can change the Link name (the display) without changing the actual field name.

  • N03L

    Thanks Dave.
    I found your blog post because I’m searching for guidance on how to add some other lists, that I’ve incorporated into our Project Workspace template, to the Project Reporting database in the same way that the Risks and Issues lists are added.
    Can you point to any MSFT or other resources that detail the process for expanding on this great functionality?

  • Chetan Patel

    There is no out of box feature available to bring SharePoint content into Reporting database. Either write custom code or purchase Data Miner

Leave a Comment

Advisicon is a Project, Program & Portfolio Management Company. We transform your organization's project management with a mix of methodology and technology that delivers results. Our team specializes in technology implementations, application and workflow development, training and consulting.
5411 NE 107th Ave, Suite 200
Vancouver
WA
98662
United States