One of the little quirks that Project Managers must deal with in Project Server is the way the system handles assignments for Generic Resources. Project Server 2007 introduces the construct of "Assignment Owner," which is an attribute of an assignment and determines who in the system is responsible for updating task assignment. In other words, who will report progress on the task. For regular work resources, the system sets the Default Assignment Owner, an attribute of an Enterprise Resource, to the individual resource, assuming that by default each resource will update their own assignments. For Generic Resources, the system does not set a Default Assignment Owner value and it does not allow a user to set that value. In fact, the system always sets the Default Assignment Owner value to Null when you add or update a Generic Resource following the logic that a Generic Resource is a placeholder for an actual Work Resource, and therefore cannot logon to the system to update assignments. The system also assumes that the Project Manager is responsible for updating the assignments until they assign an actual resource. The Project Manager always becomes the assignment owner for a task assigned to a resource without a Default Assignment Owner value. While this seems logical, it means that if a Project Manager using Microsoft Project and Project Server 2007 publishes a thousand committed Generic Resource assignments, that Project Manager is going to see 1000 assignments on their My Tasks page. Depending on the bandwidth between the user and their Project Server, this can completely cripple the ability to load the My Tasks page, and even with excellent connectivity, seriously impacts page load time. I occasionally see scenarios in the field where individual planners are working with thousands of generic resource assignments where the standard behavior of the system presents a major challenge.
In examining possible solutions to the problem, you might consider setting a Task's Publish field to "No" so that the system does not publish assignments associated with the task. While this does prevent the assignments associated with the task from appearing in Project Web Access, including the My Tasks page, it has other consequences that may be undesirable to the planning process and to reporting and analytical data in the system. For example one problem associated with the no-publish strategy is that the Publish field is a task-level setting so this does not work for tasks that have multiple resource assignments containing a mix of generic and actual resources as one might find in a transitional schedule. For reporting and analytics, it also prevents the assignments from showing up in the reporting database and OLAP analytics, which do not carry unpublished data. Most Project and Portfolio Managers want to analyze the demand data for these generic placeholders, as it is one of the main reasons for creating and assigning generic resources in the first place. While this strategy may work for those who do not care about this vital information surfacing in analytical data, it is unlikely that this will work for most users.
A much better solution, that is more likely to work for many users, is to take advantage of Booking Types in Project Server 2007, a carry forward from the 2002/2003 versions of Project Server. By setting the Booking Type value for a task to "Proposed" rather than "Committed." When you set the Booking Type to "Proposed" the system does not display the assignment on the My Tasks page for both regular work resources and generic resources. It also does not affect availability for the affected resources but the assignments do appear in the reporting database and in OLAP data dimensioned as proposed or committed. Once again, this solution falls short in satisfying everyone because Booking Type, is a resource attribute set at the project level, so it does not allow a mix of committed and proposed assignments in a single project, let alone a single task, excluding a reality that some Project Managers must model. Further, it falls apart if you want to represent Generic Resource assignments as committed, another reality that some Project Managers must represent in their schedule model.
A perfect solution to the problem is for the system to allow Generic Resources to have default assignment owners. The only workaround is then to force the value by running a query against the published database where the Enterprise Resource Pool is stored. Of course, doing so is a cardinal sin and not only is it not supported but Microsoft could theoretically deny you support for your system if you do this. With this disclaimer in mind and fully understood, I don't think what I am about to suggest presents any potential harm as you can reverse it very quickly and through numerous tests I am convinced it is completely benign. By setting a Default Assignment owner value for Generic Resources you can solve the committed-generic-resource-assignment problem on the My Tasks page. Fortunately, the system behaves exactly as expected after doing this.
The query that you need to run to set the default assignment owner for generic resources is very simple; it sets the RES_DEF_ASSN_OWNER field to the RES_UID field value in the MSP_RESOURCES table in the published database:
UPDATE MSP_RESOURCES
SET RES_DEF_ASSN_OWNER = RES_UID
WHERE (RES_TYPE = 20)
The RES_DEF_ASSN_OWNER field holds the default assignment owner unique ID which is stored as a GUID in the RES_UID field. When you run the query against the database, it updates the value of one into the other only where the RES_TYPE field contains a value of 20, which indicates that the resource is generic. Running this little query through SQL Query Analyzer gives you instant gratification but doesn't solve two problems: 1) Any a time you add a new generic resource to the system you must run the query again to update the new additions and 2) If any authorized administrator opens an existing Generic Resource for editing and then saves, the system overwrites the Default Assignment Owner field with a Null value. So, to overcome this you must setup some sort of automation to execute the query when you insert a new Generic Resource and when you update one. Two possible approaches come to mind: 1) Create a custom event handler that executes code that executes the query or 2) Create a database trigger on the MSP_RESOURCES table to run the query on Insert and Update. My colleague Stephen Sanderlin and I discussed the pros and cons of each approach at length. Steve, being the more standards-conscious dev purist would prefer that I recommend the former, while my pragmatism favors the simpler and much easier approach of creating the database trigger and that is the example that I present in this post. To represent Steve's concern fairly, I must admit that creating the database trigger is committing a "double sin" in that I am not only electing to have illicit intercourse with the MSP_RESOURCES table I am choosing not to do it in the prone position. By setting a trigger, I am changing the database configuration in addition to making forbidden value changes. Consider removing this trigger before submitting your database to Microsoft as part of a support case.
To create the trigger you need to add only a few lines to the query resulting in the following SQL:
CREATE TRIGGER TRIG_UPDATE_GENERIC_ASSN_OWNERS
ON MSP_RESOURCES
FOR INSERT , UPDATE
AS
UPDATE MSP_RESOURCES
SET RES_DEF_ASSN_OWNER = RES_UID
WHERE (RES_TYPE = 20)
The example above uses the CREATE TRIGGER statement on the first line to create a trigger named TRIG_UPDATE_GENERIC_ASSN_OWNER using a name long enough to explain its purpose. The second line specifies the table name and third line defines the scope, which includes the INSERT and UPDATE events. The query itself follows the AS statement on line four.
Using SQL Server 2008 Management Studio and the standard Trigger template, the completed trigger appears in the following figure Click the Execute button after you enter the trigger to create the trigger in the database..
