Custom Fields and OLAP Cubes

Applies to: Office 2010 | Project 2010 | Project Server 2010 | SharePoint Server 2010

Microsoft Project Server 2010 introduces new options for custom fields in OLAP cubes. The options include creating multiple databases for OLAP analyses that can have different sets of custom fields defined for different departments, and adding groups of fields by using built-in measures.

This article includes the following sections:

• Adding Custom Fields to a Cube

• Rules for Custom Fields as Dimensions and Measures

For information about the fourteen cubes available in each OLAP database, see Cube Build Service.

Adding Custom Fields to a Cube

By using Project Web App, you can modify the Project Server OLAP cubes for new data, such as enterprise custom fields, without doing custom development. The Database Configuration page includes options that are new in Project Server 2010 and that can be set for individual databases.

The CubeAdmin class in the Project Server Interface (PSI) includes several methods for reading and updating custom field settings for a specified cube and database. For example, you can use the ReadCubeFieldSettings method to get a CubeFieldSettingDataSet, modify the data, and then use the UpdateCubeFieldSettingForDimension method and the UpdateCubeFieldSettingForMeasure method to make the modifications. Project Web App uses many of the same methods on the Database Configuration page, which can help you to understand what the CubeAdmin methods do.

To change custom fields in an OLAP database, by using Project Web App

1. On the Server Settings page in Project Web App, click OLAP Database Management.

2. On the OLAP Database Management page, select the row that contains the OLAP database that you want to modify, and then click Configuration.

   The Database Configuration page for the selected database shows the options available for adding custom fields (Figure 1).

3. On the Database Configuration page, select each entity (Project, Resource, Task, or Assignment) from the drop-down list in the Cube Dimensions section and the Cube Measures section.

4. Add the available fields for each entity that you want to use, for the dimensions and measures.

5. Select the groups of fields that you want to use in the Built-in Measures section.

6. The Calculated Measures section uses the ReadCalculatedMemberDefinitions method and the UpdateCubeCalculatedMemberDefinition method to read and update Multidimensional Expressions (MDX) scripts for calculated measures in any of the fourteen cubes that are available.

Figure 1. Adding custom fields to OLAP cubes

The calculated member expression must use the Analysis Services MDX format. If the MDX script has an error, the cube does not build. For example, you can create a calculated member with the custom field name [Work Variance], where the MDX expression is [Work] - [Actual Work].

Calculated members can be very useful, for example, where an organization has its own definition of resource availability that differs from the default Project Server definition. The organization can define resource availability for internal reports that use any of the Project Server non-timephased cubes. You can also rename a field by using a calculated measure or create custom definitions for fields, such as resource capacity. For more information, see How to: Calculate Resource Availability in OLAP Cubes.

Rules for Custom Fields as Dimensions and Measures

You can use custom fields as dimensions or measures. For example, you can use a Number custom field as a measure to sum values along a task dimension. The Project Server Cube Build Service (CBS) has many rules for including custom fields in OLAP cubes. Figure 2 and Figure 3 summarize CBS features and the limitations for adding custom fields to OLAP cubes. The CBS rules include the following:

• Custom fields, in general:

  • Cannot be added as measures for the timephased cubes.

  • Cannot be added with formulas.

• Dimensions:

  • Support only custom fields with lookup tables, with the exception of Flag custom fields.

  • Support Flag custom fields, which cannot have a lookup table.

• Task custom fields:

  • If the task custom field has a lookup table, it cannot roll up to summary tasks and cannot be used as a measure.

• Measures:

  • Support only Cost, Duration, and Number custom fields. Those custom fields for Resource and Project entities can be measures.

  • If a Cost, Duration, or Number custom field for a Task entity has a lookup table, it cannot be a measure.

• Multivalue custom fields:

  • Are not supported in the cubes.

    Tip

    If the Project Departments custom field and Resource Departments custom field are configured to accept multiple values of the Department lookup table, those custom fields are not shown in the Available fields lists. However, you can filter the OLAP database by one or more departments. If multiple values are not allowed in the Project Departments custom field and Resource Departments custom field, you can add those custom fields to a cube.

    The Department lookup table can be hierarchical or single-level. In either case, the limitation is whether the Project Departments custom field or Resource Departments custom fields is multivalue.

Figure 2 summarizes the CBS rules for adding custom fields as dimensions to the OLAP cubes. In the custom field attributes, None means the custom field has no lookup table. For example, the only type of custom field that can be a cube dimension is a Flag custom field (of either a Project, Task, or Resource entity).

The Roll up section for Task Summary Rows applies only to Task custom fields; in that case, None means that the Task custom field is not rolled up to task summary rows. For example, a Duration custom field for a Task entity that has no roll up can be a cube dimension. However, if the Task Duration custom field has any of the available roll up parameters, it cannot be added as a cube dimension.

Figure 2. Cube Build Service support for custom fields as dimensions

On the Cube Configuration page, the drop-down lists in the Cube Dimensions section and the Cube Measures section include Project, Resource, Task, and Assignment entities. If a custom field satisfies all of the CBS rules for a dimension or measure, and it is a global custom field or a departmental custom field that matches one of the departments specified for the OLAP database, then the custom field shows in the Available fields list for the selected entity. If none of the custom fields satisfy the CBS rules, or if the custom fields are defined for a different department than the OLAP database includes, then the Available fields list (Figure 1) is empty.

Figure 3 summarizes the rules for adding custom fields as measures to the OLAP cubes. To check some of the CBS rules, create lookup tables and custom fields by using the Enterprise Custom Fields and Lookup Tables page in Project Web App. For example, first create a lookup table named TaskDur, and then create the three Task custom fields, as shown in Table 1.

Table 1. Sample task custom fields

On the Cube Configuration page, select the different cubes to see which of the custom fields in Table 1 are available for dimensions and measures. If the custom field is listed, that qualifies as a YES in Figure 2 and Figure 3. Table 2 shows where you can use the example custom fields.

Table 2. Usage of sample custom fields

Project Web App appends the entity name or abbreviation to a custom field name where necessary to help select the correct custom field. For example, if you create a resource Cost custom field named Department Tax that has a rolldown for assignments, the list of available fields for the Assignment cube dimension shows both Department Tax_R_Assignment and Department Tax_Resource. The list of available fields for the Assignment cube measures shows Department Tax_R.

Figure 3. Cube Build Service support for custom fields as measures

See Also

Concepts

Using Formulas and Graphical Indicators with Custom Fields

Custom Fields and the Reporting Database

Other Resources

Cube Build Service

How to: Calculate Resource Availability in OLAP Cubes