Adding Task Information for Earned Value Analysis

  • Article

The OLAP task earned value extension provides additional task and outline code information to the Microsoft® Office Project Server 2003 staging tables and builds a Portfolio Analyzer OLAP cube that enables you to explore the dimensions of task earned value and outline codes interactively, along with other project dimensions. For example, you could use the cube to show the earned value of all tasks for projects in Seattle (the outline code) in the last three months of 2003.

Portfolio Analyzer uses the breakout functions to call routines that populate the staging tables, calculate the task earned value, and add the new task and outline code dimensions to the cube. Before you install and run the OLAP extension, you must first extend the standard view tables in Project Server to hold additional task and outline code data, and then extend the corresponding staging tables.

The following discussion refers to the extensibility model for Portfolio Analyzer, as described in the topic OLAP Extensibility Model. The task earned value extension uses all three points of extensibility:

  • Extension of the view tables.
  • Extension of the staging tables.
  • Extension and reprocessing of the cube.

Extending the View Tables

As you saw in the OLAP Extensibility Model, extending the view tables involves two steps:

  1. Extending the view table schema. The default view table schema does not include a place for the extra task data that the OLAP extension uses.
  2. Modifying the publishing query. When each project is published, Project Server runs a query that updates the view table data. Because the view tables are extended, the publishing query also needs to be modified to get the extra data.

To extend the view table schema:

The OLAP Extensibility Model topic shows how you can get SQL scripts for view table extensions in the EXTVTBLS.SQL file that is installed with Project Server. The task earned value extension does not need all of the additional fields provided in EXTVTBLS.SQL for the MSP_VIEW_PROJ_TASK_TP_BY_DAY view table, so the solution starter download includes a script to add the necessary fields.

After you install the solution starter download file, your installation directory will contain ...\Solution Starters\Portfolio Analyzer OLAP Extensions\TaskEarnedValue. For a list and description of all files in the download, see Solution Starter Files.

  • The TaskEarnedValue directory contains the Visual Basic 6.0 projects to build the breakout DLL and a test application. These projects are explained in the topics that follow.

  • The Utils subdirectory includes a script to install the view table schema extension: extendViewTaskTable.bat. If you do not have a default Project Server installation, you may need to change the value of EV_ProjectDatabaseName in the script:

    set EV_ProjectDatabaseName=ProjectServer
 . . .
OSQL -E  -d "%EV_ProjectDatabaseName%" -i extendViewTaskTable.txt

    The SQL Server osql.exe utility changes the ProjectServer database, using Transact-SQL script in the extendViewTaskTable.txt file:

    ALTER TABLE dbo.MSP_VIEW_PROJ_TASK_TP_BY_DAY ADD
   TaskTimeActualCost                        decimal(25,6)  NULL,
   TaskTimeActualOvertimeWork                decimal(25,6)  NULL,
   TaskTimeActualWork                        decimal(25,6)  NULL,
   TaskTimeACWP                              decimal(25,6)  NULL,
   TaskTimeBaselineWork                      decimal(25,6)  NULL,
   TaskTimeBCWP                              decimal(25,6)  NULL,
   TaskTimeBCWS                              decimal(25,6)  NULL,
   TaskTimeCost                              decimal(25,6)  NULL,
   TaskTimeOvertimeWork                      decimal(25,6)  NULL,
   TaskTimeRegularWork                       decimal(25,6)  NULL,
   TaskTimeWork                              decimal(25,6)  NULL
GO

To modify the publishing query:

The publishing query in the Project Server QYLIBSQL.SQL file includes a default query and a commented-out extended query, as described in OLAP Extensibility Model. Because the view table extension for task earned value does not use a fully extended MSP_VIEW_PROJ_TASK_TP_BY_DAY table, the solution starter download TaskEarnedValue\Utils directory also includes a matching query. You need to copy the query from the solution starter file qylibsql.txt and add it to the 50800 query ID in the Project Server installation's \BIN\QYLIBSQL.SQL as follows. The first seven fields in the modified SELECT statement are the default for the table.

Note  Be sure to comment-out the default 50800 query and leave the original EXTENDED line commented out. The modified extended 50800 SELECT statement must all be on one line; it is broken here into six lines only for easier reading.

// TASKTIMEPHASEDBYDAY QUERIES
// 50800 SELECT ProjectUniqueID,TaskUniqueID, ... FROM TaskTimephasedByDay
// EXTENDED: 50800 SELECT ProjectUniqueID,TaskUniqueID, ..., TaskTimeActualCost, ...
// EXTENDED for the Portfolio Analyzer OLAP extension, for Task Earned Value: 
50800 SELECT ProjectUniqueID,TaskUniqueID,TaskTimeStart,TaskTimeFinish,
    TaskTimeFixedCost,TaskTimeActualFixedCost,TaskTimeBaselineCost,
    TaskTimeActualCost,TaskTimeActualOvertimeWork,TaskTimeActualWork,
    TaskTimeACWP,TaskTimeBaselineWork,TaskTimeBCWP,TaskTimeBCWS,
    TaskTimeCost,TaskTimeOvertimeWork,TaskTimeRegularWork,TaskTimeWork 
    FROM TaskTimephasedByDay

Now when you publish a project, Project Server will populate the extended MSP_VIEW_PROJ_TASK_TP_BY_DAY view table with the appropriate task data.

Implementing the OLAP Extension for Task Earned Value

The previous steps of extending the view tables and changing the publishing query are preparation for implementing the task earned value OLAP extension. The following topics explain the implementation.