Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

February, 2007

  • Brian Smith's Microsoft Project Support Blog

    Microsoft Office Project Server 2007 and SQL Server 2005 Analysis Services - Building Cubes


    Following on from my permissions piece with Project Server I will extend this logic to the service accounts and permissions to get a successful cube build.  I'll start with an explanation of what goes on when building cubes which should help any troubleshooting you do.

    When you click on Build Cube then this kicks of a sequence of events starting with the save of any new or changed data in the cube settings - such as the server or cube name.  Next a job will be placed on the Project Server queue requesting a cube build.  This job will be picked off the queue and processed by the Microsoft.Office.Project.Server.Queuing.exe process, which will spawn the ProjectServerOLAPCubeGenerator.exe process. Both of these processes will be running under the identity of the admin account of the SSP - in my last posting this is the SSPAdmin.  So this user needs to be an admin within Analysis Services so it can communicate through DSO to Analysis Services.  This permission is added through a SQL Management Studio connection to Analysis Services by right-clicking the instance name and then selecting Properties, selecting the Security tab and then adding the user (a restart of the Analysis Services service at this point will also unsure the running instance is aware of the permission change) .  This process also needs to access the repository of meta data used to define the cubes.  This repository is detailed in KB 921116 (as are some other pre-requisites for multi server environments) and is in a share on the Analysis Services server called MSOLAPRepository$.  If you have a single server then the share will not be used - instead the direct directory location of C:\Program Files\Microsoft SQL Server\MSSQL.X\OLAP\DSO9. (The X will be a number relating to the installation of analysis services).  Therefore SSPAdmin, or your equivalent service account will need read and write access to this directory - and if you are in a multi server environment then also read/write access via the share.

    The next activity in the cube building process is the Analysis Services executable - MSMDSRV.exe -  actually building the cube based on the instructions given by the ProjectServerOLAPCubeGenerator.exe process.  This executable runs under the identity of the account running the SQL Server Analysis Services (MSSQLSERVER) (or named instance) service.  I'll refer to this account as ASAdmin  So this account needs to be able to read the reporting database of the Project Server instance, which is in effect the staging tables for the cubes.  Adding a login to SQL Server for ASAdmin with datareader role on ProjectServer_Reporting (or whatever reporting database name you are using) achieves this.  That should be all you need to get a cube building.

    So basically the SSPAdmin needs to be an admin in Analysis Services with read/write access to the repository.  ASAdmin needs datareader access to the reporting database.

    Also remember - when building a cube your application server is talking to/from your SQL Server Analysis Services server - when viewing or building views in Project Web Access your client PC is talking directly to your Analysis Services server (and each client needs the ASOLEDB 9.0 components).  Make sure any firewalls allow for this traffic.

    The default instance of Analysis Services will normally be listening on port 2383.  If you have named instances then the SQL Browser service will need to be running on the server to tell give clients a port for the named instance.  The SQL Browser is normally on port 2382.

    Named instances of Analysis Services will have other dynamically allocated ports. These can be discovered by looking in the configuration file for SQL Server Browser.  Open the msmdredir.ini file located at %Program files%\Microsoft SQL Server\90\Shared\ASConfig and look at the <Instances> section in it.  On 64 bit machines this may be in the Program Files (x86) directory.
    Here is an example:-


    and would mean your AS2005 instance is listening on port 1259.

    For my next post, rather than cluttering this one, I will show a variety of the errors from both ULS logs and Event logs that can appear if the above settings are not in place.

    Technorati Tags:

  • Brian Smith's Microsoft Project Support Blog

    Operating System and SQL Permissions for the Microsoft Office Project Server 2007 Service Accounts


    As part of some internal training I captured the details of which groups the various accounts that can be used for Project Server 2007 end up belonging to.  I though this might be useful to share.  The key thing here is that you do not normally need to do any of this manually - and even if you change some accounts then as long as you use the UI or stsadm the group memberships should be set correctly. 

    So for my scenario I have 4 users.  FarmAdmin, SSPAdmin, DefAppPool and SSPAppPool and these are going to used as the farm administrator of Windows SharePoint Services (FarmAdmin), the admin account for the Shared Services Provider (SSPAdmin) and the identities for the two application pools for the initial Port 80 site (DefAppPool) and the random port for the SSP (SSPAppPool).  In a farm environment these would all need to be domain accounts.  In my tests they were all local in a Virtual Server image.  These could all be the same account - but some customers prefer each to be different - thus allowing each to have minimum permissons.  I carried out the install as myself - an administrator on the server.

    Once I had finished my installation the following groups had added the following members:-

    IIS_WPG - FarmAdmin, SSPAdmin, DefAppPool, SSPAppPool
    WSS_ADMIN_WPG - FarmAdmin
    WSS_WPG - FarmAdmin, SSPAdmin, DefAppPool, SSPAppPool

    And in SQL Server the following logins had been added with roles set as noted below:-

    Server roles - dbcreator and securityadmin
    User mappings to the PWA, SSP and WSS content databases with dbo
    User mappings to the SharePoint_Config and SharePoint_AdminContent  with dbo and WSS_Content_Application_Pools role

    No server roles
    User mapping to PWA Archive draft and published with datareader, datawriter and ProjectServerRole
    User mapping to PWA reporting as above plus ddladmin
    User mapping to SharedServices and WSS Content databases with dbo role
    User mappings to the SharePoint_Config and SharePoint_AdminContent  with WSS_Content_Application_Pools role

    DefAppPool and SSPAppPool
    No server roles
    User mapping to SharedServices database and their respective WSS_Content databases as dbo
    User mappings to the SharePoint_Config and SharePoint_AdminContent  with WSS_Content_Application_Pools role

    In my next posting I will take this to the next level and document other settings and permissions required to get Project Server 2007 working with SQL Server 2005 Analysis Services.

    Technorati Tags:

  • Brian Smith's Microsoft Project Support Blog

    One event handler and multiple PWA sites - Where did that event come from?


    An interesting support case came in through our EMEA team (thanks Valdemar) where a customer had a single event handler that was registered in different sites - but when the event was raised they needed to know which site fired it - so they could make the necessary updates against the right site.  In this case they were updating the list of projects in different categories - both on the publishing and check-in events.

    So, how do you find out?  The event itself has a parameter of contextInfo and one of the properties that gets set for events is the SiteGuid - which is actually the SiteId of the WSS site hosting PWA.  So this can identify the instance of PWA.  The next step to getting a URL rather than the GUID is to create a new instance of SPSite using this GUID and then simply read the URL property.  So a full event handler that has the extra reference to the required SharePoint library and writes the Project name, the Site ID and the Project Server URL out to the event log would look like this:-

    using System;
    using System.Collections.Generic;
    using System.Diagnostics;
    using System.Net;

    using System.Text;
    using Microsoft.Office.Project.Server.Events;
    using Microsoft.Office.Project.Server.Library;
    using WSSDll = Microsoft.SharePoint;

    namespace TestEventHandler
        public class MyEventHandler: ProjectEventReceiver
            public override void  OnPublishing(PSContextInfo contextInfo, ProjectPrePublishEventArgs e)
              base.OnPublishing(contextInfo, e);
                EventLog myLog = new EventLog();
                myLog.Source = "Project Event Handler";

                // Get information from the event arguments, and
                // write an entry to the Application event log.
                string projectName = e.ProjectName.ToString();
                Guid siteGuid = contextInfo.SiteGuid;
                string pwaUrl = new WSSDll.SPSite(siteGuid).Url;
                int eventId = 3652;
                string logEntry;

                logEntry = "Project: " + projectName +
                            "\nSiteId " + siteGuid.ToString() +
                            "\nPWA Instance: " + pwaUrl;
                myLog.WriteEntry(logEntry, EventLogEntryType.Information, eventId);


    Not very useful in that state - but you can add some extra stuff to do some real work.  They key thing is you don't have to deploy different event handlers for each instance of PWA - just deploy once and then register in each of the PWA instances you want to use it.  And no need to modify your event handler just because you add a new PWA site to your system.  See the Project Server 2007 SDK for an end-to-end explanation of creating and debugging Project Server 2007 event handlers.


    Technorati Tags:

  • Brian Smith's Microsoft Project Support Blog

    Adding Timephased Actual Work through the PSI


    There is some great content in the SDK on statusing (search for SubmitStatus and UpdateStatus) and even more coming soon.  One of the missing pieces just at the moment is around adding timephased data.  So here is a very brief example.  See the SDK for better examples of programmatically building the XML - but the key piece is the format of the XML passed to the statusing.UpdateStatus web service.  In my example I have a 5 day task assigned to me starting next Monday - and I want to update the actual work in my tasks to show 8h on Monday the 5th and 8h on Wednesday the 7th.  The XML for this change is as follows:- 


    <Proj ID="a3349ba9-7eb8-4921-9024-483d0a732f1a">

    <Assn ID="dd6a2aa8-b754-48b5-a6fe-b5cd50cea19b">

    <PeriodChange PID="251658250"  Start="2007-02-05T08:00:00" End="2007-02-05T17:00:00">480000</PeriodChange>

    <PeriodChange PID="251658250"  Start="2007-02-07T08:00:00" End="2007-02-07T17:00:00">480000</PeriodChange>






    ·         a3349ba9-7eb8-4921-9024-483d0a732f1a is my Proj ID,

    ·         dd6a2aa8-b754-48b5-a6fe-b5cd50cea19b is my Assignment ID

    ·         The PID 251658250 means s_apid_actual_work (or Actual Work - from PSLibrary.AssnConstID.s_apid_actual_work - see SDK for more details of these constants)

    ·         Start and End time format is critical – the ULS logs are good at giving help when you get a LastError=StatusingInvalidChangelist Instructions exception.

    ·         480000 = 8 hours



    So executing the statusing.UpdateStatus(changeXml); where the changeXml is the same as presented above will do exactly the same as entering the two lots of 8hrs in through PWA.  Tuesday will be updated to 0h planned work (because I have entered actual work for Wednesday)  - and 8h will be pushed back to next Monday.

    Technorati Tags:

Page 2 of 2 (9 items) 12