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

  • Christophe Fiessinger's Blog

    Project Server 2010 Delegation Audit Event Handler

    On of the great new feature for Project Server 2010 is the ability to delegate Project Web App (PWA) to a colleague while you are out of the office, check the Project 2010 Ignite Administration slides and recordings for more information (Download Project 2010 Ignite Recordings and Slides). One of the many feedback I heard during the Ignite worldwide tour was to make it easier to track who became a delegate of who and when. Finally found some time to provide an example to address this request. I basically built a custom event handler that overrides these two UserDelegation events: OnActivated  and OnDeactivated (which are called when you click on either Start Delegate Session and Stop Delegate Session in PWA:

    DelegationBy definition this information is stored in the ULS logs, so to make it easier to report, every time the above two events are fired I store in a custom table the delegate session information. After 2 hours of coding and testing voila! My very first Project Server 2010 custom event handler, so easy to do even a marketing guy can do it! Once the custom handler is running you can then leverage Excel and Excel Services to built a report that renders an audit of all Delegate activity in your PWA instance (as shown below). I just love the ease of extensibility of 2010…

    Code download is location on MSDN Code Gallery: Project Server 2010 Delegation Audit Event Handler

    Partial extract of C# code

    // Track Delegation Activation
    public override voidOnActivated(PSContextInfo contextInfo, UserDelegationActivatePostEventArgs e)
    {
        // Standard processing
      
    base.OnActivated(contextInfo, e);

        // Build T-SQL command
      
    stringcmdText = string.Format("EXEC PS2010_InsertDelegationActivationChange '{0}','{1}',1", e.Delegation.DelegateUid.ToString(), e.Delegation.ResUid.ToString());

        // Store Resource field update
      
    ConnectAndExecuteNonQuery(connectionString, cmdText);
    }

    // Track Delegation Deactivation
    public override voidOnDeactivated(PSContextInfo contextInfo, UserDelegationDeactivatePostEventArgs e)
    {
        // Standard processing
      
    base.OnDeactivated(contextInfo, e);

        // Build T-SQL command
      
    stringcmdText = string.Format("EXEC PS2010_InsertDelegationActivationChange '{0}','{1}',0", e.Delegation.ResUid.ToString(), e.Delegation.DelegateUid.ToString());

        // Store Resource field update
      
    ConnectAndExecuteNonQuery(connectionString, cmdText);
    }

    Partial extract of T-SQL stored procedure used

    	INSERT INTO dbo.PS2010_UserDelegationAudit (
    		ACTIVATION_UID,
    		DELEGATE_NAME,
    		RES_NAME,
    		DELEGATE_UID,
    		RES_UID,
    		START_DELEGATE,
    		ACTIVATION_DATE
    		) 
    	VALUES
    	(NEWID(),
    	@DelegateName,
    	@ResourceName,
    	@DelegateUID,
    	@ResourceUID,
    	@StartDelegate,
    	GETDATE()
    	)

    Sample Report

    image

    Please find below a brief code sample if you want to create delegate programmatically (a clean way would be to create a PowerShell cmdlet to automate the C# logic creation):

    UserDelegationDataSet userDelegationDataSet;
    UserDelegationDataSet.ResourceDelegationsRow row = userDelegationDataSet.ResourceDelegations.NewResourceDelegationsRow();
    Guid delegationUID = Guid.NewGuid();
    row.DELEGATE_UID = delegateUID; 
    row.RES_UID = resUID; 
    row.DELEGATION_FINISH = endDate;
    row.DELEGATION_START = startDate;
    row.DELEGATION_UID = delegationUID;
    userDelegationDataSet.ResourceDelegations.AddResourceDelegationsRow(row);
    resourceProxy.CreateDelegations(userDelegationDataSet);

  • Christophe Fiessinger's Blog

    Project Server IT Professionals Bookmarks

    For the upcoming TechEd for IT Professionals in Orlando we have prepared a list of about 70 bookmarks Project Server IT Professionals should be aware of.

    This list contains bookmarks to valuable resources such as deployment guides, deployment templates, recommended RSS feeds subscriptions, books, training course, etc…

    Also note the following newly released project templates:

  • Microsoft Office 2007 deployment (clean installation or upgrade from Office 2003)
  • Microsoft Office SharePoint Server 2007 deployment
  • Microsoft Office Project Server 2007 deployment (clean installation)
  • Microsoft Office Project Server 2007 deployment (migration from Project 2003)
  • For instance:

    image

  • Christophe Fiessinger's Blog

    Windows Server 2008 R2 Desktop Tweaks

    With the recent RTM release of Windows 7 and Windows Server 2008 R2 on July 22, I have updated all my desktops and laptops to Windows 2008 R2, so far no driver issues on any machine (How to upgrade your laptop/desktop to Windows Server 2008 R2?).

    One thing I could not figure was how to display a preview of pictures using Windows Explorer and other visual effects that are standard with Windows 7.

    Thanks to a Microsoft colleague the solution is simple, go to System Properties –> Advanced –> Performance Settings and check Show thumbnails instead of icons and voila!

    image

  • Christophe Fiessinger's Blog

    Analysis Services 2005 (OLAP) Permission for Project Server 2007

    Common questions and answers on AS 2K5 permissions for EPM 2007 (thank you David & Steven):

     

    Getting Cube to Build

    1.       Which application account is responsible for building the cube?  Is this the SSP account?  The SharePoint Timer Service account?

    SSP account

    2.       What are the database permissions needed for this account?  DataReader on ReportServer?  DataReader and DataWriter on OLAPdb?

    DataReader on ReportServer,   DataReader and DataWriter on OLAPdb (if using SQL as Analysis repository)

    3.       What are the system permissions needed for this account on the Project Server machines?  On the SQL Server machine?  On the Analysis Services machine?

    Simple user on PS machine. Have not tested on separate SQL/AS machine, but for  AS, it needs to be an AS Admin. In some cases I had to put it as a local admin on the SQL server.

    4.       Do you have a list of common error messages with their solutions?

    http://blogs.msdn.com/brismith/archive/2007/02/14/common-error-messages-when-building-cubes.aspx

     

    Allowing Users to View the Cube

    1.       What permissions need to be setup for users to view the cube?  I think part of this stems from the new Analysis Services 2005 security model.

    Normally, the right security is added automatically to a defaut AS role  during the cube build.

    The user added to this role have  the Data analysis Project server right. Typically in the Portfolio group

  • Christophe Fiessinger's Blog

    Microsoft Project 2010 Kinect Edition: You Are the Controller!

    I’m super excited to announce Microsoft Project 2010 Kinect Edition: You Are the Controller! Microsoft continues to be an industry leader in project management  software with this revolutionary new approach to Project Management. Microsoft Project 2010 Kinect Edition enables you to interact with your plan using you body, the wave of your hands and your voice. For instance simply built your schedule by waving your hands left and right; leverage the powerful resource planner view and perform resource allocation with a few gestures and voice orders. Project Portfolio Management will never be the same thanks to Microsoft Project 2010 Kinect Edition!

    Customer feedback:

    • “Microsoft nails it again! – being able to make strategic portfolio decision in front of the board members with my hands and voice made me a rocks star” Jan Kotas, CIO, Contoso Corp
    • “Planning is so much fun now and I get to work out at the same time, AGILE will never be the same – THANK YOU Microsoft I’m now so much more productive and happy“ Steve Master, PMP, President Litware PMO
    • “Being able to plan our next family vacation in the living room with my husband and kids was an awesome bounding experience” Jessica Arnold, Bellevue WA

    For more information check out: http://www.xbox.com/en-US/kinect

    Microsoft Project 2010 Kinect Edition Microsoft Project 2010 Kinect Edition

    PS: Yes it’s November 5th, 2010, but surely feels like April 1st…

  • Christophe Fiessinger's Blog

    SharePoint Performance Tuning References

    Thanks to Roger Lamb please find references to improve SharePoint’s and Project Server performance:

    Development specific tips:

    Upcoming book: Microsoft® Office SharePoint® Server 2007 Best Practices

    Achieve your IT objectives with proven, best-practice guidance on using SharePoint solutions.

    Get field-tested best practices and proven techniques for designing, deploying, operating, and optimizing Microsoft Office SharePoint Server 2007 and Windows SharePoint Services 3.0. Part of the new Best Practices series for IT professionals from Microsoft Press®, this guide is written by leading SharePoint MVPs and Microsoft SharePoint team members who’ve worked extensively with real-world deployments and customers. You’ll find out how to deploy the software, design your environment, manage content, analyze and view data, perform disaster recovery, monitor performance, and more. You'll learn how to create SharePoint sites that help your organization collaborate, take advantage of business insights, and improve productivity—with practical insights from the experts.

  • Christophe Fiessinger's Blog

    Must-read Newly published SharePoint White Papers

    On a weekly basis I always check this two great source of information on TechNet (ideally I’d love to be able to have an RSS subscription to these pages):

    The April 14, 2008 release contains a lot of very useful white papers as listed below, (it’s the end of week so plenty of time to read during the week-end!):

  • Christophe Fiessinger's Blog

    EPM & SharePoint Performance Counters

    Following Joel Oleson’s blog post on SharePoint performance counters please find below a list of EPM performance counters.

    I’m sure you are all already monitoring these in your production environment!

    Front End Perf Counters

    Back End (SQL Server) Performance Counters

    Object

    Counter

    Object

    Counter

    ASP.NET

    Application Restarts

    Processor(_Total)

    % Processor Time

    ASP.NET

    Request Execution Time

     

     

    ASP.NET

    Request Wait Time

    Memory

    Pages/sec

    ASP.NET

    Requests Queued

     

     

    ASP.NET

    Requests Rejected

    PhysicalDisk(_Total)

    Current Disk Queue Length

    ASP.NET

    Worker Process Restarts

    PhysicalDisk(_Total)

    Disk Read Bytes/sec

     

    PhysicalDisk(_Total)

    Disk Write Bytes/sec

    Memory

    Available Bytes

     

     

    Memory

    Pages/sec

    Process(sqlservr)

    % Processor Time

    Memory

    Pages/sec

    Process(sqlservr)

    Private Bytes

     

    Process(sqlservr)

    Working Set

    Process(LSASS)

    % Processor Time

     

     

    Process(OWSTIMER)

    % Processor Time

    SQLServer:Databases

    Transactions/sec

    Process(OWSTIMER)

    % Processor Time

    SQLServer:General Statistics

    User Connections

     

    SQLServer:Locks(_Total)

    Lock Wait Time (ms)

    Process(w3wp)

    % Processor Time

    SQLServer:Locks(_Total)

    Lock Waits/sec

    Process(w3wp)

    Page Faults/sec

    SQLServer:Locks(_Total)

    Number of Deadlocks/sec

    Process(w3wp)

    Page File Bytes

    SQLServer:SQL Statistics

    Batch Requests/sec

    Process(w3wp)

    Page File Bytes Peak

     

     

    Process(w3wp)

    Private Bytes

    System

    Context Switches/sec

    Process(w3wp)

    Private Bytes

     

     

    Process(w3wp)

    Virtual Bytes

     

     

    Process(w3wp)

    Virtual Bytes Peak

     

     

    Process(w3wp)

    Working Set

     

     

    Process(w3wp)

    Working Set

     

     

    Process(w3wp)

    Working Set Peak

     

     

     

     

     

    Processor(_Total)

    % Processor Time

     

     

     

     

     

    System

    Context Switches/sec

     

     

     

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib

    ProjectServer:QueueGeneral

    ProjectServer:QueueJobs

    % Sql Retries / Day

    % Jobs Failed / Day

    % Sql Retries / Hour

    % Jobs Failed / Hour

    Active Job Processing Threads

    % Jobs Retried / Day

    Average Unprocessed Jobs / Day

    % Jobs Retried / Hour

    Current Unprocessed Jobs

    Average Processing Time / Day

    New Jobs / Minute

    Average Processing Time / Minute

    Sql Calls / Hour/Day

    Average Wait Time / Day

    Sql Calls / Minute

    Average Wait Time / Minute

    Sql Retries / Minute

    Jobs Failed / Minute

     

    Jobs Processed / Hour/Day

     

    Jobs Processed / Minute

     

    Jobs Retried / Minute

     

     

    ProjectServer:Winproj

    ProjectServer:User Activity

    Average time taken for Project Open

    PSI Calls per Second

    Percentage of incremental save to full save

     

    Winproj full open count in the last hour

     

    Winproj full save count in the last hour

     

    Winproj incremental open count in the last hour

     

    Winproj incremental save count in the last hour

     

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

    image

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

    image

    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'
    image
    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:
     
    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!

    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

    How to extract and view Timesheet Audit Data?

    In EPM 2007 you have the ability to audit all timesheet transactions using the following flag:

    Server Settings >Time and Task Management > Settings and Defaults > Enable Timesheet Auditing

    Once audit has been activated, all timesheet changes get recorded in the following table in the Published database: MSP_TIMESHEET_ACTUAL_AUDIT

    Since the data is not in the Reporting db, I’ve created a query that uses data from both the Published and Reporting DB to extract and view timesheet audit data:

         DECLARE @DBNAME NVARCHAR(50)

         DECLARE @CMD NVARCHAR(4000)

         SET @DBNAME = REPLACE(DB_NAME(),'_Reporting','')

     

         SET @CMD=

         'SELECT       TSR.ResourceName AS ''Resource'',

                      TS_ACT_AUD_SEQUENCE ''Audit Sequence'',

                      TaskName AS ''Task'',

                      CONVERT(Decimal(9,2), TS_ACT_AUD_DELTA_VALUE/60000) AS ''Delta Value'',

                      TS_ACT_START_DATE ''Start'',

                      TS_ACT_FINISH_DATE ''Finish'',

                      MOD_DATE ''Modified'',

                      TSS.Description AS Status

                     

         FROM '+@DBNAME+'_Published.dbo.MSP_TIMESHEET_ACTUAL_AUDIT AS TSAA

         INNER JOIN MSP_TimesheetLine AS TSL

         ON TSAA.TS_LINE_UID=TSL.TimesheetLineUID

         INNER JOIN MSP_TimesheetTask AS TST

         ON TST.TaskNameUID=TSL.TaskNameUID

         INNER JOIN MSP_Timesheet AS TS

         ON TS.TimesheetUID=TSL.TimesheetUID

         INNER JOIN MSP_TimesheetResource AS TSR

         ON TSR.ResourceNameUID=TS.OwnerResourceNameUID

         INNER JOIN MSP_TimesheetStatus AS TSS

         ON TSS.TimesheetStatusID=TS.TimesheetStatusID

         ORDER BY ResourceName, TSAA.TS_ACT_AUD_SEQUENCE'

     

         EXEC (@CMD)

     

    A sample SQL Server Reporting Services report looks like this (RDL attached below):

    Timesheet Audit Data

     

    To enforce the EPM security model I recommend you write a PSI extension.

  • Page 5 of 69 (689 items) «34567»