Solution Starter: Portfolio Analyzer OLAP Extensions
Microsoft® Office Project Server 2003
Microsoft Office Project 2003
Summary: Extend Portfolio Analyzer cubes by using the Portfolio Analyzer OLAP Extensions Solution Starter. This article highlights new features in the Microsoft Office Project 2003 Enterprise Project Management solution, and explains how these new features make great strides in business insight, work management, collaboration, and extensibility. (9 printed pages)
Microsoft Office Project 2003 captures a wealth of information that can provide deep insight into a business. Users can ultimately make better business decisions with advanced reports and analysis on project data. Project users can use the Portfolio Analyzer feature to perform complex analysis on project data. When Microsoft Office Project Server is first deployed, a default set of information is included for Portfolio Analyzer. Users have access to resource and assignment information to gain insight into the health of a project portfolio by interacting with time-phased data and charts through a Web-based portal.
This article demonstrates how customers can use the Portfolio Analyzer OLAP Extensions solution starter to customize Portfolio Analyzer and create reports that suit their organizational needs. To see the complete Solution Starter see the Microsoft Office Project 2003 SDK.The OLAP extension for issues and risks enables project managers to make the most use out of the Microsoft® Windows® SharePoint™ Services integrated risk and issues tracking feature. Team members can continue to use the feature as usual to enter risk probability, cost to mitigation, and so on, while project managers can create interactive charts and tables to identify potentially risky projects and tackle them before they can materialize and create problems.
The OLAP extension for task earned value enables project managers to easily visualize and measure the budget and schedule status of tasks in one or more projects. Managers can use enterprise task outline codes to graph the status of an entire portfolio of projects based on location, project phase, group, or other category, and quickly see the problems and successes.
This article describes two reporting systems created by extending the current Portfolio Analyzer OLAP cubes with the Risk cube, and the Task Earned Value cube. These OLAP extensions enable Project Managers to create Task Earned Value reports and Task Issues and Risks reports by including richer and more detailed task information into Portfolio Analyzer cubes.
From the Task Earned Value cube, the user will be able to show a graph or table of Earned Value calculations for a portfolio of projects. The cube will also enable the user to drill down into the earned value by task outline codes, as well as by using standard parameters. For the Risk cube, users will be able to create a report of all the issues and risks and have the ability to drill down into them by standard measures included in the Portfolio Analyzer.
Scenario: Portfolio Analyzer Risk Cube
John is a project manager of 10 to 20 projects. His team has a fairly high project management maturity level, and enters risks and issues for projects and tasks whenever it is appropriate. Once a week on average, John would look at all the risks and issues entered by his team and review the various measures associated with them, such as cost to mitigate risk, and the risk probability. Currently, John tries to make sense out of the list of risks and issues in two ways. He sometimes prints the list of all the risks and issues and their associated measure for all the projects, and then attempts to go through them manually armed with a highlighter and a pen. Sometimes he exports everything to a datasheet, and then manually edits the sheets from multiple projects to create reports that make sense for him.
Fortunately, John read in a Microsoft Office Project newsletter that he can now retrieve Windows SharePoint Services risk and issue information into Portfolio Analyzer to create rich, interactive reporting. All John needs to do is tell Project that he needs to get the additional risk and issue information into the cube staging tables that ultimate will get into Portfolio Analyzer cubes. After the additional information is in the cube, he can create a Portfolio Analyzer cube as usual, and the next time he uses the Portfolio Analyzer he will be able to interact with all the new information. John has created the cube with the new risk information; he proceeds to create some interesting reports using Microsoft Office Project Web Access with the Microsoft Office Web Components for viewing OLAP data. The first thing he tries is creating a risk view that he can use to replace his printouts. John creates one risk bubble chart for all the projects he manages. The two axes on his chart represent the probability of the risk happening and the impact of the risk. The size of the bubbles represents the cost to mitigate the risk.
After the Windows SharePoint Services risk information is populated into the Portfolio Analyzer cube, customers can use it to create rich interactive views of project risks.
Portfolio Analyzer needs to be extended so that this new information can show up in the cubes. After the extensibility code is complete and Project is aware of the new extension codes, customers can create cubes as usual without any additional steps. The risk cube allows project managers to really drill down into all the risks associated with projects and slice and dice by any of the defined dimensions.
The most critical part of the process is to ensure users are entering rich information about the risks in the project.
Figure 1. John's team members enter rich information about the risks of the project (click picture to see larger image)
In addition to the standard fields of probability, impact, and cost, John has included a category for the risk. This will allow John to filter on the category dimension, allowing him to only look at Competitor risks if he wants.
After the list of risks and issues is entered, John can start working on creating a report. Prior to this, John compiled the extensibility code onto a DLL file, and registered that DLL on the Project Server computer. This means that the next time a cube gets created, a set of cube tables will be populated with risks and issues information, and John will be able to create a report using the data.
Figure 2. Risk information is available when a new Portfolio Analyzer view is created (click picture to see larger image)
John is now ready to analyze the risks associated with all his projects. He proceeds to create an Office Web Components chart so that he can interact with and drill down into the data.
Figure 3. From this chart, John can drill down and analyze all risks for his projects (click picture to see larger image)
Using the advanced analysis capabilities in the Office Web Components, John can now drill down into the risks by all the available dimensions. The preceding chart shows all the risks for his projects. However, John is only interested in looking at Active risks that belong to the Competitor category; he can deal with Resource risks another time.
Figure 4. John drills down to the specific type of risk that he needs to see (click picture to see larger image)
John has a much better understanding of where his project stands with regard to the various types of risk. After he drills down to include only active competitor risks, he realizes that Risky Project (shown in blue) is not the riskiest project. He can now spend time working on mitigating the active Competitor risks found in Safe Project.
Scenario: Portfolio Analyzer Task Earned Value Cube
After looking at the risks for all his projects. John wants to make sure that his projects will be on schedule. He uses Earned Value analysis to measure his projects. Furthermore, John has created a set of enterprise task outline codes to categorize tasks in his projects. Now John can view his earned value analysis, and use one of the task outline codes as a dimension for all his other Portfolio Analyzer reports.
John created his project plan using Microsoft Office Project Professional; he has categorized his tasks by a set of enterprise task outline codes. One set of these outline codes he has named Task Cost Code, and he would like to analyze the earned value of all his projects by these codes.
Using the steps described earlier to create the earned value OLAP extension, he was able to include enterprise task outline code information as a dimension for all the reports he created. He also generated a cube that contained Earned Value calculations.
Figure 5. John is using Task Outline Code as a filter for his Earned Value Analysis (click picture to see larger image)
John was looking at the Task Earned Value graph for all of his projects. He wants to drill down on that report and filter the earned value data based on the Task Cost Code dimension. He has selected the set of cost codes that he is interested in; Figure 5 shows the selected codes are 1010, 1011, 1020, 1040, and any tasks that have no assigned cost code (that is, No Value). John now has a view of the earned value of all the tasks, for the set of task outline codes that he defined.
From the earned value graph, John can easily see whether the set of tasks he has selected is ahead or behind schedule, and over or under budget. The budgeted cost of work scheduled (the purple line for BCWS in Figure 5) shows the daily budgeted cost of all the selected tasks, through the status date. The actual cost of work performed (the blue ACWP line) shows the daily cost of the set of tasks; that is, the resource and fixed costs of each task, summed over the set of selected tasks. John can see the tasks are generally under budget, because the actual costs are a little below the budgeted costs.
The budgeted cost of work performed (the yellow BCWP line) is the actual task earned value: that is, BCWP is the total budgeted cost of each of the tasks, times the percent of work completed. John can see the true value (BCWP) is very close to the actual value (ACWP); this set of projects is healthy. If the BCWP were to fall below the ACWP, it could indicate the set of tasks is behind schedule, since the value of work performed would be less than the amount spent to do the work.
If John sees any significant discrepancies, he can quickly rearrange the chart view to show different dimensions such as project versions, or to see how additional data such as task overtime work, task fixed cost, task actual work, and other measures vary over time. John quickly finds that the task earned value OLAP extension of Portfolio Analyzer is an indispensable project management tool.
John has requested that his IT development group add new dimensions and calculated measurements to the earned value OLAP extension. They plan to adapt the existing solution to add cost variance (CV), schedule variance (SV), cost performance index (CPI), schedule performance index (SPI), and to complete performance index (TCPI). These earned value indicators provide other ways of seeing whether projects are on schedule and within budget. John can also see all of this data in different views within Project Professional, but he finds the rapid interactive visual analysis and easily made charts for his reports to company executives are invaluable.
Microsoft Office Project Components
Microsoft Office Project Server Breakout Module
When Project Server initiates the cube-building process, it checks for any extension DLL that has been registered on the server. If it finds one, it will initiate a call to the two breakout functions UserStagingTableUpdate and UserOlapUpdate that are defined in the extensibility model. Developers can adapt those functions to perform a variety of operations.
Microsoft Analysis Server Decision Support Objects
Decision Support Objects (DSO) is the API that allows the user to programmatically communicate with Microsoft® SQL Server™ Analysis Services and the cubes associated with the Analysis Server.
This section briefly describes the Portfolio Analyzer extensibility architecture.
Extend View Tables
The first step in extending view tables is to alter the schemas of the view tables. This will create new columns or new view tables to store more information when a project is published. The user can then use the provided EXTVTBLS.SQL file to alter the schema of the view tables. The EXTVTBLS.SQL file can be found on the Microsoft Office Project Server CD in the \support\database directory.
Modify Publishing Queries
After the tables are created, you must update the publishing query so that the new columns or tables are populated the next time a project gets published. Project Server looks at the QYLIBSQL.SQL file found in the .\Microsoft Office Project Server 2003\BIN to execute queries to populate the view tables. In that SQL file, the user will find the query that gets executed by default. To populate the new view tables, the user can comment out the default query and uncomment the extended query.
Extend/Create Staging Tables
Because the updated publishing queries might bring in more information, the user may need to create new staging tables; the names of the tables typically start with MSP_CUBE_*. If you are bringing in external data as part of the extensibility process, you would also need to create staging tables to hold the data.
Implement Extensibility Breakout Points
The first breakout point occurs after you have created the staging tables. Using the sample code, you can use the function UserStagingTablesUpdate to add data to the staging tables. The second breakout point occurs after Project Server has generated the cube staging tables, which is where you can use the UserOLAPUpdate function. At this point, you can build an additional cube within the Portfolio Analyzer cube, add security roles to the cube or programmatically process extensions performed at the first breakout point, and then process the cube again.
Register the Breakout DLL and Build the Cube
After the breakout DLL is implemented, you must register the DLL on Project Server so that the cube building service is aware of it. After the DLL is registered, anytime a cube building command is issued, the extensibility code will be executed.
Microsoft Office Project 2003 provides certain very sophisticated issue- and risk-tracking features for the team members to use. In order for the organization to make the most of out risk-tracking features, project managers must be given access to sophisticated analysis tools. Project managers can most effectively mitigate and tackle risks before the real problem happens by drilling down into interactive project risk reports and filtering out to specific risk categories entered by team members. Extending the Portfolio Analyzer cube to include risk data also allows users to create a graphic representation of projects and the risks associated with them.
Project Professional is the best tool for entering task earned value data, and creating and assigning enterprise outline codes that uniquely categorize tasks for specific businesses. The Portfolio Analyzer in Project can show data for many projects across the entire organization so that managers can quickly find and drill down into problem areas in scheduling and budget for multiple projects and across departments. Instead of trying to analyze tables of data, project managers can more effectively analyze and report that data by extending the Portfolio Analyzer cube to include task earned value and enterprise task outline codes.
Portfolio Analyzer is a highly customizable feature for analyzing a portfolio of projects. Organizations can provide their own unique measures and dimensions. The Project Management Officer of the organization can even include metrics against which projects are measured, and project managers can include that as a dimension in their view of the OLAP cube.