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 Risk Evaluation Form Solution

    Following yesterday’s webcast: Microsoft Project and SharePoint Server 2010 Better Together–free webcast July 27 & 28 someone asked how can the Risk Evaluation demoed be customized to your own business needs?

    This form is based on an InfoPath custom form and stores its answers tied to each project in the Project Risk Forms library in the demo dataset.


    To customize the form to your needs (risk assessment questionnaire) do the following:

    You can also download and check our based demo VM to see how we did in our sample demo dataset:

    Project 2010 Business Risk

    Project 2010 Technology Risk

    Project 2010 Implementation Risk

  • Christophe Fiessinger's Blog

    Customizing Analysis Services Cube Using Analysis Management Objects (AMO)

    This post describes the steps required to perform SQL Analysis Services 2005 Project Server 2007 Cube customization using Analysis Management Objects (AMO).

    (sample code below)



    To be able to override an EPM 2007 Event Handler and do AMO customization you will have to add the following references to the C# Class Library solution:

    ¾  C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.AnalysisServices.dll

    ¾  C:\Program Files\Microsoft Office Servers\12.0\Bin\Microsoft.Office.Project.Server.Events.Receivers.dll

    ¾  C:\Program Files\Microsoft Office Servers\12.0\Bin\Microsoft.Office.Project.Server.Library.dll

    Event Handler

    The CubeProcecessedEvent is overridden to launch our Analysis Services Cube customization once the standard EPM 2007 cube has been built:

        // Override CBS event to call custom AMO code

        public class CubeProcessedEvent : CubeAdminEventReceiver


            #region Public Methods


            public override void OnCubeProcessed(PSContextInfo contextInfo, CubeAdminPostCubeProcessEventArgs e)


                base.OnCubeProcessed(contextInfo, e);


                // Process Analysis Services customization




    Retrieving AS DB Connection Strings

    // --------------------------------------------

    // STEP 0: Retrieve Analysis Services server configuration from

    // Microsoft.Office.Project.Server.Eventing.exe.config

    // --------------------------------------------

    string connectionString = ConfigurationManager.AppSettings["ASConnectionString"];

    The CONFIG file is located under: C:\Program Files\Microsoft Office Servers\12.0\Bin

    Analysis Services Cube Customization

    This is the main method doing all the Analysis Services customization (using AMO):

    private void ProcessCubeCustomisation(string databaseName)


    Stopwatch timeToProcess = new Stopwatch();


    bool success = false;

    string msg = string.Empty;




        // --------------------------------------------

        // STEP 0: Retrieve Analysis Services server configuration from

        // Microsoft.Office.Project.Server.Eventing.exe.config

        // --------------------------------------------

        string connectionString = ConfigurationManager.AppSettings["ASConnectionString"];


        // --------------------------------------------

        // STEP 1: Connect to Analysis Services server.

        // --------------------------------------------

        Server asServer = new Server();



        // --------------------------------------------

        // STEP 2: Locate the necessary OLAP objects.

        // --------------------------------------------

        Database p12Database = asServer.Databases.GetByName(databaseName); // this throws exception if the database is not found

        Cube mspPortfolioAnalyzer = p12Database.Cubes.GetByName("MSP_Portfolio_Analyzer");

        Dimension taskListDimension = p12Database.Dimensions.GetByName("Task List"); // this throws exception if the dimension is not found


        // --------------------------------------------

        // STEP 3: Perform OLAP customisation

        // --------------------------------------------

        DimensionAttribute tnDimAtt = taskListDimension.Attributes.Add("Task Name");

        tnDimAtt.Usage = AttributeUsage.Regular;

        tnDimAtt.Type = AttributeType.Regular;

        tnDimAtt.OrderBy = OrderBy.Name;

        tnDimAtt.KeyColumns.Add(CreateDataItem(p12Database.DataSourceViews[0], "MSP_EpmTask_OlapView_Dimension", "TaskName"));

        tnDimAtt.NameColumn = CreateDataItem(p12Database.DataSourceViews[0], "MSP_EpmTask_OlapView_Dimension", "TaskName");


        // Create Task Name Hierarchy

        Hierarchy tnHierarchy = taskListDimension.Hierarchies.Add("TaskNameHierarchy");

        DimensionAttribute tlAttribute = taskListDimension.Attributes.GetByName("Task List attribute");

        tlAttribute.AttributeRelationships.Add("Task Name");

        tnHierarchy.Levels.Add(tnDimAtt.Name).SourceAttributeID = tnDimAtt.Name;

        tnHierarchy.Levels.Add(tlAttribute.Name).SourceAttributeID = tlAttribute.Name;


        ValidationErrorCollection errorCol = new ValidationErrorCollection();

        taskListDimension.Validate(errorCol, true);


        // --------------------------------------------

        // STEP 4: Process updates

        // --------------------------------------------

        // Process dimension update




        success = true;


    catch (Exception e)


        msg = "FAILED to process AS customisation, exception: " + e.Message;




    if (success)

        WriteEvent("SUCCESSFULY processed AS customisation, Time= " + timeToProcess.Elapsed.ToString(), EventLogEntryType.Information, 9999);


        WriteEvent(msg + " , Time= " + timeToProcess.Elapsed.ToString(), EventLogEntryType.Error, 9999);




    Event Handler with Custom AMO Code

    The procedure to deploy custom Event Handler (calling custom AMO code) is as follow:

    Register Event Handler

    1.      Connect to the PWA server and go to Server Settings

    2.      Select Operational Policies -> Server-Side Event Handler Configuration

    3.      Select Cube Admin -> Cube Processed


    4.      Enter the following information and click Save:


    Contoso Task Name Hierarchy


    Custom AMO code that aggregates tasks by name

    Assembly Name

    MicrosoftFrance.MCS.EPM2007.Events.ContosoAMO, Version=, Culture=Neutral, PublicKeyToken=9af84715c1210e08

    Classe Name




    Once the assembly has been deployed it should appear as shown below (takes a few minutes to take effect).


    Deploy Custom Assembly

    1.      Copy compiled assembly MicrosoftFrance.MCS.P12.Events.ContosoAMOEvent.dll from proper BIN directory to C:\WINDOWS\assembly (Drag & Drop)

    2.      Assembly should now appear in the folder as shown below:


    Add Eventing Config Parameters

    1.      Copy the EPM Eventing config file located under C:\Program Files\Microsoft Office Servers\12.0\Bin\Microsoft.Office.Project.Server.Eventing.exe.config to have a backup and open the file using an XML editor

    2.      Add the following parameter: ASConnectionString

    3.      <configuration>

    4.         <runtime>

    5.            <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

    6.               <probing privatePath="ProjectServerEventHandlers"/>

    7.            </assemblyBinding>

    8.         </runtime>

    9.          <appSettings>

    10.             <add key="ASConnectionString" value="CHRISFIE03\SQL" />

    11.       </appSettings>   

    12.   </configuration>


    First you will have to the build the cube with the newly deployed customization.

    The CBS (Cube Building Process) will look like this:

    ===== Initiating cube build process =====

    [2/27/2007 8:45 AM] Cube build request message has been added to the Project Server queue

    ===== Verifying and running pre-build server event handler =====

    [2/27/2007 8:46 AM] Verifying and running pre-build server event handler

    ===== Determining database and cube structure =====

    [2/27/2007 8:46 AM] Cube build initialization started

    [2/27/2007 8:46 AM] OLAP database and cube structure was determined successfully

    ===== Building database and cubes =====

    [2/27/2007 8:46 AM] Cube build session started

    [2/27/2007 8:46 AM] Analysis Services session started

    [2/27/2007 8:47 AM] 26 of the NT accounts that correspond to users that have 'View Olap Data' permission could not be added to the Project Server default OLAP role 'ProjectServerViewOlapDataRole'

    [2/27/2007 8:47 AM] Analysis Services session completed successfully

    ===== Verifying and running post-build server event handler =====

    [2/27/2007 8:47 AM] Verifying and running post-build server event handler

    ===== Processing OLAP database =====

    [2/27/2007 8:47 AM] Process OLAP database session started

    [2/27/2007 8:47 AM] Analysis Services session started

    [2/27/2007 8:49 AM] Analysis Services session completed successfully

    ===== Verifying and running post-process server event handler =====

    [2/27/2007 8:49 AM] Verifying and running post-process server event handler

    ===== Process Completed =====

    [2/27/2007 8:51 AM] Cube build request completed successfully.

    Ensure that the newly added hierarchy has been added to the cube using Visual Studio:



    New Hierarchy

    Use Excel’s Pivot Table to validate change, for instance:

    Work the show is a task that appears in both the Boat Show and Consumer Technology Show projects:




    Row Labels

    Actual Work


    Work the show



    Boat Show



    Consumer Technology Show



    Grand Total



    That’s it!


    ¾  All EPM 2007 cube customization should be tested extensively prior to any deployment into a Production environment.

    ¾  Adding custom customization at the end of the Cube Building process will also increase the time it takes to refresh the cube.

    ¾  Figuring out the proper UpdateOptions & ProcessType could be tricky at times and depending on what option you choose it will have a impact on performance

    ¾  To figure out the order in which the AMO needs to be made, open the AS db using Visual Studio, do the changes by hand, script the full AS database and check the XML changes.

    ¾  Test, test, test J


  • Christophe Fiessinger's Blog

    How to check Project 2007 version using VBA?

    [ Updated 4/25/2011 – modified code to support Windows 7, please not that this functionality is out of the box in Project Server 2010 and no longer requires such VBA script!]

    A great question we heard this morning during the Project 2007 SP2 webcast and every time we release a server and desktop software update for Project 2007 and Project Server 2007, is how do I ensure the desktop contains the proper software updates? For instance how I do ensure that ALL desktop version for Project that connect to Project Server have Service Pack 2 installed?

    Thanks to Adrian Jenkins please find below two easy way to achieve this by leveraging VBA (macro) within the Enterprise Global.
    A quick demo first:
    I try to save a plan and I will get the following warning:
    and WinProj will automatically be closed!

    In this first approach change MIN_PROJ_VERSION to the latest SP2 version number: 6425 

    Feel free to customize the Warning message at will!

    Declare Function RegCloseKey& Lib "advapi32.dll" (ByVal hKey&) 
    Declare Function RegOpenKeyExA& Lib "advapi32.dll" (ByVal hKey&, ByVal lpszSubKey$, ByVal dwOptions&, ByVal samDesired&, lpHKey&) 
    Declare Function RegQueryValueExA& Lib "advapi32.dll" (ByVal hKey&, ByVal lpszValueName$, ByVal lpdwRes&, lpdwType&, ByVal lpDataBuff$, nSize&) 
    Declare Function RegQueryValueEx& Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey&, ByVal lpszValueName$, ByVal lpdwRes&, lpdwType&, lpDataBuff&, nSize&)
    Const MIN_PROJ_VERSION = 6330
    Const HKEY_CLASSES_ROOT = &H80000000
    Const HKEY_CURRENT_USER = &H80000001
    Const HKEY_LOCAL_MACHINE = &H80000002
    Const HKEY_USERS = &H80000003
    Const ERROR_SUCCESS = 0&
    Const REG_SZ = 1&                          ' Unicode nul terminated string
    Const REG_DWORD = 4&                       ' 32-bit number
    Const KEY_QUERY_VALUE = &H1&
    Const KEY_SET_VALUE = &H2&
    Const KEY_CREATE_SUB_KEY = &H4&
    Const KEY_NOTIFY = &H10&
    Const KEY_CREATE_LINK = &H20&
    Const READ_CONTROL = &H20000
    Const WRITE_DAC = &H40000
    Const WRITE_OWNER = &H80000
    Const SYNCHRONIZE = &H100000
    Sub ProjectVer()
        Dim projVersion As String
        Dim version As String
        projVersion = RegGetValue$(HKEY_LOCAL_MACHINE, "Software\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\00002109B30000000000000000F01FEC\InstallProperties", "DisplayVersion")
        version = Mid(projVersion, 6, 4)
        If (CInt(version) < MIN_PROJ_VERSION) Then
            MsgBox "Your version of Winproj.exe is not valid and may cause problems." & Chr(13) & Chr(13) & _
            "The installation of new version of Microsoft Project is required!" & Chr(13) & Chr(13) & _
            "Required Version: " & Left(projVersion, 5) & MIN_PROJ_VERSION & ".XXXX" & (Chr(13)) & _
            "Found Version:     " & projVersion & Chr(13) & Chr(13) & _
            "Microsoft Project will now Exit." _
            , vbExclamation, "Program Version Check"
            Application.FileExit pjDoNotSave
        End If
    End Sub
    Function RegGetValue$(MainKey&, SubKey$, value$)
       ' MainKey must be one of the Publicly declared HKEY constants.
       Dim sKeyType&       'to return the key type.  This function expects REG_SZ or REG_DWORD
       Dim ret&            'returned by registry functions, should be 0&
       Dim lpHKey&         'return handle to opened key
       Dim lpcbData&       'length of data in returned string
       Dim ReturnedString$ 'returned string value
       Dim ReturnedLong&   'returned long value
       If MainKey >= &H80000000 And MainKey <= &H80000006 Then
          ' Open key
          ret = RegOpenKeyExA(MainKey, SubKey, 0&, KEY_READ, lpHKey)
          If ret <> ERROR_SUCCESS Then
             RegGetValue = ""
             Exit Function     'No key open, so leave
          End If
          ' Set up buffer for data to be returned in.
          ' Adjust next value for larger buffers.
          lpcbData = 255
          ReturnedString = Space$(lpcbData)
          ' Read key
          ret& = RegQueryValueExA(lpHKey, value, ByVal 0&, sKeyType, ReturnedString, lpcbData)
          If ret <> ERROR_SUCCESS Then
             RegGetValue = ""   'Value probably doesn't exist
            If sKeyType = REG_DWORD Then
                ret = RegQueryValueEx(lpHKey, value, ByVal 0&, sKeyType, ReturnedLong, 4)
                If ret = ERROR_SUCCESS Then RegGetValue = CStr(ReturnedLong)
                RegGetValue = Left$(ReturnedString, lpcbData - 1)
            End If
        End If
          ' Always close opened keys.
          ret = RegCloseKey(lpHKey)
       End If
    End Function

    Another way to validate the desktop version, uses the file system. The only challenge with this approach is determining the location of the Program Files directory.

    Sub projVersion()
        Dim MainFolderName As String
        Dim LastMod As String
        Dim Created As String
        Dim Size As String
        Dim projVersion As String
        Set objShell = CreateObject("Shell.Application")
        MainFolderName = "C:\Program Files\Microsoft Office\Office12"
        Set FSO = CreateObject("scripting.FileSystemObject")
        Set oFolder = FSO.GetFolder(MainFolderName)
         'error handling to stop the obscure error that occurs at time when retrieving DateLastAccessed
        On Error Resume Next
        For Each fil In oFolder.Files
            Set objFolder = objShell.Namespace(oFolder.Path)
            Set objFolderItem = objFolder.ParseName(fil.Name)
            Select Case UCase(fil.Name)
                Case Is = "WINPROJ.EXE"
                    LastMod = fil.DateLastModified
                    Created = fil.DateCreated
                    Size = fil.Size
                    MsgBox ("File: " + fil.Name + " Last Modified: " + LastMod + " Created: " + Created + " Size: " + Size)
                    'do all of the normal tests here to determine which version you need and which you have. See above.
            End Select
    End Sub
  • Christophe Fiessinger's Blog

    The Rise of Enterprise Social Networks by @markfidelman

    The following white paper was released this week and available for download from Microsoft: The Rise of Enterprise Social Networks. This 26 pages whitepaper from Mark Fidelman is a good read to help you help understand the business value of Enterprise Social and how to get started :

    Social technologies, combined with data analysis and mobile technologies are significantly enhancing an organization’s ability to be responsive to market changes and will enable employees to work on the tasks that most benefit the company at any given point in time. In the near future, companies utilizing enterprise social software will be able to spot trends, provide information to thier employees in context, and leverage the wisdom of the organization to rapidly complete tasks and surface previously hidden pockets of valuable information.

    Chapter summary:

    • Welcome to the Enterprise Social Revolution
    • You Need a Digital Village
    • The Evolution of Enterprise Collaboration
    • The Business Value of Enterprise Social
    • The Four Building Blocks for a Successful ESN Building
    • Mapping the Strategy for the Rest of the Business
    • Success initiatives for enterprise social companies
    • Why Microsoft is Best Positioned in the Space
    • Tying it all together
    • Action items & Questions to ask your organization

    The Rise of Enteprise Social Networks

    I have also read this book from Mark Fidelman which I also recommend: Socialized! How the Most Successful Businesses Harness the Power of Social (Social Century Series).

    Happy reading!

  • Christophe Fiessinger's Blog

    Project in a Terminal Services environment

    Please find below useful links when deploying Project Standard or Project Professional on a Terminal Services (or Citrix) environment.

    A common issue I have heard/seen is that the version used on Terminal Services is incorrect (you need a Volume License Key) with this type of error message displayed: "This copy of Microsoft Office Project cannot be used on Terminal Server".

    This issue is addressed in the following knowledge base article:

    Other useful links are:

  • Christophe Fiessinger's Blog

    Project Server 2007 Report Pack II – An incredible asset you should all leverage

    This is a blog post that is worth checking out! My colleague Jan Kalis just announced the release of the Project Server 2007 Report Pack II (recorded webcast and demo here). Why is it the best thing since slice bread? Well check out the following list of SQL Server Reporting Services it contains (and to make it easier to use in your environment it all contains full documentation as well as separate Visual Studio packages and directories of ALL T-SQL queries). I’m sure you will find few key nuggets in there that will help you with your day to day activities as well as surface key information from your Project Server 2007 Project Portfolio Management gold mine of information!

    Microsoft Project Server 2007 Governance Reports Microsoft Project Server 2007 Management Reports
    • Assignment to Timesheet Consistency
    • My Planned Time Off
    • My Projects and Assignments
    • My Task Count and Status
    • My Project-Oriented Timesheet Activity
    • Project_Detail (now includes includes deliverable and cross-project link information, along with a reduced size KPI Average graphical indicator)
    • Resource Activity by RBS and Position Role
    • Resource Allocation
    • Resource Allocation sub-report
    • Resource Capacity versus Demand Heatmap by Position Role
    • Task Update Compliance
    • Timesheet Compliance report ( updated to allow for multiple timesheet period review )
    • Active Resource Details
    • Administration Dashboard
    • Archive Project Details
    • Draft Project Details
    • Inactive Resource Details
    • Metadata Assignment Custom Field Details
    • Metadata Custom Field Modifications
    • Metadata Enterprise Template Modifications
    • Metadata Lookup Table Modifications
    • Metadata Resource Custom Field Details
    • Metadata Task Custom Field Details
    • Project Baseline Details
    • Project Task and Assignment Details
    • Project Type Details
    • Published Project Details
    • Queue – Project jobs
    • Queue – Timesheet jobs
    • Security Categories Modified
    • Security Groups Modified
    • Security No RBS setting
    • Security Permissions on User Level
    • Security Templates Modified
    • Security Users Modified
    • SQL Server Database Space Usage
    • SQL Server Fragmentation
    • SQL Server Table Space Usage
  • Christophe Fiessinger's Blog

    How to create a Milestone Report

    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:

    153 2008-03-10, 22_54_36

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

    154 2008-03-10, 22_55_29

    Going further you could leverage Excel Services to render this report in your SharePoint Server farm.

  • Christophe Fiessinger's Blog

    Running SharePoint Server and Project Server 2007 on Windows Server 2008 R2 Beta

    It works! I downloaded the ISO from MSDN and followed the instructions to deploy SharePoint Server 2007 and Project Server 2007 on Windows Server 2008, and it works like a charm on R2 (notice the Windows 7 similar toolbar at the bottom with the big PowerShell icon)!


    What’s new with Windows Server 2008 R2 check these two links:

    Also check this related post if you want to experiment with our upcoming OS: Windows 7 Beta and Project Professional 2007

  • Christophe Fiessinger's Blog

    Microsoft Project Server 2010 Hardware and Software Requirements

    It’s now officially on TechNet for SharePoint and since Project Server 2010 is built on SharePoint Server 2010 the same requirements applies: Determine hardware and software requirements (SharePoint Server 2010) image

  • Christophe Fiessinger's Blog

    Sample Project Server 2003 to Project Server 2007 Migration Plan

    Due to popular requests, please find below a sample plan to migrate from EPM 2003 to EPM 2007.  Use this plan as a guide and a reminder of all phases and tasks that need to be accomplished during the migration project.  The effort (“work”) for each task does not necessarily represent what is required for your environment so use it with a “grain of salt”.

    The migration project is divided into four phases:

    1.       Resource Planning – In this phase the hardware and personal resources must be requested and obtained. It is assumed that a full 4 working weeks will be needed to accomplish this.

    2.       Migration Planning - here initial training occurs, plans are made, expectations are set, and agreements from all parties are obtained.

    3.       Migration Test – Here a test system is built out, and the migration procedures are tried out and verified for correctness. This duration of this phase is difficult to predict as issues sometimes occur that must be addressed by support.

    4.       Production Migration – Here we build up a new production system, migrate the data, and have all users verify that the data has migrated correctly. When this step is successfully completed, the old system can be turned off.

     Project Server Migration Plan

Page 4 of 69 (689 items) «23456»