Dos and Don’ts of OLAP Reporting – Configuration Guidelines & Best Practise

I am often asked why certain custom fields don’t appear in OLAP reports/views. Here are some things to bear in mind. Hope this helps.

Dos

Carefully read the customization rules (detailed below) prior to specifying custom fields as dimensions or measures. More to the point, before specifying/designing custom fields for your reporting needs it very important to understand these rules.

Don’ts

Do not simply add all the custom fields to the cube i.e. more than actually needed for your reporting purposes. If you are not sure and add many unnecessary custom fields to the cube data, this will adversely affect performance.

Customisation Rules

Specifying dimensions and measures for cubes involves the use of Project Server custom fields. There are some restrictions when using custom fields as dimensions and measures. You must observe customization rules when adding a custom field as a dimension or measure to a Office Project Server 2007 OLAP cube. These rules include:

Custom Fields as dimensions

• Must have roll up set to ‘None’
• If type is TEXT must have a look up table. Dimensions support custom fields with lookup tables
• Dimensions support Flag custom fields (which cannot have a lookup table).
• Multi-value custom fields cannot be included in a cube. They are not supported because of a limitation in Analysis Services.

Custom Fields as measures

• Measures support only Cost, Duration, and Number custom fields.
• Must have roll up set to ‘Sum’
• Date, Flag and Text CF are not supported as measures
• You cannot add custom fields with formulas.
• Use Calculated Measures for Minimum, Maximum, Average…
• Task custom fields with a lookup table cannot roll up to summary tasks and cannot be used as measures.
• Only custom fields without lookup tables can be measures for Task entities. Custom fields either with or without lookup tables can be measures for Resource or Project entities.
• If Task custom fields have a Sum rollup for task summary rows, they can be measures.

You cannot add custom fields as measures for the time-phased cubes.

Fields that have an Assignment roll-down and attributes set to None can be dimensions or measures.