Project Server 2007 did some improvement on WSS integration (If you want to know about integration between project server 2003 and WSS 2.0, please see my post:Customize WSS Role/User permission for Project Server 2003).  You may read Microsoft TechNet article about this: http://technet2.microsoft.com/Office/en-us/library/c9ca0b66-c167-4cd1-8ecf-6b311cf6976a1033.mspx?mfr=true.

 

Project Server 2007 is completed built on the top of WSS 3.0.  It will create four SharePoint groups on every project workspace:

Normally user would not be in two groups at same time, except team members and readers.  User could be in team members and readers groups if users assigned to tasks and have view project workspace permission on my organization category.

 

By default, WSS security settings will change according to PWA security changes.  However, you can change the settings on the Project Workspace Provisioning Settings page to check the box "Automatically add Project Web Access users to project team Web site when SharePoint site is created or when the project manager publishes the project information to Project Server"

 

Then, what scenarios will change the WSS security?

Since the first three scenarios may affect large number of project workspace, the best practice is to create/change those resources during non-working hours.

 

Microsoft documents that denying Logon permission and View Project Workspace will affect WSS security.  However, as I tested, I did not find denying those permission will affect WSS.

I did not find any thing about Timesheet workflow and which event will occur if you do something in PWA.  So I did some testing and come out with this diagram.  If you found something different, please email me.

 

In project server 2007, timesheet is a very good new feature.  However, when user enters timesheet and save it back to server, the server does not validate the number.  For example, user can enter 30 hours for a day.  You can set the limitation in Timesheet settings, however project server only validate timesheet hours when user submits it.  What if you don't want team members to enter ridiculous hours, here is a solution.  You can use PSI event handler to track the changes and cancel the change for whatever reason.  Here is how to do it.

Step 1: create a project server event handler to validate user input.

Timesheet Updating event occurs when a user tries to save timesheet but before it is actually saved.  You can use this event to validate user input.  If the timesheet is valid, you will let timesheet being saved.  Otherwise you can cancel the process and abort saving. 

You can use Timesheet web service to find the hours saved before.  And you can find what user changed in the TimesheetPreUpdateArgs class, which is stored in DsDelta dataset.  DsDelta dataset would only store the changed hours not all hours.  You need to merge the two dataset to get “real” updating dataset.

If you find the hours are not valid, you can set the TimesheetPreUpdateEventArgs.Cancel=True and TimesheetPreUpdateEventArgs.CancelReason=”Timesheet cancel reson”.  Then project server will stop processing timesheet updating and return to timesheet page.  Project server will also save an error message to event log.

However, when you return to timesheet page, the project server does not show any message for cancelling timesheet.  You will need the second step to show the actual reason.

Here is some sample code that it can check if actual hours in one day is more than 24 hours.  If so, it will cancel the event.

public override void OnUpdating(PSContextInfo contextInfo, TimesheetPreUpdateEventArgs e)

{

        if (e.DsDelta == null)

        {

            return;

        }

 

    TimesheetSvc.TimeSheet ts = new TimesheetSvc.TimeSheet();

    ts.Url = _EPMSite + _TimesheetSvc;

    ts.Credentials = CredentialCache.DefaultCredentials;

    TimesheetSvc.TimesheetDataSet tsDs = ts.ReadTimesheet(e.TsUID);

 

    TimesheetSvc.TimesheetDataSet.ActualsDataTable tblActuals = MergeActuals(tsDs.Actuals, e.DsDelta.Actuals);

 

    Dictionary<DateTime, Decimal> TblTotal = AccumulateActuals(tblActuals);

 

    string Msg = ValidateActuals(TblTotal);

    if (Msg != "")

    {

        e.CancelEvent(Msg);

    }

}

 

Step 2: create a custom web part to show cancel reason.

 

Since the timesheet page cannot show cancel reason from event handler, you may need to create a custom web part to show the reason.  You can just create a very simple web part which contains a label control.  The web part will read event log and display if it found any recent error log.

Many organizations have their own definitions of time.  Project Server standard time dimension may not fit their needs.  Don't worry, we can extend the cube by adding more time hierarchies. 

 

In Analysis Service 2005, one dimension could have multiply hierarchies.  Now we don't need to add time dimension but just add another time hierarchy.  If you want to find information about adding time dimension in project server 2000, you may check another post: http://netsleeper.spaces.live.com/blog/cns!2D6B305EBBD50AA5!127.entry

 

Another good thing in project server 2005 is that we can use event and managed code.  We can write code on OnCubeProcessed event.  This event occurs after project server processed OLAP cube.  Since we are creating time hierarchy, we need to wait project server finishing creating time dimension.

 

