Microsoft Project 2010
The official blog of the Microsoft Project product team. Learn how to manage your work effectively

Cube Building Services Overview

Cube Building Services Overview

  • Comments 26

For our first feature discussion, I will introduce you to Project “12” Cube Building Services that represent a part of our offerings for Enterprise Project Management Reporting. It also happens to be the feature I own, so I am very excited to share with you the work we have done.

 

Project Server 2003 ships a foundation to enterprise reporting and data analysis with the Portfolio Analyzer cube. In Project Server “12” we have expanded the idea to offer a rich set of data ready to consume, out of the box. The Cube Building Services offers an environment to truly provide business intelligence and insight to businesses using Project.  

 

We have increased the number of cubes offered to 11 cubes and 3 virtual cubes. The cubes are built using the Project Server “12” Reporting infrastructure. This infrastructure includes a dedicated Reporting SQL database that contains all Project Server “12” data that has been published. This data is incrementally update at real time as data gets published, tremendously improving the performance for the cubes as well.

 

The list of cubes offered are the following (this may be subject to change):

1.    Project Non Timephased

2.    Task Non Timephased

3.    Assignment Non Timephased

4.    Assignment Timephased

5.    Resource Non Timephased

6.    Resource Timephased

7.    EPM Timesheet

8.    Timesheet

9.    Issues

10. Risks

11. Deliverables

 

The virtual cubes offered are the following (also subject to change):

1.    Portfolio Analyzer – backwards compatible with Project Server 2003

2.    Project Timesheet

3.    Project WSS

 

During the Project Conference, one of the top questions I was asked regarding the cube schema was whether the Time dimension has a Week level and the answer is YES! In addition to the Time dimension, we’ll also offer a Fiscal Time dimension that will map to fiscal periods defined in the Project Server though it will not follow the traditional Time dimension hierarchy (Year > Quarter > Month…).

 

The Cube Building Services in the Project Web Access interface provides a very centralized set of cube administrator pages.

 

Cube Building Settings

Similarly to Project Server 2003, this page helps administrator setup the cube builds and define the cube settings:

·         Analysis Services server name

·         Data range to be included on the cubes

·         Frequency to which the cubes should be updated

 

Cube Customization

The Cube Building Services in Project Server “12” has an easy to use interface to allow customization of the cubes by using Enterprise Custom Fields. For example, you can add Location custom field to your Assignment Timephased cube as a dimension.  That would allow you to analyze Actual Cost sliced on Location over a period of time. This is very simple example, but you can get the idea of the powerful analysis you will be able to do with this.

 

We also offer an interface to add calculated members without having to write any custom code just the appropriate MDX formula. For example, for Profit, the formula would be something like [Revenue] – [Cost]

 

Once the customizations are save, they will be added to the cubes the next time the cube is built.

 

Cube Building Status

This page has been added to help administrators verify the status of the building process and troubleshoot if there are any errors, such as the Analysis Services server name is invalid.

 

Note: Project Server “12” also has an amazing Queue feature that allows great job management on the server side but I’ll save the details for a future post.

 

The Cube Building Services has been built primarily in Analysis Services 2000 though it successfully builds in Analysis Services 2005 as well. For those of you who did have a chance to use these cubes in the Beta 1 release, this was not working at that point but it has been fixed. We will also support mixed configurations, the final support configuration document has not yet been finalized but our test team has been making good progress validating many of the scenarios we want to support.

 

