Christophe Fiessinger's Blog Updates on Microsoft's Enterprise Social Networking (ESN) and Project Portfolio Management (PPM) offerings
While delivering Project Server training in Beijing, a customer asked me to produce a report that displayed Milestones dates by Projects.
The assumption is that all projects are using the same project template and thus there is a specific naming convention for all Milestones.
The hard part was creating the T-SQL query from the Reporting database. You basically have to de-normalize the data and combine two UserViews (MSP_EpmTask_UserView and MSP_EpmProject_UserView)as shown below:
SELECT P.ProjectName AS Project, P.ProjectAuthorName AS Author, P.ProjectStartDate AS Start, P.ProjectFinishDate AS Finish, MAX(CASE WHEN TaskName = 'M1' THEN TaskFinishDate ELSE NULL END) AS M1, MAX(CASE WHEN TaskName = 'M2' THEN TaskFinishDate ELSE NULL END) AS M2, MAX(CASE WHEN TaskName = 'M3' THEN TaskFinishDate ELSE NULL END) AS M3 FROM MSP_EpmTask_UserView AS T INNER JOIN MSP_EpmProject_UserView AS P ON P.ProjectUID=T.ProjectUID WHERE T.TaskIsMilestone=1 GROUP BY P.ProjectName, P.ProjectAuthorName, P.ProjectStartDate, P.ProjectFinishDate
For instance for Proj4 it contains the following data:
I then created a new view in the Reporting database called MilestoneView, then went into Excel 2007 added a data connection my Reporting database and used the MilestoneView created earlier. Results looks like this (note I added some conditional formatting to display indicators):
Going further you could leverage Excel Services to render this report in your SharePoint Server farm.
[update November 19, 2008] the links below are obsolete, please refer to this link instead: Protecting and restoring the farm (Office Project Server 2007)
FYI two useful articles for any PS Administrator, were published on TechNet:
For more information about using the STSADM command-line tool with Office Project Server 2007, see Operations for the Stsadm command-line tool in Microsoft Office Project Server 2007.
Do you ever wonder how many projects are in a specific phase? Would you like to know the attributes of a specific resource? Are you looking when a specific milestone ends? Do you have users in your organization that are not part of the Project Server resource pool but still need to access the data? Being able to easily search valuable information is becoming a key feature in any organization.
This solution starter demonstrates how to leverage SharePoint Server’s Business Data Catalog (BDC) to index project server data and enable users to search the different objects (projects, tasks, resources, lookup tables…) using SharePoint Server’s Enterprise Search, and give you answers to all the questions above. Out of the box the Project Server data is not indexed (only the SharePoint objects are indexed for instance Issues, Risks, Documents…) and thus users cannot search the content contained in the Reporting database. This solution starter will thus enable you to mine the PS 2007 gold mine and find the nuggets you were looking for very easily!
http://www.codeplex.com/EPMBDC
This solution starter was created for a presentation I delivered last week at the SharePoint Conference to illustrate one of the benefits of deploying PS 2007 with SharePoint Server ( and not just WSS V3).
In case you have not heard, we’ll be launching a new version of SQL Server in 2008 (in the second half of this year)!
Why consider SQL Server 2008 with Project Server or Portfolio Server 2007?
Joel Oleson wrote the following post recently on that subject: Top 10 SQL 2008 Features for SharePoint IT Pros
The new version of Reporting Services will be a major benefit for PS and PPS, it will enable much nicer reports and ease of use.
They are multiple resources to learn more about this upcoming release:
Last but not least, SQL 2008 will be supported once it’s released not before.
Finally I like the robot of the “The Server Unleashed” campaign, under Toy Box there some cool Walpaper/Screen Savers/Buddy Icons (click on the image for the SilverLight link):
Lately I’ve heard a few requests to improve the Project Server queue performance. For instance some of the request I’ve heard are: improve the publishing time from Project Professional, improve the timesheet processing time, etc…
Before going further I highly recommend reading the following article: Microsoft Office Project Server 2007 Queuing System for an in depth understanding on how the queue works.
Assuming the customer has the latest Project Server Service Pack/hotfix, what else can be done to improve performance?
A first option is described in Brian Smith’s blog: Is your Project Server 2007 queue running a few minutes slow?, basically make sure all servers in the farm have the same time.
Another option which requires more work is to identify was is slowing the queue down and act accordingly. For instance start monitoring performance counters (for a list check this: EPM & SharePoint Performance Counters) for each server in your farm and focus on counters when the queue is “slow”, basically try to identify the bottleneck.
Typical bottleneck I’ve seen in the past:
You could also run a SQL Profiler to see what’s happening from a database point of view (check this on how to setup a trace: Running SQL Server Profiler with Project Server).
Another great tool to watch the queue is this from CodePlex: EPM 2007 Queue Watch tool
In the end improving the queue performance could be quick or long to achieve depending on how much you know your farm (performance counters, customization…) and how optimized it is.
Two very useful SharePoint Server articles have been published on TechNet that are directly applicable to a Project Server farm. As I mentioned yesterday a properly configured SQL server will help Improving Queue Performance.
The disks and arrays you choose — and how you place data on those disks and arrays — can significantly affect system performance.
Sections include:
The topology of your system’s database tier, and your network, physical storage, and caching can significantly affect system performance. When you plan your hardware, remember that Office SharePoint Server 2007 is the last version of Office SharePoint Server that will run on 32-bit operating systems and databases.
I was recently visiting a large customer who had multiple Web Front End/Application servers (combined PWA and PSI) and each server had 12GB of memory. They were using Windows Server 2003 32-bit version.
In a 32-bit environment only a fraction of the RAM was being utilized and it's important to remember that the /3GB is not supported either: The Windows Server 2003 /3GB switch is not supported in Windows SharePoint Services 2.0 or in later versions or in SharePoint Portal Server 2003 SP2 or in later versions http://support.microsoft.com/kb/933560
As stated in the SharePoint documentation: if you are running 32-bit front-end Web servers, we recommend that you do not use the /3gb switch in Windows Server® 2003 to change the 2 GB of virtual address space to 3 GB for all user mode processes. We recommend against using the /3gb switch because most SharePoint site traffic involves sending large amounts of data through the operating system, and leaving only 1 GB of address space for the operating system can destabilize the computer.
So in the end the only option is to re-install the servers on a 64-bit operating system like Windows Server 2008 for instance :)
I encourage you to check the following article on MSDN: Memory Limits for Windows Releases
FYI just release on our Knowledge Base: 949982
Also note the following article: How to obtain the latest service pack for the 2007 Office server products
If if you want to stay up to date with KB articles I recommend a subscription to the following RSS feeds:
http://blogs.msdn.com/chrisfie/archive/2007/05/23/project-server-2007-help-support-site.aspx
FYI in case you missed it: http://support.microsoft.com/kb/948680/en-us
非常感谢您能前来参加本周在北京举办的Project Server 2007技术培训。能够见到大家我感到很荣幸。希望你们能够继续从事Project Server 2007的部署实施工作，千万不要忘了我们在课堂上讲述的那些最佳实践，关键时刻，也许它们能帮上大忙！
遇到问题的时候，你知道在哪里能找到答案，对啦，我的Blog!! 我可不是为了提高点击率啊。:)