Here, I am using Analysis Management Object API for Analysis Service 2005, which is similiar but a little different from old way.  The following sample code is to create another time hierarchy that only have year, week and day.  So that week will not split between months.

 

        public override void OnCubeProcessed(PSContextInfo contextInfo, CubeAdminPostCubeProcessEventArgs e)
        {
           

Project Server 2007 can work with SQLServer 2000 and SQLServer 2005.  To setup OLAP cube for project server 2007, here is some references and some tips:

 

1. For Analysis Service 2000

You can just follow the microsoft MSDN document: http://technet2.microsoft.com/Office/en-us/library/5d90076f-bbcc-48c1-a569-bd236862d47c1033.mspx?mfr=true

 

2. For Analysis Service 2005

Here is the microsoft MSDN document:

http://technet2.microsoft.com/Office/en-us/library/86910044-701c-4b02-89d7-e094e9cb7dcd1033.mspx?mfr=true

 

It is working if you install analysis service on the same computer of project server.  However, it does not work if you separate those two, which is the most case.

 

You will get the following error:

Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to delete the Olap database: PWA_AnalysisServices. Error: Your permissions on the server computer do not allow you to administer this Analysis server.

 

The reason is because the SSP account does not have permission to manage analysis service remotely by using DSO.  To enable remote administer analysis service through DSO, you will need to create shared folder for analysis service: MSOLAPRepository$.  This is just for back compatible to DSO.  Analysis service 2000 will create it during the installation.  However, since analysis service 2005 no longer need it.  It will not create the shared folder.  Then you have to do it yourself.  The folder will only be used for remote administer.  So if you install analysis service on the same box of project server, you don't need it.

 

Use this two steps with the MSDN document, you can setup OLAP cube.

Create the shared folder

1.     In Windows Explorer, browse to the folder containing the Analysis Services installation. By default, it is located at:
C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP

2.     In this folder, create a new folder and name it DSO9.

3.     Right-click the DSO9 folder, and choose Sharing and Security.

4.     On the Sharing and Security page, in the Sharing tab, select Share this folder. In the Share Name box, type MSOLAPRepository$ as the share name for the folder.

5.     On the Sharing tab, in the Group or user names list, click Add. On the Select User, Computers, or Groups page, add the SQLServer2005MSOLAPUser$<Servername>$MSSQLServer account from the local computer. Click OK.

6.     In the Group or user names list, select the SQLServer2005MSOLAPUser$<Servername>$MSSQLServer account. In the Permissions for SQLServer2005MSOLAPUser$<Servername>$MSSQLServer box, select Allow next to Full Control. Click OK.

7.     On the Security tab, in the Group or user names list, select SQLServer2005MSOLAPUser$SERVERNAME$MSSQLSERVER. Select the Allow check box next to the Full Control item in the Permissions list, and then click OK.

8.     On the New Folder Properties page, click OK.

Configure Analysis Services For DSO

1.     In SQL Server 2005, start SQL Server Management Studio. On the Connect to Server window, select Analysis Services, and then click Connect.

2.     In SQL Server Management Studio, in the Object Explorer pane, right-click the Analysis Services name, and then choose Properties.

3.     On the Analysis Services Properties page, in the Select a page section select General. Select Show Advanced (All) Properties.

4.     Select DSO\RepositoryConnectionString from the Name list.
a.   Select the corresponding value for the string in the Value column, and then click the box that appears to the right of the value to display the Connection Manager page.
b.   On the Connection Manager page, in the Provider list, select Native OLE DB\SQL Native Client.
c.   In the Server Name list, select the server on which the repository database is located.
d.   In the Logon to the server field, enter the account information to log onto the server.
e.   In the Connect to database section, select Select or enter a database name and enter the name of the repository file.
f.    Click OK.

5.     Select DSO\RemoteRepositoryConnectionString from the Name list.
a.   Select the corresponding value for the string in the Value column, and then click the box that appears to the right of the value to display the Connection Manager page.
b.   On the Connection Manager page, in the Provider list, select Native OLE DB\SQL Native Client.
c.   In the Server Name list, select the server on which the repository database is located.
d.   In the Logon to the server field, enter the account information to log onto the server.
e.   In the Connect to database section, select Select or enter a database name and enter the name of the repository file.
f.    Click OK.

6.     Select DSO\LocksDirectory from the Name list, enter the local path of the shared folder.  For example: E:\MSSQL.3\OLAP\DSO9\

7.     Select DSO\RemoteLocksDirectory from the Name list, enter the UNC name of the shared folder.  For example: \\HACSSDB028\MSOLAPRepository$\

8.     On the Analysis Server Properties page, click OK.

NOTES:

1.     When you configure analysis service 2005, MSDN gaves you two choices: creating Access MDB file or SQLServer 2005 database.  You can do either one of them.  However, you cannot migrate from MDB to SQLServer 2005.

2.     To view project server OLAP view in PWA, the client computer (with Windows XP.  Vista already has those component) has to install the following components:

• Microsoft Core XML Services (MSXML) 6.0
• Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider

You can find them in SQLServer 2005 feature pack: http://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en

Sometimes, you need to create a very complex formula for indicator.  It's very hard to understand and maintain.   

For example, client wants to show schedule indicator green when duration variance within 10% of baseline duration, 11%~20% for yellow and above 20% shows red. 

 

Sample formula:

SWITCH([Finish Variance]=0,"On Target",[Finish Variance]<0,CSTR(CINT([Finish Variance]*(-100)/[Baseline Duration]))+"% Ahead Of Schedule", true,CSTR(CINT([Finish Variance]*100/[Baseline Duration]))+"% Over Schedule")

Then when you set graphic indicator, use "Is Less Than" or "Is Greater Than".  You can set as

Is Less Than     11      Green

Is Less Than     21      Yellow

Any Value                  Red

 

Project Professional is smarter enough to compare the percent as number.  You can event put percent in the middle of the data, such as "Task 10% Behind Schedule".

 

I have experienced Project Server 2007 Active Directory Synchronization failure on client.  As the result, Active Directory cannot be synchronized and even worse, the user/resource edit page shows error when you tried to edit a synchronized user/resource.  It may potentially create corrupted assignments in project plan.

 

In the event log on project server, there are errors in application category.

 

 

Error Message:

 

Standard Information:PSI Entry Point:

Project User: IHESS\sdai

Correlation Id: 837bfd5f-9502-4e09-a193-2af20c8392b0

PWA Site URL: http://hacssia021/PWA

SSP Name: SharedServices_MOPS

PSError: Success (0)

A general exception occurred during communication with Active Directory. Context: SyncGroup. Additional Information: An exception occurred while syncing a project server group with active directory. GroupName: '%s'. PWA Group Guid: '%s' Exception: '%s'.. Exception Info: EPIS Team Members

 

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

 

 

 

 

There also are errors in system category:

 

 

 

 

Error Message:

 

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID

{61738644-F196-11D0-9953-00C04FD919C1}

 to the user IHESS\SRV_PROJSVR_DB SID (S-1-5-21-789336058-2052111302-839522115-106752).  This security permission can be modified using the Component Services administrative tool.

 

 

 

From the error message, I decided to change the DCOM configuration first.  By searching the registry, I found that (S-1-5-21-789336058-2052111302-839522115-106752) is the ID of IIS WAMREG Admin Service component.

 

Open the Component Services on project server and select “Component Services” – “DCOM Config”.

 

 

Right click on component and select “Property”.  Then select “Security”.

 

 

Select “Edit” button in “Launch and Activation Permissions”, then add the SharePoint farm service user in and give it “Local Launch” and “Local Activation” permissions.

 

 

 

After done, I tried to synchronize the users again.  However, I still got errors.  When I check the event log, the errors are different.

 

 

 

 

Error Message:

 

Standard Information:PSI Entry Point:

Project User: IHESS\sdai

Correlation Id: 837bfd5f-9502-4e09-a193-2af20c8392b0

PWA Site URL: http://hacssia021/PWA

SSP Name: SharedServices_MOPS

PSError: Success (0)

Changes were detected for a resource during Project Server Active Directory Synchronization. However, the changes could not be applied because the resource could not be checked out. The checkout failed because the resource is checked out to another user. Resource GUID: 67581218-21cd-4f5e-9ee0-6ad4f0e25799

 

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

 

 

 

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

 

It shows some resource has been checked out.  But in force-in resource page, I did not find any check-out resources.  Actually, those resources are checked-out by AD synchronization process. 

 

I used “SQL Server Management Studio” to open the Project Server 2007 published database. 

 

Run the script to check back in the resources

 

UPDATE MSP_RESOURCES
SET RES_CHECKOUTBY = NULL
WHERE RES_UID in (
SELECT
RES_UID
FROM MSP_Resources
WHERE RES_CHECKOUTBY = '00000000-0000-0000-0000-000000000000')

 

 

If you have corrupted assignments, you may use the script to find out:

 

SELECT MSP_PROJECTS.PROJ_NAME,
MSP_ASSIGNMENTS_SAVED.TASK_NAME,
MSP_RESOURCES.RES_NAME,
MSP_ASSIGNMENTS_SAVED.ASSN_UID,
MSP_ASSIGNMENTS_SAVED.PROJ_UID,
MSP_ASSIGNMENTS_SAVED.TASK_UID,
MSP_ASSIGNMENTS_SAVED.RES_UID
FROM MSP_ASSIGNMENTS_SAVED INNER JOIN
MSP_PROJECTS ON MSP_ASSIGNMENTS_SAVED.PROJ_UID =
MSP_PROJECTS.PROJ_UID INNER JOIN
MSP_RESOURCES ON MSP_ASSIGNMENTS_SAVED.RES_UID =
MSP_RESOURCES.RES_UID
WHERE (NOT EXISTS
(SELECT TASK_UID
FROM MSP_TASKS_SAVED
WHERE (TASK_UID = MSP_ASSIGNMENTS_SAVED.TASK_UID)))

 

 

 

Reference:

 

http://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=288068&SiteID=235