This is the final installment of a four part series on common workflow administration tasks associated with Project Server 2010. 

Installation and setup of Project Server 2010 is covered in the overall setup guide, and these articles will make the assumption that the user has already read and completed the setup of Project Server 2010.

These articles also will not cover the topic of creating Project Server 2010 workflows. Please refer to our SDK articles to find out more information on how to create our workflows.

How to Restart/Change Workflows

Restarting a workflow may become necessary for any number of reasons. By restarting a workflow, you will cause the workflow engine to execute the workflow from the very beginning. No project related data will be lost or reset. This action simply tells the workflow to “Go to Stage 1” and execute everything again. Similarly, changing a project to another Enterprise Project Type that has a workflow, will cause the project to execute the new workflow from the very beginning.

How to restart/skip to stage within a workflow

1. Within Project Web Access go to Server Settings
   • 
2. Under “Workflow and Project Detail Pages” Click on “Change or Restart Workflows”
3. Select the Enterprise Project Type that the project in question belongs to.
   • Note: A project with a damaged workflow instance might be under the “None” category.
4. 4) Under Choose Projects, select the projects you wish to restart/Skip to stage and press the “>” button.
   • 
5. Under: “Choose new Enterprise Project Type or restart current workflow:”
   • To restart the workflow select “Restart current workflow for the selected projects”
   • To change the Enterprise Project Type the selected projects are currently associated with, select “Associate projects with a new Enterprise Project Type:”
6. Under: “Choose Workflow Stage to Skip to” if you are restarting a workflow or moving to an Enterprise Project Type which has a workflow associated with it, the below options will be enabled
   • “Skip until the current workflow stage” will cause the workflow to execute from the beginning and attempt to continue to execute until it reaches the last stage that it was at before it was restarted
   • “Skip to a particular workflow stage:” will allow you to select a stage you would like the workflow to attempt to execute to.
     • The workflow will begin executing from the very beginning and if it finds the stage which you selected it will stop.
     • The drop down does not filter the stages to just the stages within the target Enterprise Project Type. As such, if you select a stage which does not exist within the workflow, the workflow will simply continue to execute until it reaches a natural stop point.

Skip to Stage Support

The skip to stage functionality is something that will only work if the workflow is correctly designed to allow for stage skipping. All project server workflows will always stop whenever natural stop points are reached. These include

1. Stages where required fields are not filled out
2. Approval points
3. “Wait” activities (such as “Wait for Submit” or “Wait for Commit” activities)
4. And others.

As such, to get the skip to stage functionality working fully, you will need to incorporate “if” branches that will bypass “stop points” like approval points and portfolio selection points when developing the workflows.

This is the third of a four part series on common workflow administration tasks associated with Project Server 2010. 

Installation and setup of Project Server 2010 is covered in the overall setup guide, and these articles will make the assumption that the user has already read and completed the setup of Project Server 2010.

These articles also will not cover the topic of creating Project Server 2010 workflows. Please refer to our SDK articles to find out more information on how to create our workflows.

How to Troubleshoot Your Workflows

Check the workflow status page

1. There are two different ways to do this based on your need
   1. Check from within the project
      • Open a project with a failing workflow
      • Go to the Stage Status Page (This is the very first page from within a workflow stage)
      • From within the workflow Status page expand the “All Workflow Stages” section
        • 
      • Click on the “Additional Workflow Data” link, which is round at the bottom right.
        • 
   2. If your project cannot be opened, you can also get to this page by:
      • Site Actions, View All Site Content
        • 
      • Click on “Site Workflows”
        • 
      • Click on “Show All Workflows” link
      • Find the workflow which you are concerned about.
      • Once you have opened the workflow status page you can investigate the workflow history to see what the workflow was doing before it began to error.
        • 

View the ULS logs

1. ULS logs can be found in: “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS” 
   • Suggestion:  Create a desktop shortcut on the server to this location.
2. Logs will be broken up into pieces. Find the log with a time stamp as close to the time you are most concerned about, and open it.
3. Some key words to look for when going through the logs are:
   • Sharepoint foundation
   • Startworkflow
   • Winwf
   • entering...activity
   • leaving... activity

This is the second of a four part series on common workflow administration tasks associated with Project Server 2010. 

Installation and setup of Project Server 2010 is covered in the overall setup guide, and these articles will make the assumption that the user has already read and completed the setup of Project Server 2010.

These articles also will not cover the topic of creating Project Server 2010 workflows. Please refer to our SDK articles to find out more information on how to create our workflows.

How to Setup the Workflow Proxy Account

Project Server Workflows need to run under the context of a user.  However, they do not run under context of the user that started the project, instead, the workflows are run under the “Workflow Proxy Account”. This means that the user account which you specify as the workflow proxy account must have the proper rights to execute all of the commands a project server workflow will need to do.

It is recommended that you setup a service user to serve as this function.  The steps below show how to define and setup a workflow proxy account.

1. Within Project Web Access, go to Server Settings
   • 
2. Under “Workflow and Project Detail Pages” Click on “Project Workflow Settings”
3. Workflow Proxy User: type in the user name of the account you wish to have all workflows run under.
   • 
4. The minimum rights needed for the Workflow Proxy User account Project Server security are:
   • Global permissions:
     • Log On
     • Manage Users And Groups
     • Manage Workflow
   • Category permissions:
     • Open Project
     • Save Project
     • View Enterprise Resource Data
     • Edit Project Properties
     • View Enterprise Resource Data

This is the first of a four part series on common workflow administration tasks associated with Project Server 2010. 

Installation and setup of Project Server 2010 is covered in the overall setup guide, and these articles will make the assumption that the user has already read and completed the setup of Project Server 2010.

These articles also will not cover the topic of creating Project Server 2010 workflows. Please refer to our SDK articles to find out more information on how to create our workflows.

How to associate a workflow with an Enterprise Project Type(EPT)

Once a workflow has been created within Visual Studio and activated on the server farm (covered in SDK articles) the administrator will need to correctly associate the workflow with an EPT.

1. If you have already created a Workflow Association skip to step 11
2. Once the workflow has been activated on the server, the administrator will need to go to Site Actions, Site Settings
3. 
4. Select Site Administration, Workflow Settings
5. 
6. Click on Add a workflow
7. Fill in the correct information for your workflow
• Workflow: Select the workflow you installed on the farm
• Name: Type in a name for the workflow association
• Task List: Select the “Project Server Workflow Tasks” item from the drop down
• This list is where all of the approvals (if you have any) will go, so long as you coded your workflow to follow the same logic as the Out of the box Project Server Workflow approval process.
• History List: Select “Project Server Workflow History” item from the drop down
• This is the history list for the workflows
• Start Options: Leave it the “Allow this workflow to be manually started by an authenticated user with Edit Item permissions” option checked
8. Assuming you have no workflow association page, press the OK button.
9. You should now see your new workflow association
10. 
11. Within Project Web Access go to Server Settings
12. 
13. Under “Workflow and Project Detail Pages” Click on “Enterprise Project Types”
14. Either select on an existing Enterprise Project Type or press the “New Enterprise Project Type” button
15. Fill in the required information, and under Site Workflow Association select the new workflow association you created
16. 
17. Click Save and you’re done!
• All new instances of this Project Type will use this workflow association.
• If you modified an existing Enterprise Project Type which already had a different Workflow Association, any existing instances of this Project Type will continue to use the old workflow association. Only new workflows will use the new association that you just indicated.
• If you require all workflows to use this new workflow association, you will need to restart the existing workflows (covered below)

All will be recorded and available afterward as podcasts in case you miss them.

We will be supporting migration from Microsoft Project Server 2003 SP3 to Microsoft Project Server 2010 via a Virtual Migration Environment (more details to come.).  If you have not already updated your 2003 server to SP3, now might be a good time to plan the application of that service pack.

The Project Server 2010 Business Intelligence feature utilizes the support of Excel Services, Secure Store Service, PerformancePoint Services and SQL Server.  A thorough understanding of how these features fit together is necessary to get the most from the feature.  This post will provide that overview and provide troubleshooting questions for common issues.

Architecture