I would really like to hear how in depth you would like the topics to go into. I can really drill down on a few of them if there is interest. I will start new feature discussions with an overview post like this one to give you a background on the work done for the feature in P12. 

  • How to get schedule variance report in project server 2007. For this actually I need "Actual Start" and "Actual Finish" for this which field i need to refer in Cube.

  • I am running into confusion on Data View content.

    I am using PS 2007 and when I use a data view to look at the Timesheet cube

    data I get a actual worked billable hours of 296.75.

    When I use the MSP_Project_Timesheet cube data I get a value of 73.5 for the

    same time period.

    Any idea of what is going on?

    Thanks for the assistance.

    Doug

  • Is there a way (and if so, can you point me in the right direction) to add a

    custom field to EPM Timesheet cube?

    This cube has a hook into Resource List. We have created a custom field for

    resources called Team Name, which I would like to bring into this reporting cube, if possible.

    When I goto Server Settings, Cube Configuration... step 4 from the

    instructions on how to add custom fields to your cube is below...

    On the Cube Configuration page, in the Cube Dimensions section, go to the

    Cube list and select the cube for which you want to specify dimensions.

    My problem is, I DON"T SEE THE EPM TIMESHEET CUBE in the list of cubes for

    Cube Dimensions...

    all I see in the drop down list is Project, Resource, Task, Assignment

    I DON"T SEE THE EPM TIMESHEET CUBE in the Cube Measures...all I see is

    project, resource, task, assignment.

    Now in CALCULATED MEASURES, I DO SEE EPM Timesheet....

    what gives??? My custom field, uses a lookup table...so...any help out there?

  • Hello, i'm trying to add a Member to the cube MSP_Portfolio_Analyzer. The name is "Availability" and the formula is Availability = [Capacity] - [Work], but when I type the MDX Expression shows an error message about wrong sintax (or somenthing like that...) I´m using PWA in Spanish, so i guess the names of the fields should be in that language but I still get the same result. ¿Could you people help me?

  • I am trying to build cube in 2007...I have 1 project with resources assigned...I have a demand vs. capacity data analysis view built...my time period is 2008, 2009 and 2010, but my capacity only shows for 1 year...I have gone into server settings and changed resource capacity settings to 24, but I still cannot see further data????

    also, can anyone explain when one needs to use each of the cubes...i am not following the need for the multiple cubes and need a primer on differentiating between the various ones.  thanks.

  • ok, doesn't look like my query got posted, so here I go again.

    I am building a cube in 2007.  I have a view that looks at demand (work) and capacity...I want to see 24 months into the future...but when I build the cube I only see 12 months of capacity.  I went to additional server settings and increased resource capacity settings from 12 to 24.  I repbulished, rebuilt cube, but still see only 12 months...I verify capacity for the resources are for a much longer time period.

    Also where can one learn what and when do we use each of the various cubes...it seems everyone can list what they are, but no documentation is listed that provides a laymans description and use of each cube.  it shouldn't be this hard to find this info.  Thanks for your columns and posts.  very helpful insights.

    T

  • Tombzi,

    I have seen the same thing.  I only get resource capacity to show for 12 months in the future and 12 months in the past no matter what time period I use for the cube.  

    I guess I would like to know if this is expected behavior or an issue, because I have not seen any documentation on this.

  • Hi,

    Im New for this, Could you give me a tutorial or links for an overview in each cube. Thanks!

  • Customized Project, Resource and Task enterprise fields, even though available in the field list for the MSP_Project_Timesheet, actaully do not have any numeric values associated with them and therefore are not available in building Data Analysis views using MSP_PROJECT_TIMESHEET.  How can we fix this?  All the documentations shows it should work, but it does not...it is a bit frustating to see that I cannot organize not only my project specific work reported on timesheets, but all my admin time.  I want to use several task and resource attributes to organize my timesheet data for cost accounting purposes.

    T

  • I have a cube report that displays a different date than the date for same Project task.  I've tried changing the date in Project.  I even tried to manipulate the date in the cube report.  Any suggestions/ideas?

  • Was there ever a response posted regarding bringing in a custom field into the EPM_Timesheet data analysis views.  (similar to what Oleksandr posted)

    I've create a custom task level field that I'd like to use to filter/group data similar to seeing hours per project, but instead hours posted to a task code across projects.

Page 2 of 2 (26 items) 12