Project Server Blog

This is an issue I have seen now with two different Project Server 2010 clients;

Symptoms:

• Certain Projects cannot be opened in either PWA or MS Project.
• Oddly the same projects do not appear to exist in the Draft database, but DO appear in the Published database.
• Projects cannot be deleted from Server Settings after selecting the Published version and hitting delete.

Errors:

Delete Queue job fails and review of the ULS log shows the following:

10/14/2010 11:30:40.33        Microsoft.Office.Project.Server (0x14A8)        0x172C        Project Server        Server-side Project Operations        7gvo        Critical        Standard Information:PSI Entry Point: Project User: i:0#.w|domain\administrator Correlation Id: e63eca62-f9fb-40d6-af52-db59bff5b7cd PWA Site URL: http://project2010:82/PWA SSP Name: Project Server Service PSError: ProjectDeleteFailure (23006) The request to delete a project encountered a problem – the relevant job failed in the Queue. Project UID: 01c7c325-1704-4269-8316-ab1b0bc85d07. Sub-job type where failure occurred: Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.
AdjustTimeSheetForDeletedProjectMessage. Sub-job ID where failure occurred: . Specific stage in the sub-job where failure occurred: . Does this failure block the correlated job group: Undefined. See the ‘Manage Queue’ page in PWA for more details.

Additionally enabling Verbose ULS logging for all Project Server events turns up this one:

10/14/2010 11:50:05.33        Microsoft.Office.Project.Server (0x14A8)        0×1434        Project Server Timesheet myzd Verbose PWA:http://project2010:82/PWA, ServiceApp:Project Server Service, User:i:0#.w|domain\administrator, PSI: Start ApproveProjectTimesheetLines(approvedTimesheetLines=’ ‘, rejectedTimesheetLines=… [GUID’s removed] … 10/14/2010 11:50:05.33        Microsoft.Office.Project.Server (0x14A8)        0×1434        Project Server Timesheet myzl Verbose Error is: GeneralItemDoesNotExist. Details: . Standard Information: PSI Entry Point: Project User: i:0#.w|domain\administrator Correlation Id: e63eca62-f9fb-40d6-af52-db59bff5b7cd PWA Site URL: http://project2010:82/PWA SSP Name: Project Server Service PSError: GeneralItemDoesNotExist (10000)

Resolution:

It took the enabling of Verbose logging to figure out the cause, the give away was the timesheet line delete job, it seems that for some reason the TS Line GUID’s above were causing the failure.

So the solution was relatively simple, identify the Timesheets (and respective lines) and delete them manually.

To that end I ended up creating a couple of SQL scripts;

Script 1:

Identify all projects which exist in the Published Database but not in Draft.

– Query to identify projects in Publish but not in Draft

USE ProjectServer_Published
SELECT PubProj.PROJ_UID, PubProj.PROJ_NAME AS Published, DrafProj.PROJ_NAME AS Draft
    FROM ProjectServer_Published.dbo.MSP_PROJECTS AS PubProj
    LEFT OUTER JOIN ProjectServer_Draft.dbo.MSP_PROJECTS AS DrafProj ON PubProj.PROJ_UID = DrafProj.PROJ_UID
    WHERE DrafProj.PROJ_NAME IS NULL

Script 2:

Identify all Timesheets with lines against projects not in the Draft database.

– Query to identify timesheets with lines against projects not in draft

USE ProjectServer_Published
SELECT MSP_TIMESHEETS.TS_UID, MSP_TIMESHEETS.CREATED_DATE, MSP_TIMESHEETS.TS_CACHED_RES_NAME, MSP_TIMESHEETS.TS_COMMENTS
    ,RepTSP.PeriodName, RepTSP.StartDate, RepTSP.EndDate
    ,MSP_TIMESHEET_LINES.PROJ_UID, MSP_TIMESHEET_LINES.TS_LINE_CACHED_PROJ_NAME
    FROM MSP_TIMESHEETS
    INNER JOIN MSP_TIMESHEET_LINES ON MSP_TIMESHEETS.TS_UID = MSP_TIMESHEET_LINES.TS_UID
    INNER JOIN ProjectServer_Reporting.dbo.MSP_TimesheetPeriod AS RepTSP ON MSP_TIMESHEETS.WPRD_UID = RepTSP.PeriodUID
    WHERE
    MSP_TIMESHEET_LINES.PROJ_UID IN (SELECT PubProj.PROJ_UID
        FROM ProjectServer_Published.dbo.MSP_PROJECTS AS PubProj
        LEFT OUTER JOIN ProjectServer_Draft.dbo.MSP_PROJECTS AS DrafProj ON PubProj.PROJ_UID = DrafProj.PROJ_UID
        WHERE DrafProj.PROJ_NAME IS NULL)
    ORDER BY CREATED_DATE

