Share via

Using the Task Earned Value Cube

  • Article

If you have built the Portfolio Analyzer OLAP cube with the task earned value extension, the new MSP_TASK_EARNED_VALUES cube exists, but Portfolio Analyzer does not yet have a way to show it. To use the task earned value OLAP extension, you need to add a view to Portfolio Analyzer where you link the pivot table and chart controls from the Office Web Components to the earned value cube.

To show earned value information, you need to enter cost data in your projects, update the tasks with percent complete, and then build the cube with Portfolio Analyzer after you have installed the earned value OLAP extension. You can choose any status date for which to show earned value. It is not necessary to have enterprise task outline codes associated with tasks to use the earned value OLAP extension; however, if you do use outline codes, that provides another valuable way to slice task status based on phases, groups, or cost codes that are used in your organization.

The Manage Views page in Microsoft Office Project Web Access enables you to add a custom view.

To add a task earned value view to Portfolio Analyzer:

  1. In Project Web Access, click Admin, and then click Manage Views.

  2. Scroll down the list of views, and you can see the current views under the Portfolio Analyzer heading. Click Add View under the heading Specify Views.

  3. Click Portfolio Analyzer in the View Type section.

  4. Type a name and description in the View name and description section, and then type the name of the Analysis Server and the name of the cube in the Analysis Server and Cube section. The cube name is the cube database name that you see under the server name in SQL Server Analysis Manager, for example, MSP_Sample_Cube.

  5. Click PivotTable with Chart in the Portfolio Analyzer Mode section.

  6. Click the Chart Type icon in the toolbar above the empty chart, and then click SmoothLine for a chart that can plot the three earned value members (ACWP, BCWS, and BCWP). Click the Close box in the Commands and Options dialog box.

  7. Click the Show/Hide Legend icon to show the legend under the Drop Series Fields Here box in the chart.

  8. Click the Field List icon in the toolbar; if you see the MSP_PORTFOLIO_ANALYZER cube and fields in the Chart Field List, you will need to show the MSP_TASK_EARNED_VALUES cube instead.

    To show the MSP_TASK_EARNED_VALUES cube:

    • Click the Chart Wizard icon. In the Data Source tab of the Commands and Options dialog, in the section Select where the chart data comes from, click Data from a database table or query.

    • In section 2 of the Data Source tab, click Connection. The connection string should be similar to the following:

      Provider=MSOLAP.2;Integrated Security=SSPI;Persist Security Info=True;Data Source=ServerName;
    Client Cache Size=25;Auto Synch Period=10000;Initial Catalog=MSP_Sample_Cube

      If the MSP_TASK_EARNED_VALUES cube is not in the drop-down list in the Data Details tab, click Edit for the connection string. In the Select Data Source dialog, click New Source. In the Data Connection Wizard dialog, click Microsoft SQL Server OLAP Services, and then click Next. Type the Analysis Server name, and then select the logon credentials (click User Windows Authentication, or type the user name and password for the SQL Server Analysis Services database). Click Finish.

    • In the Data Connection Wizard dialog, click the drop-down list Select the database that contains the data you want, and select the Portfolio Analyzer cube, for example, MSP_Sample_Cube. Check Connect to a specific cube or table, and then click MSP_TASK_EARNED_VALUES in the list. Click Next and type a description and keywords if you want, and then click Finish.

  9. Click the Field List icon again in the chart toolbar; you should now see the MSP_TASK_EARNED_VALUES cube in the Chart Field List.

  10. Drag the Project List dimension to the chart box labeled Drop Filter Fields Here.

    Similarly, add the following dimensions to the chart:

    • Expand the Time group in the Chart Field List dialog box, and then drag one or more of the time dimensions to the box labeled Drop Category Fields Here. For example, drag Years, Quarters, Months, and Days to the category fields box.
    • Expand the Totals member, and then drag Task ACWP, Task BCWS, and Task BCWP anywhere over the gray part of the chart. As you drag a field over the chart, you will see the tooltip Drop Data Fields Here. As you drag a field, you can see the order in which the fields will appear with the blue insertion point at the top of the chart. When you drop a field on the chart, the Totals series shows with a legend on the right side of the chart.

    All of the chart fields are now filled and show data from the MSP_TASK_EARNED_VALUES cube. The following figure shows the result.

    Adding MSP_TASK_EARNED_VALUES dimensions to the chart in a new view

  11. If you have defined enterprise task outline codes in your projects, you can also add one or more of them as filter fields next to the Projects filter. For example, the figure shows the task outline code dimensions Development, Initial, and Production. They have not been added to the chart.

  12. In the Categories section of the Specify Views page, add the categories you want from the Available Categories list to the list Categories this view belongs to.

  13. Click Save Changes.

In Project Web Access, click Projects to go to the Project Center page, and then click Analyze Projects in Portfolio Analyzer. In the Portfolio Analyzer page, click the drop-down list to choose a view, and then select the view you just created. If you click Show toolbar in the View Options section, you can interactively modify the view you just created. For example:

  • Select a different set of projects in the Projects dimension within the drop-down list (click the black downward-pointing arrow in the Projects dimension to see the set of values).
  • If you added enterprise task outline codes, add or remove an outline code filter or select specific task outline codes within the drop-down list.
  • Add or remove dimensions in the category fields. For example, select a set of years or remove the Years and Quarters dimensions to show just the Months, and then select which years to show in the drop-down list for Months.
  • Add or remove data members in the chart. For example, drag Task Overtime Work to the chart.

To make any of the changes permanent, you have to modify the view with Manage Views in the Admin page.