August, 2007

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

  • 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

    Sample COM Add-in code for Project Professional 2007

    Please find below sample code (thank you Mike Shughrue) in C# for a Project Professional 2007 COM Add-in.  This code is very similar to writing a COM Add-In Project Professional 2003. In the long term an easier way to start would be to leverage new next version of Visual Studio Tools For Office (VSTO):

  • Christophe Fiessinger's Blog

    Lessons Learned from writing a PSI Extension

    After writing a PSI extension recently, Mike Shughrue and I learned the following lessons:

    ¾  Follow the deployment procedure – coding is easyJ, deploying a PSI extension requires multiple steps, follow each carefully. A good way to test if the web service is properly deployed is to type its address in IE and ensure it returns its definition.

    ¾  Locating DISCO.EXE – if you can’t find it under C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin; then install it using your Visual Studio DVD (under .NET Framework SDK -> Tools)

    ¾  Generating WSDL & DISCO files – if you get the following error when running DISCO.EXE under C:\Program Files\Microsoft Office Servers\12.0\WebServices\Shared\PSI:

    Microsoft (R) Web Services Discovery Utility

    [Microsoft (R) .NET Framework, Version 2.0.50727.42]

    Copyright (C) Microsoft Corporation. All rights reserved.

    ERROR: http://chrisfie03:8181/_vti_bin/PSI/reporting.asmx

      - The document at the url http://chrisfie03:8181/_vti_bin/PSI/reporting.asmx was not recognized as a known document type.

    The error message from each known type may help you fix the problem:

    - Report from 'http://chrisfie03:8181/_vti_bin/PSI/reporting.asmx' is 'The document format is not recognized (the content type is 'text/html; charset=utf-8').'.

    - Report from 'DISCO Document' is 'Discovery document at the URL http://chrisfie03:8181/_layouts/error.aspx?ErrorText=File Not Found. could not be found.'.

      - The document format is not recognized.

    - Report from 'WSDL Document' is 'The document format is not recognized (the content type is 'text/html; charset=utf-8').'.

    - Report from 'XML Schema' is 'The document format is not recognized (the content type is 'text/html; charset=utf-8').'.


    If you would like more help, please type "disco /?".


    The workaround is to create an IIS Virtual Directory under a site which is not managed by SharePoint Services V3, for instance “Test”, copy the ASMX in that directory and run this command instead:

    disco" http://chrisfie03:81/test/reporting.asmx

    Then copy the DISCO and WSDL files in the proper directory and follow the rest of the procedure to adapt the files to SharePoint V3.

    ¾  Automatically generate and deploy the DISCO and WSDL files – you could skip the steps above and use the following tool to automatically generate the two files and do the proper XML replacement: SharePoint Web Service DISCO and WSDL Generator

    ¾  Reading PSI configuration settings – Ideally the configuration settings such as database connection strings, impersonation parameters should not be hard coded in the C# for ease of deployment and maintenance. In our example the settings are defined in an application settings XML file (located under C:\Program Files\Microsoft Office Servers\12.0\WebServices\Shared\PSI) and the settings are set in the constructor of the WS. Another way to do it would be to store the settings in the web.config but he could not get it to work..

    ¾  Validate authorisation from all method call – to ensure that the EPM security is enforced each PSI calls validates the user using such algorithm:

    private bool IsAuthorized(Guid userGuid)


            bool isAuthorized = false;



                Reporting.wsResource.ResourceAuthorizationDataSet resAuthDS = resource.ReadResourceAuthorization(userGuid);

                Reporting.wsSecurity.SecurityGroupsDataSet secGrpDS;


                foreach (Reporting.wsResource.ResourceAuthorizationDataSet.GroupMembershipsRow dr in resAuthDS.GroupMemberships.Rows)


                    secGrpDS = security.ReadGroup(dr.WSEC_GRP_UID);

                    if (secGrpDS.SecurityGroups[0].WSEC_GRP_NAME == SEC_GROUP)


                        isAuthorized = true;







                // Eat the exception and return false.


            return isAuthorized;


Page 1 of 1 (3 items)