Christophe Fiessinger's Blog The latest news you need to know about Microsoft Project
Check out the following article Agile Planning Tools In Visual Studio Team System 2010 from Ajoy Krishnamoorthy in the latest version of MSDN Magazine; another interesting article in the December edition: Streamline Team Projects With Process Templates.
Related link:
Check out the following article from TechNet Magazine (January 2009 edition) from Alan Maddison from Strategic Business Systems: Getting Started with Project Server 2007 This article is targeted at IT Professionals:
At a Glance:
(…)
As IT administrators, most of us will have used Microsoft Office Project (Standard or Professional) to help streamline and organize at least some of our many projects. However, not many of us have implemented Project Server, due to a lack of familiarity, expertise, or simply the time to tackle one more complex installation and configuration. With Office Project Server 2007, Microsoft has made significant enhancements not only to the features and functionality available for users but also to those that interest administrators.
Last Words
Project Server 2007 is clearly the most significant release of this product in a long time. Microsoft has invested a lot of time and energy into making Project Server 2007 a marked improvement over earlier versions and if you have not taken a look at it recently, then it is time you did.
Related links
<Update June 2010> As a reminder as mentioned in this MSDN article: SharePoint Database Access “process for acquiring that data should be through the built-in SharePoint object model, and not by using any type of query to the database.” hence a much more elegant and supported solution to address this request is to use the object model (rewrite the T-SQL code below in managed C# using the SharePoint object model or if you are not a developer you could also leverage partner solutions such as this one to achieve the same result: How to report across multiple SharePoint lists? i-PMO SharePoint Data Miner to the rescue. Thanks to the SharePoint MVP community for the reminder on this un-supported scenario!</ Update>
A request I heard a few times from customers is how can you report Risks (or Issues) at a Program Level and since I’m snowed in it’s a perfect opportunity for a new EPM blog post! Since Risks and Issues are stored as lists within SharePoint another way of looking at this problem, is how can one create a list of lists from a SharePoint content database?
For instance lets assume I have the following project hierarchy in Project Web Access:
where Level A is a parent of Level AX and Level AX a parent of Level AXX. The levels could also represent Program/Project/Workpackage. At each level I have projects and for each projects I have a workspace associated with it (following the SharePoint site hierarchy shown above for ABA (add a ‘B’ and start singing!)):
Let’s assume I track Risks at each level and for each project shown above. How can you report all the risks at any level is the question? Remember that Issues and Risks are all tracked and stored in the SharePoint content database associated with the site collection. I hence wanted to build a report by querying the data directly in the SharePoint content database to ensure I have the latest and greatest data (remember that the SharePoint data (Issues/Risks/Deliverables) only flows in the reporting database once a project plan is opened and published). I could have leveraged SharePoint Designer to build a List of Lists by accessing the list webservice (sample here), but instead of using ASMX I figured it would a lot more efficient to query the database directly.
There were two challenges in writing that query: figuring out the Sharepoint “Risk” T-SQL query and building a recursive function to find all the children “Risks” of each parent. Please find below how I solved these two challenges:
SharePoint “Risk” T-SQL query
I used the following post from Rob Fisch to get started: Reporting on Sharepoint lists from Microsoft SQL Reporting Services. I fired up the query editor in SQL 2008 to leverage Intellisense (I love it!) and started with this first query in the SharePoint content
SELECT TOP 1 tp_Fields FROM Lists WHERE Lists.tp_Title = 'Risks'
<FieldRef Name="ContentTypeId"/><FieldRef Name="Title" ColName="nvarchar1"/> <FieldRef Name="_ModerationComments" ColName="ntext1"/><FieldRef Name="File_x0020_Type" ColName="nvarchar2"/> <FieldRef Name="Owner" ColName="int1" StaticName="Owner" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="AssignedTo" ColName="int2" StaticName="AssignedTo" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Status" ColName="nvarchar3" StaticName="Status" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Category" ColName="nvarchar4" StaticName="Category" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="DueDate" ColName="datetime1" StaticName="DueDate" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Probability" ColName="float1" StaticName="Probability" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Impact" ColName="float2" StaticName="Impact" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Exposure" ColName="sql_variant1" StaticName="Exposure" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Cost" ColName="float3" StaticName="Cost" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Cost_x0020_Exposure" ColName="sql_variant2" StaticName="Cost_x0020_Exposure" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Description" ColName="ntext2" StaticName="Description" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Mitigation_x0020_plan" ColName="ntext3" StaticName="Mitigation_x0020_plan" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Contingency_x0020_plan" ColName="ntext4" StaticName="Contingency_x0020_plan" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Trigger_x0020_Description" ColName="ntext5" StaticName="Trigger_x0020_Description" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Trigger" ColName="nvarchar5" StaticName="Trigger" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/> <FieldRef Name="Links" ColName="ntext6" StaticName="Links" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
SELECT ProjectStructure.Program, ProjectStructure.Project, UserData.nvarchar1 AS Title, UserInfo.tp_Title AS 'Owner', UI.tp_Title AS 'Assigned To', UserData.nvarchar3 AS 'Status', UserData.nvarchar4 AS Category, UserData.datetime1 As 'Due Date', UserData.float1 AS 'Probability', UserData.float2 AS 'Impact', UserData.sql_variant1 AS 'Exposure', UserData.float3 AS 'Cost', UserData.sql_variant2 AS 'Cost Exposure', UserData.ntext2 AS 'Description', UserData.ntext3 AS 'Mitigation Plan', UserData.ntext4 AS 'Contingency Plan', UserData.ntext5 AS 'Trigger Description', UserData.nvarchar5 AS 'Trigger', UserData.tp_Modified AS 'Modified', ProjectStructure.FullUrl AS 'URL', UserData.tp_DirName + '/DispForm.aspx?ID=' + CONVERT(varchar(5),UserData.tp_ID) AS [Risk URL] FROM UserData INNER JOIN LISTS ON UserData.tp_ListId = Lists.tp_ID LEFT OUTER JOIN UserInfo ON UserData.int1 = UserInfo.tp_ID LEFT OUTER JOIN UserInfo AS UI ON UserData.int2 = UI.tp_ID INNER JOIN ProjectStructure(@ProgramName) ON ProjectStructure.Id = LISTS.tp_WebId WHERE tp_ContentType='Project Workspace Risk' AND UserData.nvarchar3='(1) Active' ORDER BY UserData.sql_variant1 DESC
Recursive Function to Find all Children that belong to a level
I used this blog post as a starting point: Creating Recursive SQL Calls for Tables with Parent-Child Relationships and created a function in the SharePoint content database. Notice how ProjectStructure does an inner join on itself hence the recursion:
ALTER FUNCTION [dbo].[ProjectStructure](@ProgramTitle nvarchar(255)) RETURNS TABLE AS RETURN ( WITH ProjectStructure (Id, [Program], [Project], FullUrl, [Level]) AS ( SELECT Id, Title AS [Program], Title AS [Project], FullUrl, 0 FROM Webs WHERE Title = @ProgramTitle UNION ALL SELECT Project.Id, ProjectStructure.[Program], Project.Title, Project.FullUrl, 1 FROM Webs AS Project INNER JOIN ProjectStructure ON ProjectStructure.Id = Project.ParentWebId ) SELECT * FROM ProjectStructure )
I then created a report in SQL Reporting Services 2008 (check these cool SSRS08 reports I did recently!) and voila!
You can also run the query at the root (PWA level) and you’ll get all the Risks currently active in your Project Server instance. You could write a similar report for Issues. Last but not least use this reporting sample as a starting point for your reporting needs and don’t forget to test, test, test prior to any production deployment!
Happy reporting!
Related links SharePoint Database Access Reporting on Sharepoint lists from Microsoft SQL Reporting Services Creating Recursive SQL Calls for Tables with Parent-Child Relationships
Yet another cool tool from CodePlex: SPWakeUp - Wake up your Sharepoint and WSS Sites written by Andrew Kennel.
A great tool to warm up and optimize your demo performance as well as your favorite SharePoint farm.
A simple application that touches each site and site collection on a Sharepoint or WSS server to rebuild the IIS cache.This project was inspired by the Warm Up script I found at: http://blogs.msdn.com/joelo/archive/2006/08/13/697044.aspx For information about how the script was developed, please see my blog: http://akennel.blogspot.com
The SharePoint CodePlex community has been very active this past few months, click here (567 projects as of December 18 2008) for a lot more cool SharePoint projects that you could potentially leverage in your Project Server deployments.
David Kitchen, Louise Brigthon and Calvin So from PCubed have recently released the following CodePlex project: ShUIE (SharePoint User Interface Extender):
ShUIE is an addition to Microsoft SharePoint that allows a developer to inject JavaScript and CSS fragments depending on the context of the page being displayed. JavaScript and CSS injected can be optionally minified, and jQuery is included to increase functionality.
An easy way to ‘pimp’ (to decorate) your Project Web Access User Interface, for instance (after spending 5 minutes to set it up and storing ShUIE configuration in my favorite the Reporting Database of my favorite PWA instance) I got this using jqDock sample:
URLS were broken but the concept sounds interesting.
Another fun project worth investigating for the end-of-year holidays (consider the security and scalability for instance of ShUIE)!
The December Cumulative Update was released on December 16th. The next Cumulative Update (CU) is scheduled for the end of February, then Service Pack 2 will be available between February and April and it will include the February CU.
From this point forward each Cumulative Update will consist of a package that contains the latest of every hotfix patch that we have shipped. What this means is that if you build a new Project Server 2007 farm, you can apply the latest service pack (Service Pack 1), the latest WSS CU package and the latest SharePoint CU package and be completely up-to-date. The package includes the Infrastructure Update, there is no reason to install it separately (but you still need to install SP1).
Please refer to this TechNet article for deployment guidance: Deploy cumulative updates (Project Server 2007)
After applying the December CU, the file version should be: 12.0.6334.5000
Knowledge Base Articles and Downloads
959643
http://support.microsoft.com/kb/959643
http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=959643
960313
http://support.microsoft.com/kb/960313
http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=960313
959644
http://support.microsoft.com/kb/959644
http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=959644
959637
http://support.microsoft.com/kb/959637
http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=959637
Related links:
· Announcing the release of the October Cumulative Update for Project and Project Server 2007
· Announcing the release of the August Cumulative Update for Project and Project Server 2007
· Upcoming Microsoft Project 2007 and Project Server 2007 Service Pack 2
· Cumulative updates are available from the Microsoft Office team to deliver hotfixes for reported problems
· Project Server TechCenter (TechNet)
I’m sure you have seen these cool SQL Reporting Services 2008 reports: Microsoft SQL Server Reporting Services 2008 – Sample Project Server Reports and would like to do the same in your favorite EPM demo VPC or you just have plenty of free time during this time of the year and you are looking for a fun project, hence please find below the steps to upgrade an existing image running on SQL 2005 to SQL 2007 (should take about two hours depending on hardware):
SQL Server 2008 Setup requires two updates prior to install:
Download and install the following components from the Feature Pack for Microsoft SQL Server 2005 - December 2008 (all X86 Package in this case):
These components are required to properly configure the Cube Building process.
Related Links
Microsoft released today SQL Server 2005 Service Pack 3 (SP3). SQL 2005 version should now be 9.00.4035.
Microsoft SQL Server 2005 Service Pack 3 (SP3) contains hotfixes that were included in cumulative update packages for SQL Server 2005 Service Pack 2 from cumulative update package 1 to cumulative update package 9, and fixes to issues that have been reported through our customer feedback platforms. It also includes supportability enhancements and issues that have been reported through Windows Error Reporting.
Downloads:
The following article Best Practices for capacity management was recently published on the SharePoint Server TechCenter.
Some highlights that are also applicable to the Project Server world are:
Capacity planning should be revisited repeatedly in all phases of a project as assumptions become more concrete and more detailed hardware and configuration decisions have to be made. Plan accordingly.
Be familiar with the tested Office SharePoint Server scenarios
Use 64-bit server versions wherever possible to enable scale-up scenarios and improve performance. Future versions of Office SharePoint Server will only support 64-bit hardware and operating systems (<- this is also true for Project Server)
Plan the monitoring rules that you will use to trigger the appropriate scale-out and scale-up options.
Be sure to stress test customized platforms before they go into production.
Search, search, search! Please find some useful pointers on the subject, including a great book I read recently that just got published last month that explains very well our search strategy and offerings; in case you missed it Microsoft positioned in the Leaders Quadrant of the 2008 Information Access Magic Quadrant. Finally if you want to search Project Server data check this: Search Project Server data using MOSS' BDC and Enterprise Search - CodePlex Solution Starter