Architecture of Portfolio Analyzer

Microsoft® Office Project 2003 supports online analytical processing (OLAP) through the Portfolio Analyzer feature. Portfolio Analyzer uses components in all three tiers of the Project application:

• Database tier   Portfolio Analyzer uses SQL Server Analysis Services to create an OLAP cube. The Analysis Services use tables generated by the Project Server middle tier components.
• Middle tier (business objects)   The cube generation service in Portfolio Analyzer creates a set of fact and dimension tables, based on rules specified in Microsoft Office Project Web Access, from data in a number of tables in the Project Server database.
• Client tier   Project Web Access uses the Microsoft Office 2003 Web PivotTable® and PivotChart® controls bound to the OLAP cube, to view the data.

The following figure shows the high-level architecture of Portfolio Analyzer. The Microsoft Office 2003 Web controls in the Project Web Access page connect to the Portfolio Analyzer cube built by Project Server. The client is Microsoft Internet Explorer, and the server is the computer where SQL Server Analysis Services is installed, which may or may not be the same computer where Project Server is installed.

The MSP_PORTFOLIO_ANALYZER cube is actually a virtual cube made up of two cubes: the MSP_ASSN_FACT cube and the MSP_RES_AVAIL_FACT cube. These cubes reside in the cube database specified by the administrator in Project Web Access.

The MSP_ASSN_FACT cube contains the bulk of the data. For example, this cube includes assignment information that has been extracted from projects. The fact table of the MSP_ASSN_FACT cube contains timephased assignment data for all enterprise projects. The cube contains the standard time, project, project version, booking type, resource status, and resource dimensions, along with dimensions for any enterprise project outline codes and enterprise resource outline codes defined in Project Server.

The MSP_RES_AVAIL_FACT cube contains resource availability information and calendar information for resources. The fact table of the MSP_RES_AVAIL_FACT cube contains mainly timephased resource availability data for all resources in the enterprise resource pool. The cube contains the standard time and resources dimensions, as well as dimensions for any enterprise resource outline codes defined in Project Server.

The two cubes are combined and presented through a virtual cube named MSP_PORTFOLIO_ANALYZER, so that the assignment information can be seen side-by-side with resource availability information by the end users who browse the OLAP cube through the Portfolio Analyzer user interface in Project Web Access.

Cube Building Architecture

Project Server builds the OLAP cube on the server with SQL Server Analysis Services, and Office Web controls bind directly to the OLAP cube. Project Server components do not form a middle tier between the Office Web controls and the OLAP cube. Because Project Server does not apply any default security settings, you must use the SQL Server Analysis Manager to apply security settings to the OLAP cube. SQL Server Analysis Services allows security to be applied at a very detailed level, and Project Server maintains any existing security roles when cubes are updated.

For more information about security, see the topic Portfolio Analyzer Cube Security. For information about setting up Portfolio Analyzer and SQL Server Analysis Services with the Project Server sample database, and setting security roles in Analysis Manager, see the Project Server 2003 Installation Guide.

Generating the Cube

As an administrator in Project Web Access, you can start the OLAP cube building process in Project Server manually, or you can specify settings so that the cube is generated automatically on a regular basis. For example, you can have Project Server generate the cube weekly or at midnight every two days.

To specify settings for building the OLAP cube:

1. In Project Web Access, click Admin.
2. On the Administration overview page, click Manage enterprise features in the left pane or in the main pane.
3. Under Enterprise options in the left pane, click Update resource tables and OLAP cube.
4. Fill in and select the appropriate fields to build the cube. For example, to build a cube for the Project Server sample database, do all of the following:
   • Click Yes, I want to update resource availability information and build an OLAP Cube.
   • Enter the computer name where Analysis Server is installed.
   • Select the date range. For the sample database, click Use the earliest project start date and the latest project finish date.
5. In Update frequency, specify when to build the cube.
   • To build the cube manually, click Update only when specified, and then click Update Now.
   • To build at regular intervals, click Update every ..., and then select the time interval and enter the starting date and time.

Cube Generating Processes

The dynamic-link library (DLL) file Projolap.dll contains the cube building service. This file resides on the Project Server computer, where the processing takes place. There are two main steps that occur when you initiate the process to generate the OLAP cube.

1. Generate staging tables   A Project Server business object responds to the manual or automated request to build a cube, checks permissions, and then calls Project Data Service (PDS) methods to gather the data and build the cube with the specified name and dates. The PDS calls the cube builder process to gather the necessary view table and project table data and creates the staging tables, also referred to as the data warehouse. The staging tables contain all of the Project Server data necessary for building the OLAP cube, and all have the prefix MSP_CUBE in the Project Server database. The schema of the staging tables is optimized so that SQL Server Analysis Services can easily generate the OLAP cube. Most of the time required to build a cube is in creating and optimizing the staging tables.

   The following figure shows the processes in generating the staging tables.

   

2. Generate the virtual cube   After the staging tables are built, the cube building service (Projolap.dll) calls cube generation processes that use Decision Support Objects (DSO) methods to interact with the Analysis Server. The Analysis Server then generates the cube from the staging tables of the data warehouse. When the cube generation processes are complete, the virtual cube specified by the user in the Project Web Access Admin center, and the two cubes it is based on, will have been built in the Analysis Server database.

   The following figure shows the processes in generating the virtual cube.