Note: For both script’s replace your ProjectServer_* name, and then run this script against your PUBLISHED database.

Running the 1st script is not mandatory, although it can be useful as in my case it identified a few more projects with this issue. However: Ignore the ‘eGlobal …” project(s) and do not attempt by any means to remove them, you have been warned!

The second script will identify line by line timesheets that need to be edited / deleted before you can delete from Published the problematic project. This needs to be done in the usual ways, probably by the Resource themselves unless using delegation or something similar.

Cause:

Good question! I was not able to identify the root cause of this one, although I did notice it occurring to me during a training session that I was hosting, in fact the issue occurred on the test project I was working on while testing the creation and use of Timesheet approval rules and of course submitting / approving / rejecting test timesheets. However despite that clue I was never able to reliably reproduce the issue, please leave a comment if you have any other ideas!

Hope that helps someone out there!

Brian posted this link to a new document posted on the Microsoft Project Hompage containing a slide deck detailing the limitations of the new 2010 Online Editing feature, I thought that it was definitely worth a mention here as this is one feature that I have had a lot of experience with in 2010.

Here’s a snapshot of the contents:

(http://www.microsoft.com/project/en/us/project-server-2010-editions.aspx)

There are a couple of things in there that I had not yet noticed, although the Cost resource one I learnt the hard way trying to save Budget Cost resources in my EPT template!

However a couple of the limitations that have most affected me and my clients were actually not mentioned, they include:

• Predecessor / successor links to Summary Tasks – not supported at all.
• Blank lines – IE lines created in MS Project to space out your schedule.

One of the biggest areas of advancement in Project Server 2010 is reporting, with Project now utilising; Excel Services, SQL Analysis Services, SQL Reporting Services and PerformancePoint the options are almost endless.

However most people I encounter look at that list and say; “What on earth is PerformancePoint?”, so lately I had another opportunity to flex my PPS skills and thought I might share my experience to give you a taste of how powerful yet simple to use this new thing can be.

Creating a Dynamic Resource Utilisation Graph using PerformancePoint

I’m going to keep this very simple, as I find PPS to be very often quite mind-boggling, so to give you a taste of it what I’ll describe here is how to create a simple equivalent to the old typical Data Analysis Resource Utilisation view.

So before you begin, make sure you have your Cube setup and built, the PerformancePoint service application setup and running in your SharePoint installation and some data to play with.

First step you need to get to the PerformancePoint Dashboard Designer, this is a nifty little web application (independent from the Office Web Apps) that is automatically installed and made available when you provision a PWA site.

To find it, open up the Business Intelligence site, and from there the quickest way to get to it is by hovering over any of the landing page images (Create Dashboards, etc) and selecting the ‘Start using PerformancePoint Services’ link.

You then have the icon to run Dashboard Designer:

This should download and install the designer for you, and assuming that you have all the correct permissions (and that PWA is in your Internet Explorer trusted sites), you will end up with a mostly empty designer window looking something like the image below.

Now you’re ready to start creating those reports, first step setup your Data Connections by selecting the Create tab on the ribbon and selecting Data Source:

Select Analysis Services as the connection type, and populate the connection details and properties as required:

For Analysis Services datasources you should populate the Time tab to ensure that your Time dimensions are correct, something like this;

Finally when you are done, select save from above the ribbon to continue.

Note: This is something you will get used to with the Dashboard Designer, everything is automatically saved to the PWA BI site in their respective locations (dataconnections, reports or dashboards), with just one exception being the ‘Workspace’ which is effectively your configuration of Dashboard Designer (something I don’t usually bother saving).

Now lets move on and create our report, select PerformancePoint Content and then from the Create ribbon lets select ‘Analytic Chart’;

Then select your datasource just created and hit finish, then your report will open in the designer;

Give it a name and then lets start adding content:

• First add some measures to the Series; Work and Actual Work.

• Now add your Resource List dimension to the Bottom Axis.

It should look something like this;

If you want to expand members of a dimension select the chevron (Down arrow next to the X), and select the members in the dialog.

Here for dynamic dimensions like the Resource list you are better off right-clicking and selecting one of the Autoselect Members, such as All Children, like so;

Which now looks like:

Finally we need our Time dimension, add it to the Bottom Axis from the right list and use the chevron to select the desired time periods (I’m selecting months by name here, however you can use something called Named Set’s to do this dynamically for you – another blog article maybe). Finally I think it’s best to move the Resource List to the top of the series Series list and apply some filters to filter out the blanks, to give us something like:

Don’t forget the Edit tab on the Ribbon which has a number of settings that you’ll find handy getting your report right.

Almost there now..

Okay so now we’re ready to save and see this thing for real, so hit the save button, and lets minimise the designer and go back to our BI site in Internet Explorer.

If you open the default ‘PerformancePoint Content’ link, you should see your new report listed, select the drop-down to Display the report:

Final product:

Now this report is ready to add to any where in SharePoint using the PerformancePoint viewer web part, and the best thing is that all of the dynamic functions will be available to all users, so if someone wants to view this report in terms of Cost / Actual Cost, it’s just a few clicks away:

Or maybe you want to see the breakdown of a particular person’s activities using the Decomposition Tree?

I’d say this beats those old Data Analysis views!

That’s it for this how-to, hopefully this scratching of the surface has shown you some of the potential of PPS, keep experimenting and you’ll see very quickly how easy it is to replace those old Data Analysis views that are so 1990!

In the future I might come back and write a Part II to this one on creating your first KPI Scorecard in PPS, stay tuned..

This is a useful solution; with it you can update on a PDP an Excel spreadsheet saved in project’s workspace document library and then potentially update custom fields from the results.

However it is far from a complete solution to download and deploy.

First issue – Admin rights required to use Web-Part:

Installed as per instructions (build / package / run script), then when adding the web part this familiar error occurred:

“An unexpected error has occurred.”

A quick look at the ULS shows more details:

The source was not found, but some or all event logs could not be searched. Inaccessible logs: Security.

Now that’s interesting as I had this exact same issue a few months back working with a 2007 PSI project, it turns out the answer is rather simple; the web service is attempting to write to the event log using “EventLog.WriteEntry” method calls, however in Windows 2008 and above this is restricted to Administrators only. So as the PWA users are not local admins the error was guaranteed.

A quick solution is to comment out each line in the code beginning with “EventLog.” of course that will disable the logging, so you would be better off doing that after you get everything else working. The file in the solution that you are after is CostAndBenefiXlsWebPart.cs in the WebPart.CostBenefit project and you’ll find about half a dozen envent log entries to comment out.

Second issue – The default site template location:

While your in that file commenting out lines, you’ll most likely notice this;

private string pdpSitePath = @”http://project.contoso.com/PWA/MajorPWSTemplateSite;”

Although the solution will work without changing this, as that is only used when no workspace site exists (like when in server settings – project detail pages), updating that to point to your http://server/pwa/template workspace site template will fix any file not found issues when setting up your PDPs.

Third issue – Location of the Excel document used:

If you’ve gotten this far then like me you might be seeing something like this:

No item exists at http://servername/_layouts/xlviewer.aspx?list=b73da3b8-b48e-400c-8fe0-9b6a48442518&id=1&DefaultItemOpen=1&Edit=1.  It may have been deleted or renamed by another user.

A little digging turned up the cause of this one to be the embedded ID in that URL “&id=1”, which means that it is trying to open the document with ID 1 in that particular list. This again is hard coded in the same .cs file as above (line 191), so you can either make certain that in your template above the first file saved to the Project Documents folder is your xls sheet, or modify the code again.

All done.

So now we should have a working webpart referencing our Excel sheet saved in the project workspace site, there are some obvious limitations, not least of all the inability to use any other spreadsheet in the webpart. But I guess that’s why they call it a “solution starter”.

Update 23/10/2010: Most of the points above have been fixed in the more recent updates to the solution starter package, make sure you update your code from Codeplex: http://code.msdn.microsoft.com/P2010SolutionStarter

Came across this issue when building my cube using SQL AS 2005 with 2010 for the first time;

[7/23/2010 9:28 AM] Failed to build the OLAP cubes. Error: Failed to process the Analysis Services database PS2010TestCube1 on the PRJDBCLUSTER server. Error: OLE DB error: OLE DB or ODBC error: Class not registered.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘Project Reporting data source’, Name of ‘Project Reporting data source’.

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘PS2010TestCube1′, Name of ‘Assignment Owner’ was being processed.

Server: The operation has been cancelled.

Fortunately the solution was not too hard to come by; it seems that you need to install the SQL 2008 Native client on the SQL 2005 server in order to fix this one.

Hope that helps someone!

This is a new error that has been bugging me for a few weeks now:

The error message "Project Server encountered a problem with this error code:9000" occurs when attempting to save a project in Project Professional 2007 client:

Additionally you may receive a blank "Job failed" window when trying to check-in a project:

This problem is caused by an Internet Explorer login session to Project Web Access expiring and as a result the project client is forced to re-login, however this re-login only seems to be supported in Project Professional 2010 and not 2007 client running in backwards compatibility mode.

This only applies to Project Servers / SharePoint configured to use Claims authentication, or when Project Professional 2007 is using Forms authentication to logon to PWA. (I assume Project Pro can re-authenticate easily using NTLM)

The best workaround to this is to not use the connect / disconnect and work offline options in Project Professional as this does not re-log you in.

Additionally perhaps avoid opening PWA in a separate Internet Explorer window so as to not logout inadvertently.

Performance problems also exacerbate this issue.

Hope this helps someone!

UPDATE 11/09/2010:

Since posting the above I have run into this error on some other occasions not related to Claims based authentication. Specifically I found this error would occur whenever attempting to save one particular MPP file into a server. In that particular case reviewing the schedule and specifically the resource sheet turned up the cause:

Clearly this resource is corrupted (this is an English install) in some way, what this indicates is that the error 9000 is a generic ‘error of last resort’ it would seem.

Been very quiet on this blog the last couple of weeks, largely due to the fact that I’ve just kicked off my second PS2010 project (this one for one of the big mobile network management companies), while at the same time being in the thick of the first round of system testing for my first 2010 project!

I’ve got a handful of things I wanted to blog about but let’s start with some issues that I have come across, some of these I plan to log with MS, however none were really bad enough to cause any major concern.

1. Workspace Template Risks List ‘Exposure’ Unexpected Error

When you create a blank template site (not linked to a project) an ‘Unexpected Error’ will occur when you try to edit the field behind the default ‘Exposure’ field. This doesn’t happen when the site is linked to a project through the normal project creation.

Fortunately the standard calculation (Consequence * Likelihood) is all that I usually use anyway so this is a non-issue.

2. No Spaces Between Tasks when using PWA online editing

This one is causing some frustration, basically the Project Web App online editing does not support blank lines between tasks at all. Rather annoying as just about everyone I know uses spaces to make large projects more readable.

3. BCM Project Professional 2007 Crash when spaces exist between tasks

This one is related to #2 above, if you save a project template containing spaces between tasks (blank lines) and associate it with an Enterprise Project Type, then it seems when in BCM mode attempting to open a project created by that type will cause problems for Project 2007 client, but not 2010.

I didn’t fully test this one out to find if there are any other factors, but after removing the blank lines from my templates both this and #2 were fixed.

5. Project Client version restriction ignores CU build numbers

This one is more of an annoyance, it seems that the new feature in server settings to restrict the version number of connecting Project Pro clients only recognises major build numbers, ie Service Packs. Meaning that trying to force all 2007 clients in BCM mode to run at least Feb2010 CU will be just as troublesome as it always was.

5. Project Template used by EPT with Budget Resources assigned causes failure to create project

This is probably the worst issue I have found, I like the Budget Cost feature but it doesn’t seem to be the most widely used feature if an issue like this can slip through testing. I’m probably going to log this one with MS, but for this particular customer we simply modified our procedures to work around it. Here is my full issue description:

Issue:

Attempting to save an Enterprise Template and associate it with an Enterprise Project Type when the template includes Budget Resources assigned to the Project Summary Task causes a failure in the project creation queue job(s).

Environment:

Project Server 2010 RTM

Project Professional 2007 or 2010

Reproduction Steps:

1. Create a new blank project in Project Professional
2. From Tools – Options menu select ‘Show Project Summary Task’ (or equiv ribbon in 2010)
3. Select Resource Sheet view, then add a single Cost Resource named ‘Test’
4. Double click the resource to edit properties, and select ‘Budget’ to set the resource as a Budget Cost resource
5. Assign the Test resource to the Project Summary Task
6. Save As  to save as an Enterprise Template named ‘Test Template’
7. Open PWA, go to Server Settings – Enterprise Project Types
8. Create a new Project Type as follows;
1. Name: Test type
2. Workflow: No Workflow
3. New Project Page: Proposal Details (or anything else)
4. Project Plan Template:  Test Template
5. All other values default
9. Select Project Centre, New Project – “Test type”
10. Enter the details and Save

Result:

Queue job fails with the following error:

Datasets:

ProjectDataSet

Table Assignment

Row: ASSN_UID=’18a3ec42-a1ad-415b-9e38-5b822f38750e’ PROJ_UID=’09c32ca4-5d86-4b0b-91f0-21e8abe97e3c’

Error TaskNotFound (7021) – column

General

Queue:

GeneralQueueJobFailed (26000) – ProjectCreate.ProjectAddToMessage. Details: id=’26000′ name=’GeneralQueueJobFailed’ uid=’b6640d9d-36ab-496d-b210-7b765ea852ea’ JobUID=’abf46cad-10b1-4e7c-94b8-6beeb76b30c5′ ComputerName=’AU-GLB-MSP04′ GroupType=’ProjectCreate’ MessageType=’ProjectAddToMessage’ MessageId=’1′ Stage=”. For more details, check the ULS logs on machine AU-GLB-MSP04 for entries with JobUID abf46cad-10b1-4e7c-94b8-6beeb76b30c5.

With SharePoint 2010 adding a good number of new databases to the default configuration one thing that is sorely missing is the ability to easily name those databases.

Is it just me who has to conform to strict database naming conventions in most installations of Project Server?

I’ve just been working on what I think is a pretty typical Project Server 2010 installation and found that in-order to simply prefix my databases names with something rather than let them be automatically named with a GUID I faced the following:

• Central Admin and the farm configuration is the same as 2007, the PSCONFIG command line will let you name both of those databases.
• Session State Service Application – the first hurdle, this one requires a PowerShell command to give the database a name, e.g.:

$serviceApp = New-SPStateServiceApplication -Name "State Service"

New-SPStateServiceDatabase -Name "MyPrefix_State_Service" -ServiceApplication $serviceApp

New-SPStateServiceApplicationProxy -Name "State Service" -ServiceApplication $serviceApp –DefaultProxyGroup

• Secure Store Application – Central Admin fortunately will let you name the database through the usual creation process.
• Search Service Application – Next hurdle and this one much bigger, three databases are required now for the Search service; Administration, Crawl and Property databases are created, this blog post has a nice PowerShell script to create the service with named databases but with 50 odd lines for the script I’m not going to repeat it here. Fortunately if you’re happy with not naming just one of those three databases, it is actually possible to create new Crawl and Property databases with specified names after creating the three using Central Admin.
• PerformancePoint Service – Well for this one I have not found a way to name the database?! I need to revisit this as there must be a way, but looking at the PowerShell command line options and searching TechNet I couldn’t find one!
• User Profile Service, Excel Services and WSS Usage are all well behaved and through Central Admin you can name the databases.

So it’s about time to get that installation PowerShell script done, as unless your happy with the default wizard configuration then this setup is getting quite long “just” to get Project Server 2010 installed.

Found this one out the hard way this week; it seems that currently the only supported version of SSRS for the SharePoint 2010 SRS Add-in is SQL 2008 R2. At least one forum poster from MSFT has confirmed this.

Annoying as it can be surprisingly hard enough to convince customers to install Windows 2008 let alone upgrade their SQL servers! Ah, well at least SRS can be separated from the SQL DB servers making it a bit easier.

Update 12/07: This has now been addressed by Microsoft in the SQL 2008 SP1 CU8 release which adds support to reporting services 2008 to work with the R2 add-in.

As I’ve been deep in the world of Project Server 2010 configuration this week, I thought I’d share a quick round up of the cool things I have noticed:

• Ability to edit custom fields in PWA without Pro client running.
• Project and Task Custom Fields update when project edited in PWA (Except for the first time usually).
• SharePoint 2010 Workflows have been updated, they are quite nice; for example the approval workflow is now able to do multi-level serial / parallel approvals, etc.
• Saving new Workspace templates is so so easy now! Just save site as a template, then go to your project type and select the new template!
• PDP’s rock – ‘enuf said.
• BI Centre is great, but so much more time will be needed (at least at first) in getting the reports you need. The flip side is that the reason why so much more time is needed is because you can get so much more out of them!

More to come in the coming days / weeks as it looks like client number 2 is going to kick of a 2010 implementation in the next week or so. Exciting times..