Export (0) Print
Expand All

Cube building process overview

Office 2007

Updated: April 23, 2009

 

Topic Last Modified: 2009-04-16

The Microsoft Office Project Server 2007 Cube Building Service is a reporting and analysis feature in Microsoft Project Server that allows you to perform complex analysis of project data. It uses SQL Server Analysis Services to create an online analytical processing (OLAP) database containing several cubes that are used for data analysis reporting. It is administered through the Project Web Access Site Settings page and allows for data cubes to be built from selections within the Reporting database. This article describes an overview of the cube building process.

New features in Office Project Server 2007 make cube building more convenient to do than in Microsoft Office Project Server 2003. Microsoft Office Project Server 2007 is multithreaded, and the new Project Server Queue service prioritizes cube builds amidst other actions taking place in a Project Server deployment. But it can take several hours for a cube to build and errors can occur during this process that require troubleshooting. Therefore, it is valuable to understand how the process works.

Once you have configured your cube and your build settings, you are ready to build your cube. At that point, you simply click a button to start the process, but the process has several complex steps that happen in the background. The process of building the Office Project Server 2007 OLAP cube database goes as follows.

Overview of Project cube build process
  1. Clicking the Build Now button in the Server Settings section of Project Web Access creates a cube build request that generates an OLAP cube build job in the Project Server Queue. The Microsoft Office Project Server Queue service takes the job and calls ProjectServerOlapCubeGenerator.exe, which in turn starts the Cube Generator process. This executable file is located by default in C:\Program Files\Microsoft Office Servers\12.0\Bin and is not load-balanced.

  2. The identity running the Queue service and the Cube Generator process manages Analysis Services through the Analysis Services Decision Support Objects (DSO). The Cube Building Service uses the SQL Server 2000 Analysis Services DSO compatibility mode. SQL Server 2005 Analysis Service is backwards compatible and can still be used.

  3. Analysis Services accesses the SQL Server Analysis Services Repository of metadata used to define the cubes. As a best practice, the repository should be stored in a SQL Server database. In SQL Server 2005 Analysis Services, it can also reside in a shared folder.

  4. The cube database is built by Analysis Services (Msmdsrv.exe), based on the instructions given by the Cube Generator process. These instructions are based on the cube build and configuration settings you have specified in Project Web Access. During this process, Analysis Services accesses staging tables for the cube database from the Project Server Reporting database.

  5. The Project OLAP cube database is complete.

With a successful cube build, a cube database is created in SQL Server Analysis Services. Three virtual cubes are available, based on 14 Office Project Server 2007 and Windows SharePoint Services 3.0 cubes.

Following are the three default virtual cubes. These cubes contain consolidated sets of dimensions and measures in the underlying cube component. Users that view the cube data will see a virtual cube as a single cube. These virtual cubes are recommended for use in reporting on your Office Project Server 2007 and related Windows SharePoint Services 3.0 data.

  • MSP_Project_Timesheet   Combines the Assignment Timephased, Resource Timephased, and EPM Timesheet cubes.

  • MSP_Project_WSS   Combines Project Non-timephased, Issues, Risks, and Deliverables cubes. This cube is most useful for reporting on Windows SharePoint Services 3.0 data.

  • MSP_Portfolio_Analyzer   Combines the Assignment Timephased and Resource Timephased cubes. This Portfolio Analyzer cube is backward-compatible with the Project Server 2003 MSP_Portfolio_Analyzer cube. This cube is most useful for reporting on project data and project plans.

For best results, consider the following when creating reports based on cube data:

  • Timesheet cubes contain details on non-working time.

  • Timesheet data may not align with the actual time data in the project plan. If changes are made in the project plan after the timesheet is created, the actual time listed in each may not match.

  • Historical timesheet related information that has been removed, renamed, or deleted in the project plan will not be available in the timesheet cubes.

  • When you are tracking project information where time has been submitted and accepted, we recommend that you use the MSP_Portfolio_Analyzer cube.

NoteNote:
Office Project Server 2007 OLAP cube schema information can be found in the Project 2007 SDK: Software Development Kit (http://go.microsoft.com/fwlink/?LinkId=86947&clcid=0x409).

Following are the eight primary enterprise project management (EPM) cubes and the three default Windows SharePoint Services 3.0 cubes. These cubes serve as aggregation points for the virtual cubes listed above and are not recommended for direct use in reporting.

EPM cubes:

  • Project Non-timephased

  • Task Non-timephased

  • Resource Non-timephased

  • Resource Timephased

  • Assignment Non-timephased

  • Assignment Timephased

  • Timesheet

  • EPM Timesheet

Windows SharePoint Services 3.0 cubes:

  • Risks

  • Issues

  • Deliverables

This topic is included in the following downloadable book for easier reading and printing:

See the full list of available books at Downloadable content for Project Server 2007 .

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft