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.
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.
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.
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.
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.
Overview of Microsoft Project Server 2010 for IT Professionals
Tuesday, December 15, 2009 8:00 AM Pacific Time (US & Canada)
In this webcast, we provide an overview of Microsoft Project Server 2010 features, requirements, and deployment considerations that IT professionals need to know about the product. Topics we discuss include: system requirements, deployment scenarios, installation procedures, upgrade options, and administration and operations enhancements that help IT professionals.
Christophe Fiessinger, Senior Technical Product Manager, Microsoft Corporation
Project 2010 Overview
Wednesday, December 16, 2009 11:00 AM Pacific Time (US & Canada)
Keshav Puttaswamy, Group Program Manager, Microsoft, will discuss and demonstrate core capabilities and features of the upcoming release – Microsoft Project 2010. The webcast will cover the key bets of unifying project & portfolio management, improving execution with effective collaboration, enhancing user experience & appeal, and simplifying deployment & interoperability.
Keshav Puttaswamy, Group Program Manager, Microsoft Corporation
Project Server Security in SQL Server Reporting Services
Wednesday, December 16, 2009 1:00 PM Pacific Time (US & Canada)
In this webcast, we discuss a method of taking advantage of Microsoft Office Project Server security in Microsoft SQL Server Reporting Services reports. We also cover a scenario where a customer has requested a SQL Server Reporting Services report that displays sensitive financial data. The customer only wants executors of the report to see information on projects to which they have access. Join us to learn more.
Stephen C. Sanderlin, System and Software Architect, MSProjectExperts
Project 2010 and Project Server 2010 Programmability
Thursday, December 17, 2009 8:30 AM Pacific Time (US & Canada)
In this webcast, we provide an overview of the programmability enhancements that are in the upcoming versions of Microsoft Office Project 2010 and Microsoft Office Project Server 2010. We highlight Windows Communication Foundation, Ribbon programmability, and the new programmability features such as Workflow. We also discuss writing backwards compatibility for Microsoft Office Project 2007 applications
Chris Boyd, Program Manager II, Microsoft Corporation
All will be recorded and available afterward as podcasts in case you miss them.
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.
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.
This is the second of a four part series on common workflow administration tasks associated with Project Server 2010.
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.
This is the third of a four part series on common workflow administration tasks associated with Project Server 2010.
This is the final installment of a four part series on common workflow administration tasks associated with Project Server 2010.
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.
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
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.
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.
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.
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.
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.
The following properties should be set on your Project Server databases.
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.
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.
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
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.