Wednesday, February 18, 2009

Timesheet Classifications and Timesheet Import

A new feature in Microsoft Office Project Server 2007 from 2003 is the Timesheet feature. Although, this a really cool feature, the design falls short in terms of actually getting the time from a timesheet into a task and then getting those tasks approved by Project or Assignment Managers. I won’t get into the whole, import Timesheet or how to programmatically automate the import and submit method, but if that’s something you want to do, there’s a great starter solution available on CodePlex by Christophe Fiessinger:

Timesheet Classifications are something that a Project Server Administrator can customize/add to their hearts content. Then, when a user goes to their PWA Timesheet and Adds a Task, they have the option to select a different classification other than Standard. So, why would you want to do this? Well suppose you want to duplicate timesheet lines for business purposes or accounting reasons. You would setup the specific classifications, then add tasks with the same name to your timesheet, but select a different classification each time. These duplicate tasks names with different classification names will become the unique identifier for a timesheet line.

The intention for multiple classifications can be used for business analysis of time related to the task that was not productive as far as working the task. So you may have spent 2 hours traveling to perform a task - and you may bill the customer, but this wasn't something directly related to performing the task - and in this scenario was not included in the planned time to perform the task. There are probably better ways to handle this scenario, but this is just an example.

Sounds great huh? Wrong! Here’s the caveat. When you use the Import Timesheets through the My Task section in PWA, only Standard timesheet classifications will appear, and only those tasks will be allowed to be imported. This is by design and is not a functional bug.
So, if you’ve gone down the rabbit hole or customization against the timesheet utilizing the web services via PSI, or if you’ve decided to create a custom timesheet interface that overlays Microsoft Office Project Server 2007 Timesheet functionality and you are utilizing classifications for various things, like unverified timesheet line items or to allow duplicate tasks for different departments, you have probably seen this error.

Event Type: Error
Event Source: Office SharePoint Server
Event Category: Project Server Timesheet
Event ID: 7851
Date: 2/16/2009
Time: 1:10:43 PM
User: N/A
Standard Information:PSI Entry Point: Statusing.ImportTimesheet
Correlation Id: ead69630-b9b5-43f2-b7d7-81eade40b8ff
SSP Name: SharedServices1
PSError: TimesheetIncorrectPeriod (3208)
Timesheet period used '(null)' is invalid.

This is caused because programmatically, the ImportTimesheet method for the Statusing web services doesn’t know how to filter for only Standard classifications. So, what’s happening is that the system is trying to do something that it can’t do and therefore throws the error causing the time for that task to fail and come up missing. Change those lines back to Standard and try concatenating the tasks programmatically to create unique tasks and importing should work.

401.1 when you browse a Web site that uses Integrated Authentication and is hosted on IIS 5.1 or IIS 6

This is a problem that also happens when you programatically try to manupulate data utilizing web services, such as PSI. The error isn't always apparent right away or even with minimal useage on the system. But, if your farm gets hammered, it'll show up pretty quick. This will also cause things like Profile Synchronization, Indexing and Crawling to fail.

You may see an error that like these:
  • HTTP 401.1 - Unauthorized: Logon Failed
  • Event id 2436: Access is denied. Check that the Default Content Access Account has access to this content, or add a crawl rule to crawl this content. (0x80041205)
  • Access is denied. Verify that either the Default Content Access Account has access to this repository, or add a crawl rule to crawl this repository. If the repository being crawled is a SharePoint repository, verify that the account you are using has "Full Read" permissions on the SharePoint Web Application being crawled. (The item was deleted because it was either not found or the crawler was denied access to it.)
  • Error while trying to run project: Unable to start debugging on the web server. You do not have permissions to debug the server. Verify that you are a member of the 'Debugger Users' group on the server.

This issue occurs if you install Microsoft Windows XP Service Pack 2 (SP2) or Microsoft Windows Server 2003 Service Pack 1 (SP1). Windows XP SP2 and Windows Server 2003 SP1 include a loopback check security feature that is designed to help prevent reflection attacks on your computer. Therefore, authentication fails if the FQDN or the custom host header that you use does not match the local computer name.

The workaround for this issue is to disable the the loopback check or specifiy the host names. The following describes how these are accomplished, keeping in mind that Method 1 seems to be the best method with the best results:

Method 1: Disable the loopback check Follow these steps:

  • Click Start, click Run, type regedit, and then click OK.
  • In Registry Editor, locate and then click the following registry key:


  • Right-click Lsa, point to New, and then click DWORD Value
  • Type DisableLoopbackCheck, and then press ENTER
  • Right-click DisableLoopbackCheck, and then click Modify
  • In the Value data box, type 1, and then click OK
  • Quit Registry Editor, and then restart your computer

Method 2: Specify host names

To specify the host names that are mapped to the loopback address and can connect to Web sites on your computer, follow these steps:

  • Click Start, click Run, type regedit, and then click OK.
  • In Registry Editor, locate and then click the following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0Right-click MSV1_0, point to New, and then click Multi-String Value

  • Type BackConnectionHostNames, and then press ENTER
  • Right-click BackConnectionHostNames, and then click Modify
    In the Value data box, type the host name or the host names for the sites that are on the local computer, and then click OK
  • Quit Registry Editor, and then restart the IISAdmin service

More information can be found here:

Configuring the Office Project Server 2007 Cube Building Service

Can't get your cube to build in your shiney new environment? Well, don't dispair, this is actually a common problem! It's a pretty easy fix, but you will need a SQL 2000 disk, to finish this proceedure, so hopefully, you have one laying around. Yes, I said SQL 2000 (x86 or x64), that wasn't a misprint or fat finger! I listed these steps in the order they need to be done. So, sit back, relax...and here we go!

Security Requirements
The Application Server (PSI Server) Queue service will be running using the SSP credentials, entered when setting up the SSP instance. The Analysis Services service, used to build the cubes, should be running under the same account or an account running that would need to have read access to the Reporting database.
If multiple PWA instances are created and their cubes are built on the same Analysis Services machine, the recommendation is for administrators to create a single windows account that will run the Analysis Services service, as follow:

  1. Go to the machine where Analysis Services is installed
  2. Go to the Computer Management window
  3. Right click on ‘My Computer’ and select Manage
  4. Select Services under ‘Services and Applications’
  5. Select the Analysis Services (OLAP) service
  6. Right-click on it and select Properties
  7. Select the Log On tab and enter the credentials that will have read access to the Reporting database of all of the PWA instances
The same account used to run the Analysis Services service will need to have read access to all of the PWA instances that plan to build their cubes on that Analysis Service.

Application Server
This applies to all of the WFEs that are running Microsoft Office Project Server 2007. But, it would be a good idea to do this on all WFEs, so if one of your Microsoft Office Project Server 2007 servers go down, you can easly spin up another one.

The Application Server must have the Analysis Services DSO (Decision Support Object) component installed.
To install the Decision Support Objects for Analysis Services

  1. Run the SQL Server Installation Wizard.
  2. On the End User License Agreement page, read the license agreement and then select the check box to accept the licensing terms and conditions. Click Next.
  3. On the SQL Server Component Update page, setup installs software required for SQL Server 2005. To begin the component update process, click Install. To continue after the update completes, click Finish.
  4. On the Welcome page, click Next.
  5. If you will build the cubes in Analysis Services 2005, this component can be obtained by running the installation setup for Analysis Service, and selecting the Client Component to be installed.

If the cubes will be built on Analysis Services 2005, you need to install the following components:

  1. Microsoft SQL Server Native Client (sqlncli.msi)
  2. Microsoft SQL Server 2005 Management Objects Collection (sqlserver2005_xmo.msi)
  3. Microsoft SQL Server 2005 Backward Compatibility Components (SQLServer2005_BC.msi)
  4. Microsoft SQL Server 2005 Microsoft ADOMD.NET (SQLServer2005_ADOMD.msi)
These components can be downloaded from the Feature Pack for Microsoft SQL Server 2005 – February 2007 release, available here:

Analysis Services
If running Analysis Services 2005, the following setup steps are required:

  1. Add the user account running the Queue service at the Application Server to the OLAP Admin Group
  2. Give administrative access to the OLAP Admin Group to the “\bin” folder
If running Analysis Services 2005 (Service Pack 1 is required), you need to setup a repository for DSO to write to. If you have migrated the Analysis Services 2000 repository to the Analysis Services 2005 server, no additional steps are required.
The instructions below detail how to migrate the repository or create a new one:

  1. On the server with Analysis Services 2005 installed, go to \Program Files\Microsoft SQL Server\MSSQL.2\OLAP
  2. Create a directory called ‘DSO9’
  3. Share it as MSOLAPREPOSITORY$
  4. Give the SQLServer2005MSOLAPUser$MSSQLSERVER administrative access to the share
  5. Copy the file msmdrep.mdb into the DSO9 directory previously created. This file can be found in the Analysis Services 2000, and by copying it to this directory you will be migrating it to Analysis Services 2005. If you don’t have an Analysis Services 2000 server, you should create a new (empty) .mdb file and add it to the DSO9 directory (you should use the name above or edit the section below).
  6. Navigate to the \Program Files\Microsoft SQL Server\MSSQL.x\Config directory
  7. Edit msmdsrv.ini file and replace DSO section with the following (make sure to change the text in red to your specific environment variables):

Data Source=\\<MACHINENAME>\MSOLAPRepository$\msmdrep.mdb;
Persist Security Info=False
Data Source=x:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\DSO9\msmdrep.mdb;
Persist Security Info=False
x:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\DSO9

Internet Explorerer Settings
Make sure that the URL to your almost complete Microsoft Office Project Server 2007 PWA site is in the Trusted Zone of your Internet Security. You will then need to follow the final steps to be able to setup and build the cube.

  1. Open Internet Explorer, click Tools, and then click Internet Options.
  2. Click the Security tab, click the zone that you use to connect to the Office Project Server 2007, and then click Custom Level.
  3. Under Access data sources across domains, select Enable.

Note: If you set the security setting to Prompt, you cannot use the Office Web Components when you create a data analysis view.