By:  Collin Quiring

I have been teaching Project Server 2007 Cube Building lately.  To me, this means diving into the tool and testing its capabilities so that I am prepared for questions and “what if” scenarios.  I will probably be posting a time or two on some of my discoveries.  But, I have noticed that there appears to be little information about the very basics on cubes.  Like the differences of the cube types.  There is LOTS of information on setup, configuration, modification, reporting capabilities and so on, but almost everything I find seems to assume that you know what cubes are being created and some of the definitions.  There is also some terminology that is used interchangeably (but probably shouldn’t be).  For this article, I am referring to “OLAP cubes” which might be called “cubes” or “Data Analysis” or “Reporting”.

So, with information combined from the Project Server 2007 SDK and my discovery, I provide this list about some fundamental information and short definitions.

The OLAP database for Project Server includes a total of 14 default cubes for enterprise project management (EPM) reports, rather than the one cube in earlier Project Server versions. Following are the eight primary EPM cubes:

·         Project Non-timephased – Data allowing cross project and master project reports

·         Task Non-timephased – Task non timephased data

·         Resource Non-timephased – Resource non timephased data

·         Resource Timephased – Resource timephased data

·         Assignment Non-timephased – Assignment non timephased data

·         Assignment Timephased – Assignment non timephased data

·         Timesheet – Specific Timesheet dimensions (Timesheet Project, Timesheet Task and Timesheet Resource) for data   (see note below)

·         EPM Timesheet – Timesheet and EPM dimensions (Project, Task and Resource) with timesheet measures.

Following are the three default Windows SharePoint Services cubes:

·         Risks – WSS risks data

·         Issues – WSS issues data

·         Deliverables – WSS deliverables data

Following are the three default virtual cubes that combine data from regular cubes:

·         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.

·         MSP_Portfolio_Analyzer combines the Assignment Timephased and Resource Timephased cubes. The Portfolio Analyzer cube is backward-compatible with the Project Server 2003 MSP_Portfolio_Analyzer cube. You can add the resource availability calculated measure to MSP_Portfolio_Analyzer.

The next question that I am often asked is about the difference between “timephased” and “non-timephased”.  The definition of timephased data means that it is information about a task, resource or assignment distributed over time.  Simply put, it means that a time component exists.  For example, the difference between the Resource Non-Timephased and Timephased cubes is that the timephased one has the dimensions and measures for Time, Fiscal Time, Base Capacity and Capacity while the non-timephased has dimensions and measures for Resource Type, Standard Rate, Overtime Rate, Cost per Use, and Resource Count.

This often leads to a question about the definition of dimensions and measures.  A dimension is used as a pivot for analyzing and providing more detail.  A measure is the detail information associated with different dimensions to provide more detail or as a summary.  For example, in the Resource non-Timephased cube, “Cost Per Use” is a measure and “Resource Type” is a dimension.  There are also calculated measures that you can create but that is beyond the goal of this article.

Note for Timesheet cube (from Project 2007 SDK):  This cube contains specific Timesheet dimensions (Timesheet Project, Timesheet Task and Timesheet Resource) for data that has been entered in a timesheet. That is, resources that haven’t submitted any timesheets will not be part of the Timesheet Resource List dimension. This enables some historical reporting on timesheet data, so if a resource becomes inactive but he/she submitted timesheets, the submitted data will remain in this cube.