Our Business Intelligence features leverage Excel Services as the base functionality since most people use Excel to visualize data and it’s a tool that many people already know how to use. 

There are four core components to this solution.

Excel client. The Excel Client is used to author and publish new reports.  This solution will work with Excel 2007 SP2 or later.

Office Data Connections.  Office Data Connections(ODC) are used to store the connection information, the SQL Query and the Secure Store Target Application ID.  External ODCs are used to allow you to manage data connection and query information externally to the reports that consume the data.  These two components together are the deliverables from the report author.

When you provision a new Project Web Application site or when you create a new OLAP database, ODCs and attached templates will be automatically generated in the Business Intelligence Center.

Excel Services.  Excel Services provides rendering and interactivity support on the web.  This service enables the user to share reports easily with others.  It also enables a user to filter the data in a report dynamically to meet a particular need.

Secure Store.  Secure Store is a SharePoint service used to store credentials in a Target Application Profile.  These profiles help avoid double hop authentication situations and provide control around who has access to what data for a given Target Application Profile.  In SharePoint Server 2007, this service was known as Single Sign-On service or SSO.

The diagram above illustrates the interactions between the four components.  The arrows denote what information is passed between the components and in what direction.

Lastly, PerformancePoint is called out above as it is used to create the Business Intelligence Center as it is their service that provides this infrastructure.  It isn’t used for the core reporting features.  However, you can easily develop PerformancePoint reports over Project Server data. 

Setup

The setup steps for the Business Intelligence features can be found here.  http://technet.microsoft.com/en-us/library/ee662106(office.14).aspx

Please note, if you are using Active Directory(AD), you can set up a AD Group for Report Authors such that you only have to create one SQL Login for the group.  The membership of the group is then maintained outside of SQL Server.

Also, you must set up a SQL Login with db_datareader rights for the credentials used in the Secure Store Target Application ID.  If you have created the AD group for the authors above and it matches the security needed to service reports, you can simply add the Target Application ID credentials to the Report Author’s AD group instead of creating a new SQL Login.

Frequently Asked Questions

Why isn’t my workbook rendering on the web?

This can be due to a number of reasons.  Here is a list of items to verify.  The steps to do each of these items are listed in the setup link above.

• One Time Setup Items
  • Has the Excel Services Service Application and Proxy Connection been created?
  • Is Secure Store Service Application and Proxy Connection been created?
  • Is Excel Services Service Application configured?
  • Is the folder for the data connections in a trusted location within the Excel Service Application?
  • Is the folder for the reports in a trusted location within the Excel Service Application?
  • Is the Secure Store Service Service Application configured?
• For each Target Application Profile
  • Has the Secure Store Target Application Profile for the reports configured?
  • Is the user authorized or belongs to a group that is authorized to use the particular SSS Target Application profile?
  • Does the SSS Target Application have credentials set?
  • Does the SSS Target Application credentials have DB_DataReader rights to the Reporting Database?
• For each workbook
  • Is the SSS ID value in the Office Data Connection filled in?
  • Does the SSS ID in the Excel workbook match the ID of the Application Profile in SSS? 

Why hasn’t my data appeared yet?

• If the data is sourced from the Reporting database, it could be that the Reporting Publish job has not yet completed.  Since these jobs are queued, you need to ensure the jobs has completed before you will see the data.
• If the data is sourced from an OLAP database, you won’t see the data until the OLAP database is refreshed.
• If these two items aren’t the case, there may be an issue with the query itself.  

We’ve added some performance configuration suggestions for SQL in this release, based on feedback from early test customers and from the results of our own performance testing.  These settings are intended to help the overall performance of the system. 

Database Properties

The following properties should be set on your Project Server databases.

AUTO_CLOSE set to False

Auto close is typically set to False by default when Project Server 2010 creates the databases for a farm setup.  If set to True, this property tells SQL Server to automatically close the database when the last user has ended their connection and all other processes have completed.  This makes sense in single user scenarios when you are using the desktop version of SQL Server with limited resources.  However, on a multiple user system, this creates unnecessary overhead. 

This property can be set accidentally, if you prototyped an environment using a Standalone install and then moved the databases to a production farm.

AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNCHRONOUSLY set to True

Slow queries are annoying to everyone.  One of the most common factors we’ve found that cause this slowness to occur is the database statistics being out of date.  The two settings above are recommended so that your statistics are kept up to date AND that query processing doesn’t wait for the statistics refresh to complete.  Previously, if you set AUTO_UPDATE_Statistics to True, if SQL found stale statistics, it would halt a query and make it wait until the statistics were updated.  Depending on the size of the database and the query, this can lead to a substantial wait time, leading the user to think the system is hung. 

In SQL Server 2005, the AUTO_UPDATE_STATISTICS_ASYNCHRONOUSLY property was added.  This enabled SQL to automatically refresh the statistics in the background while allowing queries to continue execution.  This leads to a better overall user experience since the operation may be a bit slower initially but it still completes.  Note, Project Server 2010 does not set this property by default.  Also, note, if you are migrating Project Server 2007 databases, you should update these properties post Upgrade.  For more information, go to this link:  http://msdn.microsoft.com/en-us/library/ms190397.aspx

If you upgrading databases from SQL Server 2000 to SQL Server 2005/2008, the sampling algorithm changed in the 2005 release.  It is recommended that you run sp_updatestats with the RESAMPLE option to update the statistics, using the new algorithm.

Enable the SQL Common Language Runtime (CLR)

Custom field performance will see the most benefit from this setting.  As the number of custom field values grow, the query performance will decline as the number of records to query grows. 

Note, I said custom field values, not number of custom fields.  For example, if you have a task level custom field with a large lookup table and assignment roll down enabled, this one field will create a lot of potential data to query.  The new departments feature of Project Server 2010 may also lead to more custom fields and custom field values on the server. 

Enabling the CLR on the SQL Server allows us to execute queries in a more efficient manner by reducing stress on the application server, reducing SQL roundtrips and performing queries closer to the data.  The resulting Custom Field performance gains are significant.  For more information on how to enable the CLR, go to this link.  http://technet.microsoft.com/en-us/library/ee662108(office.14).aspx#section3

Need More Information?

I recommend starting with the SharePoint Guidance for SQL Server Database Administrators.  The document is targeted to SharePoint Server 2007 but the concepts are valid for SharePoint Server 2010 as well.  This document can be found here:  http://technet.microsoft.com/en-us/library/ee721075.aspx

Another article on Project Server 2007 Performance and Capacity Planning best practices white paper would also be another great read.  This document can be found at http://technet.microsoft.com/en-us/library/dd823304.aspx

The initial documentation for planning a SharePoint Server 2010 Server farm may also be of interest to you.  The documentation can be found here:  http://technet.microsoft.com/en-us/library/cc789337(office.14).aspx

Lastly, there are three videos from the Project Conference which may be of interest to you.  Each cover aspects of SQL Server and Performance best practices.

Chalk Talk: Project Server 2007 Performance Tuning (Also covers SQL Best Practices)

http://www.microsoft.com/video/en/us/details/73213feb-9a93-43b6-94c2-65f64127bbe7

Server 2007 Performance Testing and Capacity Planning

http://www.microsoft.com/video/en/us/details/15142262-d421-47f1-8ce7-ea85476cde15

SharePoint (or Project) Admins: The Reluctant DBAs

http://www.microsoft.com/video/en/us/details/65b75005-8a93-459d-bd4a-65c1246501e7

The Microsoft Project Server 2010 Public Beta will expire on October 31, 2010.  Some people have asked and I wanted to make this date clear to all.

Please note:  Project Server 2010 Public Beta is not supported for Production use. 

Also note: upgrade from Microsoft Project Server 2010 Public Beta to Microsoft Project Server 2010 Released Version is explicitly blocked and not supported.  This restriction applies to both In-Place or DB Attach upgrade methods.   

In the previous post, it was stated that Windows Server 2008 R2 support was coming.  I’m happy to announce it is here, if you apply the following hotfix.  http://support.microsoft.com/kb/976462  Once you apply it, you don’t have to reboot the server.  However, we would suggest doing an IISReset to ensure everything is reloaded with the patched bits.