Christophe Fiessinger's Blog Updates on Microsoft's Enterprise Social Networking (ESN) and Project Portfolio Management (PPM) offerings
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
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)
// Process Analysis Services customization
Retrieving AS DB Connection Strings
// STEP 0: Retrieve Analysis Services server configuration from
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
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, "MSP_EpmTask_OlapView_Dimension", "TaskName"));
tnDimAtt.NameColumn = CreateDataItem(p12Database.DataSourceViews, "MSP_EpmTask_OlapView_Dimension", "TaskName");
// Create Task Name Hierarchy
Hierarchy tnHierarchy = taskListDimension.Hierarchies.Add("TaskNameHierarchy");
DimensionAttribute tlAttribute = taskListDimension.Attributes.GetByName("Task List attribute");
tnHierarchy.Levels.Add(tnDimAtt.Name).SourceAttributeID = tnDimAtt.Name;
tnHierarchy.Levels.Add(tlAttribute.Name).SourceAttributeID = tlAttribute.Name;
ValidationErrorCollection errorCol = new ValidationErrorCollection();
// STEP 4: Process updates
// Process dimension update
success = true;
catch (Exception e)
msg = "FAILED to process AS customisation, exception: " + e.Message;
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
MicrosoftFrance.MCS.EPM2007.Events.ContosoAMO, Version=220.127.116.11, Culture=Neutral, PublicKeyToken=9af84715c1210e08
Once the assembly has been deployed it should appear as shown below (takes a few minutes to take effect).
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:
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
5. <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
6. <probing privatePath="ProjectServerEventHandlers"/>
10. <add key="ASConnectionString" value="CHRISFIE03\SQL" />
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:
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:
Work the show
Consumer Technology Show
¾ 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
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): http://blogs.msdn.com/project_programmability/archive/2007/07/30/vsto-add-in-support-for-project-2003-and-project-2007.aspx
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.
- 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:
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);
foreach (Reporting.wsResource.ResourceAuthorizationDataSet.GroupMembershipsRow dr in resAuthDS.GroupMemberships.Rows)
secGrpDS = security.ReadGroup(dr.WSEC_GRP_UID);
if (secGrpDS.SecurityGroups.WSEC_GRP_NAME == SEC_GROUP)
isAuthorized = true;
// Eat the exception and return false.
The Project Server Interface (PSI) for Microsoft Office Project Server 2007 can be extended with custom Web services. PSI extensions tightly integrate with Project Server, can call other PSI methods, and can use the same security infrastructure that the rest of the PSI uses. The following article in the SDK describe how to perform a PSI extension: Walkthrough: Creating a PSI Extension - http://msdn2.microsoft.com/en-us/library/bb428837.aspx
Mike Shughrue and I recently did a presentation on this subject and we came up with the following reasons why one might consider such customisation:
1. Mining data from the Reporting database – create a custom web service that return the exact data you are interested in and call that WS from your favourite reporting tool (Excel, Reporting Services ...)
2. Mining data not in the Reporting database – not all EPM data reside in the Reporting database, for instance you could retrieve the timesheet audit data from the Published database.
3. Real time data – Let’s say the cube building service is schedule every night at 2am and you are doing your financial closing and you need the latest project data from the Reporting database right away; a PSI extension could address that need.
4. Consolidating information from multiple data sources (EPM, line-of-business …) – for instance you want to extract Actual Work data and match timesheet line to account code from your external accounting system. You could have an account to TS line mapping table in the Reporting database and pull the data with the corresponding mapping.
5. Performing impersonation for specific jobs – for instance users who are not part of the EPM resource pool need to access data from the Reporting database. External suppliers/consultant or someone from another department, an accountant for instance needs to query the actual work entered by EPM resources for the current billing cycle. Since these people are not part of the EPM resource pool you could create a specific user that has only access to that PSI extension (via and EPM security group).
6. Integration with line of business applications – third party application can easily “pull” data from EPM 2007 by calling a PSI extension with the data in the correct format. For instance a timesheet extract could contain all relevant information in the proper format for the LOB to “consume” it.
I am sure there are other reasons why one might consider PSI extension so feel free to respond to this post.
Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists:
This add-in accompanies the MSDN article "Publishing and Synchronizing Excel 2007 Tables to SharePoint Lists", available in the Related Links section. The add-in allows you to publish a read-write list to Windows SharePoint Services.
Two parameters help you manage the size and location of Project Professional 2007’s Active Cache (HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\MS Project\Settings):
These two parameters are also accessible via Project Pro’s menu: Tools -> Local Project Cache -> Cache Settings
It is thus recommended to control via a Group Policy Object (GPO) these two parameters specially when Proj Pro. is deployed in a Terminal Server/Citrix environment.
For more information on how to use GPO with Office 2007 please refer to the following documents:
¾ Enforce settings by using Group Policy in the 2007 Office system
¾ 2007 Office System Administrative Templates (ADM)
Please note 200MB is the minimum recommendation for the Cache Size Limit.
Yes EPM (Enterprise Project Management) is an Enterprise application and it needs to be treated as such with Enterprise Resources J Oxford English Dictionary’s definition of Enterprise is a project or undertaking, especially a bold one.
I often compare an EPM deployment to other enterprise application such as SAP in the ERP space or Siebel in the CRM space. Companies typically never roll out these applications in a Production environment overnight. Companies get experts involved to help them define the best Architecture based on their functional and technical requirements. Companies also put in place multiple environments (typically three: development, Integration, Production); process are put in place to define the usage of each and how changes are applied from one to the other. Server and desktop deployment procedures are documented and reviewed. An Operation Guide specific to the company’s environment and architecture is written. A Disaster Recovery Guide is written to address any platform un-planned downtime. Last but not least company resources are trained on the new solutions & technologies deployed so that they are autonomous and can do basic troubleshooting.
Quite often I visit customers that deploy EPM like a desktop product and treat it as such. Yes it is very easy to install EPM with couple clicks on server in a Pilot or Production environment (my record is 30min for EPM 2007 with the all pre-requisites pre-installed), however the project does not end after PWA pages gets displayed and the OLAP cubes gets refreshed.
From experience all Enterprise EPM deployment requires the following Enterprise Resources:
¾ Architecture study: don’t just read our prescriptive guidance on TechNet to define the physical architecture that fits your functional and technical requirements. It’s a good start but not enough from experience. Get a skilled external EPM Architect to assist you in this study. He will bring experience from other architecture recommendation and deep product knowledge to make the best architecture recommendation for today’s volumes and usage but also taking into consideration future changes in usage/volume/product.
¾ Deployment Guide: the deployment procedures for both the servers and the desktop needs to be documented in detail with you company’s specific architecture and constraints.
¾ Operation Guide: there is no reason why every EPM deployment should not have one. Areas it should contain for instance: maintenance plans for IIS, SQL, performance counters to track... Again getting an EPM expert to help you write one is the safest route in the long term.
¾ Disaster Recovery Plan: even if you have a single server deployment you should have one. In case of a hardware/software failure or both an administrator should know what to do anytime of the day, any day of the week without calling Microsoft/a Partner … For large deployment with high availability including Network Load Balancing, and Clustering technologies it’s a must!
¾ Multiple Environments: very often customer plan for one environment (their Production environment) during a deployment. They then start customizations and start applying them directly in Production without any prior validation on a separate environment (Integration for instance). If customizations are perfect and bug free then everyone is happy but if it’s not the case the production environment goes down and the EPM users might not be happy! Similarly changes in architecture for instance SAN disk changes should be validated prior on a separate environment and should not be rolled out in production unless it has been validated (+ it should not be rolled out during working hours!).
¾ Optimum SAN configuration: too often SQL data is either not stored on a SAN or worst stored on a SAN but not optimized. Get a specialist to review your setup and ensure you have the best configuration.
¾ Skilled Employee: for instance since EPM and SharePoint data are all stored in a MS SQL database it’s key to understand well SQL 2000/2005. Database administrators should be able to put together a maintenance plan, optimize disk configuration, understand SQL performance counters and do basic troubleshooting using the SQL Server Profiler or SQLIO. Also administrators of the solutions should be knowledgeable of Windows 2003, Internet Information Services (IIS), .NET 2.0/3.0, Terminal Server/Citrix if used, and last but not least they should all have a basic understanding of the EPM solution, it is architecture and each component (services, databases …). If a company does not have these skills or want to refresh them, get an expert to train your employees or send them to training events.
Please find below a study I did for a customer to demonstrate the advantage of the new EPM 2007 architecture and specifically how Project Professional 2007 communicates to the application server.
DISCLAIMER: the times presented below highly depend on the EPM Architecture, the project plan data, as the network between the test machine and the servers, as well as the testing methodology. There is thus no guarantee on the response time in your environment!
Contoso is a worldwide company with many branch offices and is considering a migration from Project Server 2003 to Project Server 2007 but requires reassurance that the newer version of EPM would handle high latency environment when using Project Professional 2007. Contoso uses a Citrix farm today to address the latency issues of Project Professional 2003.
The results showed that Project Professional 2007 runs fine under a high latency environment thus Contoso can migrate from EPM 2003 to EPM 2007 and not use a Citrix farm anymore to run Project Professional.
EPM 2007 Architecture
Project Server 2007 was installed on a single server (including SQL 2005) with the following specifications:
¾ Computer modem: Dell Power Edge 2850
¾ Processor: Dual proc Xeon 3.6 Ghz
¾ System memory: 4 Go, FSB 800 Mhz
¾ Hard drive: 36 Go Ultra Scsi 320 15000 rpm
¾ Windows 2003 R2, Enterprise Edition US + SP1
¾ SQL Server 2005, Enterprise Edition US + SP1
¾ Project Server 2007, x86 EN
The EPM Test Framework (EPMTF) was used to automate the testing process of the Project Professional 2007.
The EPMTF ran on a Dell Latitude D600 laptop with 2 Go RAM; Pentium 1,7GHZ & 7200 rpm hard drive running Windows Server 2003 + SP1.
No latency simulation tool were used and instead a true 120 ms latency was generated by accessing an EPM 2007 server located at the MS France lab and accessing it from home via a VPN connection.
¾ Contoso gave us a generic project plan used to track “business as usual” activities.
¾ This initial plan contained the following:
¾ 440 tasks
¾ 10 projects Enterprise Custom Fields
¾ 10 resource Enterprise Custom Fields
¾ 5 task Enterprise Custom Fields
¾ 5 resources
¾ 1 to 5 resource assignment per task
¾ No baseline
To simulate the impact of project size we multiplied the total number of tasks for each run as shown below, total number of tasks per run:
Only one use case was tested, it included the following actions:
1. Start Project Professional 2007 and open project plan
2. Add one task at the end of the project plan
3. Save project plan
4. Publish project plan
5. Close project plan
Average response time in milliseconds for five runs for each action and each project size with 120ms latency:
Total Number of Project Tasks (results in ms)
The following observations can be made from the results above:
¾ The time to Open a plan “stays” under 10 seconds which is acceptable.
¾ Close & Publish actions are not impacted by the project size (number of tasks).
The following table shows the percentage gain (compared to the table above) obtained by using the Active Cache when performing each action:
Total Number of Project Tasks
¾ As expected the AC has mostly an impact when opening the project plan.
¾ AC does generate significant time improvement as the project plan increases in size.
Raw results (in milliseconds):
No Active Cache
Opening/Publishing/Saving actions in Project Professional 2007 are well under 10 seconds with 120ms latency; the responses time are thus acceptable.
The new Project Server 2007 architecture introduces significant improvement in performance when using Project Professional in a high latency environment. The performance improvement is mainly due to the fact the data access layer was rewritten to use web services to access the database instead of ODBC connections (EPM 2003).
Active Cache does provide further performance improvement when opening a large project plan after it has been open once before (cached copy of project plan is created the very first time a plan is open).
In theory Contoso does not need to setup a Citrix farm to use Project Professional 2007 from a performance point a view but might still require it if the client desktop does not meet the Office 2007 minimum requirements of XP SP2 for the OS.
Must read if you are an administrator and enjoy running scripts instead of navigating a User Interface:
For instance to retrieve a list of projects you can issue the following command:
stsadm -o projenumprojects –url <URL name>
All EPM 2007 options available are:
If you are planning to do SharePoint/EPM development/customisations, this document is a good review of best practices: http://msdn2.microsoft.com/en-us/library/bb428899.aspx
Summary: Learn to properly conduct team-environment development of Microsoft Office SharePoint Server 2007 sites and assemblies (Web Parts, site templates, custom list templates), as well as develop Microsoft Office SharePoint Designer artifacts (master pages, workflows, CSS sheets). (13 printed pages)