Data Differences between the Timesheet Cube & MSP_Project_Timesheet Cube in Microsoft Project Server 2007

The fact that Project Server has two different "Timesheet" cubes, alone, is an understandable source of confusion for many users, but when people then discover that in most Project Server implementations where both of these contain actual work data, that most often these contain very different data they get really puzzled. This can be particularly noticeable when you look at the data summarized by resources or projects. While much of the task and assignment line-level data may appear to be identical, even this data can vary widely. Not only is this a source of confusion, it’s rather disconcerting to many new Project Server users who immediately become suspicious of all the data in Project Server 2007.

To get comfortable with the data differences you first have to understand that the data for these two cubes originate from different data sources. That’s why the Microsoft product team created two distinct cubes in the first place. The Timesheet Cube shows actual work reported through the timesheet system in Project Server 2007, which is completely isolated from the actual work record recorded in the project other than the fact that data entered through the timesheet system can be pushed through the My Tasks page and into the Project Plans themselves. The SP_Project_Timesheet shows the actual work record as it is recorded in the Project file. Because the product team wanted to make the configuration of Project Server 2007 as flexible as possible, you can choose to configure your system to collect time in the Timesheets without ever sending the information to project, or you can choose to send some information to the project, or you can configure Project Server 2007 to force all users to enter all time (Time entry by Timesheet only. Users will sync to update tasks option) through the timesheet and you can mandate that the actual work record in Project 2007 always be equal to the time collected in the Timesheets (Restrict updates to Project Web Access option). Of course, you can also choose to disable the Timesheet system, but then this data duplicity issue doesn’t apply.

There are numerous reasons why the data in these cubes are not always in sync, including some obscure bugs in the project client that caused alteration of actual work and that have just recently been resolved in the December 2009 CU. With that said, bugs are the least common reason for discrepancies unless you’re using the Restrict updates to Project Web Access option, which locks down the system rather tightly. Without applying that setting, project managers can update the project plans directly, which can cause actual work to change in many ways. For example, suppose a PM updates a task status directly in the project plan where a resource is assigned. This action causes the system to impute actual work for the assigned resource that will never show in the timesheet cube. As this is a very common practice, in many, if not most cases, you are very likely to see more actual work registered in the project plan than in the timesheets, unless of course, your PMs are also in the habit of deleting completed tasks, which has the exact opposite impact. Using the more restricted setting, however, puts the system into a very rigid and unforgiving state in its effort to maintain integrity between the Timesheet record and the Project record. Unless organizations have very experienced Microsoft Project practitioners operating a high-level of maturity with the tool, this setting is not practical because it causes most novice users untold grief in managing their plans.

Remember, also, that the Timesheet cube may contain actual work entered against Administrative task classifications and that this data shows only in the Timesheet cube and never makes it into a project record. If you use the Administrative time feature, you need to exclude this data in your analysis views in order to begin to compare roll-up data between the Timesheet cube and the SP_Project_Timesheet cube, which remains a problematic challenge unless you are willing to commit to the restrictive system options.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s