Christophe Fiessinger's Blog Updates on Microsoft's Enterprise Social Networking (ESN) and Project Portfolio Management (PPM) offerings
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?
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?
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
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=18.104.22.168, 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
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!
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:
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.
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
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.
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
GROUP BY P.ProjectName, P.ProjectAuthorName, P.ProjectStartDate, P.ProjectFinishDate
For instance for Proj4 it contains the following data:
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):
Going further you could leverage Excel Services to render this report in your SharePoint Server farm.
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!
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!):