December, 2008

Christophe Fiessinger's Blog
Updates on Microsoft's Enterprise Social Networking (ESN) and Project Portfolio Management (PPM) offerings

  • Christophe Fiessinger's Blog

    Agile Planning Tools In Visual Studio Team System 2010

    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:

  • Christophe Fiessinger's Blog

    Getting Started with Project Server 2007 - TechNet Magazine January 2009

    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:

    • Four general areas of enhancement
    • Multitier architecture aids scalability and flexibility
    • Prerequisites for installation
    • Installing and configuring


    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

  • Christophe Fiessinger's Blog

    How to report Project Risks at a Program Level?

    <Update June 2010> As a reminder as mentioned in this MSDN article: SharePoint Database Accessprocess 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'
    I then exported the XML result in notepad to figure out all the “Risk” field required.
    <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}"/>
    I then wrote the following query to retrieve all the Risk fields:
        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
        ON UserData.tp_ListId = Lists.tp_ID
        ON UserData.int1 = UserInfo.tp_ID
        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))
    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!

    Level A image
    Level AA image
    Level AB image

    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

  • Christophe Fiessinger's Blog

    Warm up your Project Server and SharePoint VPC in seconds!

    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:
    For information about how the script was developed, please see my blog:


    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.

  • Christophe Fiessinger's Blog

    Announcing the CodePlex release of ShUIE (SharePoint User Interface Extender)

    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:

    Check this live demo from Jan Tielens.
    I get the navigation on the root PWA site and all project workspaces.

    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)!

  • Christophe Fiessinger's Blog

    Announcing the release of the December Cumulative Update for Project and Project Server 2007

    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

    Title ID KB URL Download URL
    Description of the Project 2007 hotfix package (Project.msp): December 16, 2008


    Description of the Project Server 2007 hotfix package (Pjsrvapp.msp, Pjsrvwfe.msp): December 16, 2008


    Description of the Windows SharePoint Services 3.0 hotfix package (Sts.msp): December 16, 2008


    Description of the SharePoint Server 2007 hotfix package (Coreserver.msp): December 16, 2008


    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)

  • Christophe Fiessinger's Blog

    How to upgrade your existing Microsoft Project VPC running on SQL 2005 to SQL Server 2008

    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):

    Download SQL Server 2008 Developer (x86, x64, ia64) - DVD (English)using your MSDN Subscription.
    Start your favorite EPM image using your favorite virtualization production Hyper-V and mount the ISO downloaded.

    SQL Server 2008 Setup requires two updates prior to install:

    • .NET Framework 3.5 SP1
    • Windows Installer 4.5
    ScreenHunter_11 Dec. 16 09.18
    .NET Framework 3.5 SP1 Setup ScreenHunter_13 Dec. 16 09.22
    One pre-requisite is to have the latest version of Windows Installer 4.5 (KB942288-v4); install and reboot image
    Start Upgrade Wizard image
    Setup Support Rule are run successfully image
    Choose the Instance to Upgrade image
    All Upgrade Rules are passed successfully, start upgrade process (it took about 90min on Dell Precision 690) image
    Upgrade Progress finished successfully image

    Download and install the following components from the
    Feature Pack for Microsoft SQL Server 2005 - December 2008 (all X86 Package in this case):

    • Microsoft SQL Server Native Client (sqlncli.msi)
    • Microsoft SQL Server 2005 Management Objects Collection (SQLServer2005_XMO.msi)
    • Microsoft SQL Server 2005 Backward Compatibility Components (SQLServer2005_BC.msi)

    These components are required to properly configure the Cube Building process.

    Rebuilt your cube and voila!  

    Related Links

  • Christophe Fiessinger's Blog

    Announcing the release of SQL Server 2005 SP3

    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.

    Related links:


  • Christophe Fiessinger's Blog

    SharePoint Best Practices for capacity management

    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.

  • Christophe Fiessinger's Blog

    Microsoft Search – Portal & Book

    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

    Portal : Microsoft Enterprise Search image
    Book : Professional Microsoft Search: SharePoint 2007 and Search Server 2008 (Wrox Professional Guides) by Tom Rizzo, Richard Riley, and Shane Young.
    Table of Content:
    • Chapter 1: Introduction to Enterprise Search.
    • Chapter 2: Overview of Microsoft Enterprise Search Products.
    • Chapter 3: Planning and Deploying an Enterprise Search Solution.
    • Chapter 4: Configuring and Administering Search.
    • Chapter 5: Searching LOB Systems with the BDC.
    • Chapter 6: User Profiles and People Search.
    • Chapter 7: Extending Search with Federation.
    • Chapter 8: Securing Your Search Results.
    • Chapter 9: Customizing the Search Experience.
    • Chapter 10: Understanding and Tuning Relevance.
    • Chapter 11: Building Applications with the Search API and Web Services.
    Cover image for product 0470279338
    Team Blog : Microsoft Enterprise Search Blog  
Page 1 of 2 (15 items) 12