Project Server 2010: OLAP–where are my Custom Field Dimensions and Measures?

This question comes up reasonably frequently – I have added dimensions and measures to my cube but do not see them.  Where are they?

The answer has a few slight variations, and depends which cubes you are expecting to see them in – and how those cubes are constructed.   Most of this also applies to 2007 (apart from the department issue) but this is written with 2010 in mind.

The first thing to note is the text on the configuration page for the cubes:

Cube Dimensions Specify the custom fields you want to add to the OLAP database as dimensions. The selected custom fields will be added to both timephased and non-timephased cubes, when applicable

Cube Measures Specify the custom fields you want to add to the cube as measures. The selected custom fields will be added to the related Non-Timephased cube.

The devil is in the detail, but this could be the reason they are not appearing – you are expecting to see the data in a time-phased cube when this is not applicable.  Particularly this applies to measures, as these are just values that apply to the specific entity and have no timephased spread – so they cannot be presented in a timephased manner.

The usual cube that these dimensions and measures are expected in is the MSP_PORTFOLIO_ANALYZER cube.  However, this cube is actually a virtual cube constructed from the Assignment and Resource Timephased cubes – so don’t expect any measures here!  Also if you have added a project level custom field to the project cube then it will not be here either.  You can however add that same project level field to the Assignment cube and it will be available.  The other virtual cubes are:

  • MSP_Project_SharePoint – Including Deliverables, Issues, Risks and the Project Non Timephased cubes
  • MSP_Project_Timesheet – including the EPM Timesheet and Resource Timephased cubes

The final ‘gotcha’ for missing measures comes from the new Department feature in 2010.  If you have a Custom Field that you want to add as a measure, and that Custom Field has been scoped to a specific Department then it will only appear as a measure for cubes buit for that specific department.  If you build a cube with no departmental filtering it will not show up – even though there may be project from that department included in the ‘full’ cube.  The UI for the cube configuration doesn’t help with understanding this last issue – as the measures are not filtered based on the department.  This is because the department filtering is applied in one screen and measures in another.  And just a reminder – measures are only available for Cost, Number and Duration custom fields.

Every added dimension and measure will add extra data to the cube, make the build slower and the cube bigger, so don’t just choose everything – be selective.  Also with 2010 you have the option to leave out some of the usually built in measures such as Costs, Work, Earned Value and the various baselines.

Technorati Tags: ,