Extracting Timephased Data from the Microsoft Project Database

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary

This article describes three SQL script files you can use to extract timephased data from the Microsoft® Project or Microsoft Project Server database tables. You can use the SQL script files to extract information from the MSP_TIMEPHASED_DATA table in Microsoft Project, as well as the MSP_WEB_WORK table or the MSP _VIEW_PROJ_ASSN_TP_BY_DAY table in Microsoft Project Server. In addition, this article describes how to normalize the timephased data for display.

Using Timephased Data

Timephased data is task, resource, or assignment information that is distributed over time. There are many reasons to extract timephased data from either the Microsoft Project or the Microsoft Project Server database tables. For example, you may want to access data from the MSP_TIMEPHASED_DATA table when Microsoft Project is used, but Microsoft Project Server is not. Another case is where Microsoft Project Server is installed, but progress is tracked directly in Microsoft Project rather than in Microsoft Project Server.

The types of data that can be recorded in the table MSP_TIMEPHASED_DATA include:
  • Assignment actual work
  • Assignment actual overtime work
  • Assignment remaining work
  • Assignment baseline work
  • Assignment baseline cost
  • Assignment actual cost
  • Resource baseline work
  • Resource baseline cost
  • Task baseline work
  • Task baseline cost
  • Task percent complete
The types of data that can be recorded in the table MSP_WEB_WORK include:
  • Scheduled work
  • Actual work
  • Actual overtime work
The types of data that can be recorded in the table MSP_VIEW_PROJ_ASSN_TP_BY_DAY include:
  • Assignment work
  • Assignment regular work
  • Assignment actual work
  • Assignment actual overtime work
  • Assignment baseline work
  • Assignment cost
  • Assignment actual cost
  • Assignment baseline cost
  • Assignment peak units

See the document SvrDB.htm, located on the Microsoft Project 2002 CD in the folder \FILES\PFILES\MSOFFICE\OFFICE10\1033\ (for the U.S. English version), for information about the data that can be found in the tables MSP_VIEW_PROJ_TASK_TP_BY_DAY and MSP_VIEW_RES_TP_BY_DAY.

Normalizing Timephased Data

Timephased data about tasks, resources, and assignments is stored in the Microsoft Project database table MSP_TIMEPHASED_DATA and in the Microsoft Project Server database table MSP_WEB_WORK. Because of the way timephased data is stored for performance reasons, simply obtaining the values from one of the tables can make interpreting that data extremely difficult.

The SQL script files sp_pjactualsbyweek.sql (MSP_TIMEPHASED_DATA table), sp_psactualsbyweek.sql (MSP_WEB_WORK table), and sp_psactualsbyday.sql (MSP_VIEW_PROJ_ASSN_TP_BY_DAY table) extract this data and then assign one timescale unit per row, instead of up to seven timescale units per row (typically days), as is common in the Microsoft Project database. The scripts also format these values to make them more readable, for example, by displaying the timescale unit and indicating whether the value is for time (hours), cost (dollars), or percentage of work complete.

For more information about how timephased data is stored, including details about the columns (fields) referenced in the scripts and ways to safely modify the data in the Microsoft Project or Microsoft Project Server database, see the section "Managing Timephased Data" in the file ProjDB.htm, located on the Microsoft Project 2002 CD in the folder \FILES\PFILES\MSOFFICE\OFFICE10\1033\ (for the U.S. English version).

Using the sp_pjactualsbyweek.sql Script

You can use this script when it is necessary to access timephased data from the MSP_TIMEPHASED_DATA table. Typically, this is where Microsoft Project is used, but Microsoft Project Server is not. Another case is where Microsoft Project Server is installed, but progress is tracked directly in Microsoft Project rather than in Microsoft Project Server.

The issue with extracting data from the MSP_TIMEPHASED_DATA is that the data is not always stored in the database as it is presented in Microsoft Project. This is due to how Microsoft Project handles timephased data while working with multiple calendars, constraints and rules. As a result, extract reports do not always agree with Microsoft Project on when actual work was performed, although the total actual work performed should agree.

Using the sp_psactualsbyweek.sql Script

You can use this script when you want to extract timephased data from the Microsoft Project Server database in a way that is more usable than extracting data from the MSP_TIMEPHASED_DATA table in Microsoft Project. You can also use this script when actual work values are recorded in Microsoft Project Server.

Using the sp_psactualsbyday.sql Script

You can extract data from the view tables in periods of one day, which makes this script more convenient for extracting data than the non-view tables. However, data in the timephased data view tables (MSP_VIEW_PROJ_ASSN_TP_BY_DAY, MSP_VIEW_PROJ_TASK_TP_BY_DAY, or MSP_VIEW_RES_TP_BY_DAY) will only be available after the project has been updated with actual hours and published back to Microsoft Project Server.

Running the SQL Scripts

Although these scripts can be run from an application like OSQL or Microsoft SQL Server™ Query Analyzer, they were designed to be implemented in SQL Server stored procedures. Stored procedures can be called from a script macro or from an application using an ActiveX® Data Objects (ADO) Command object, which may be more convenient for many users.

To run one of these script files, use either the command-line OSQL utility, or SQL Query Analyzer, available from the Tools menu of SQL Server Enterprise Manager. When you run one of these script files, you must specify the SQL Server computer and database name for the Microsoft Project or Microsoft Project Server database.

For more information about SQL scripts, see Documenting and Scripting Databases and Working with SQL Scripts.