Set up and manage OLAP cubes for the Portfolio Analyzer

Portfolio Analyzer Online Analytical Processing (OLAP) cubes are a powerful reporting and analysis feature in Microsoft Office Project Web Access that enables you to perform complex analysis of project data. OLAP cubes contain task, resource, project, assignment, issues, risks, and commitments information that team members can readily browse by using PivotTable data and PivotChart views within the Portfolio Analyzer page.

Microsoft Office Project Server provides a number of preconfigured OLAP cubes that you can use to explore resource and task information. The process of scheduling the building, specifying the fields, and customizing the OLAP cubes occurs within Project Web Access and involves choosing Microsoft SQL Server 2005 Analysis Services, creating a schedule to generate the OLAP cube, and optionally adding custom fields to each cube. Adding these custom fields enables you to extend the Project Server OLAP cube by adding, for example, a language dimension for the resources cube. Or you might want to show more corporate data by adding measure fields for nonproject costs to the OLAP cube.

Note: Setting up and managing an OLAP cube requires that you first set up Microsoft SQL Server 2005 Analysis Services and set its permissions.

What do you want to do?

Create a schedule for building OLAP cubes

Specify the fields (dimensions) to use for the OLAP cubes when using Data Analysis views

Customize the predefined OLAP cubes

Create a schedule for building OLAP cubes

1. On the Quick Launch, click Server Settings.

2. On the Server Settings page, click Build Settings.

3. In the Analysis Services Settings section, type the name of the Analysis Services server in the Server box.

4. Type the name of the database that is used by Analysis Services in the Database box. If the database doesn't currently exist, one is automatically created.

   Optionally, type the extranet address that is used by Analysis Services in the Extranet URL box. You can also type a description for the extranet URL location of the Analysis Services server in the Description box, if you need to distinguish it from other Analysis Services servers.

5. In the Database Date Range section, select the date range of data that you want to include in the OLAP cube:

   • If you select Use the earliest project start date and the latest project finish date, the cube is built so that it includes the earliest date and the latest dates on tasks in the project.

   • If you select Use the following last and next time units to calculate the date range at the time the cube is built, in the Last and Next boxes, indicate the number of days, weeks, or months before and after today's date that you want to use in the OLAP cube.

   • If you select Use the fixed date range specified below, specify a date in both the From and To fields.

6. Under Cube Update Frequency, choose how frequently you want to refresh the data in the cube.

7. To start building the OLAP cube, click Save and Build Now. Otherwise, click Save and the cube will be updated based on the schedule that you chose in the Cube Update Frequency section.

Tip: To check the build status of OLAP cubes, click Server Settings, and then on the Server Settings page, click Build Status.

Top of Page

Specify the fields (dimensions) to use for the OLAP cubes when using Data Analysis views

1. On the Quick Launch, click Server Settings.

2. On the Server Settings page, click Manage Views.

3. Click New View, and then in the View Type section, click Data Analysis.

4. Type a name and description for the view that team members will see in the Portfolio Analyzer page in the Name box and Description box.

5. In Analysis Services Settings section, enter either the default Analysis Services server that your organization uses for cube building or another Analysis Services server. If you select another server, enter its name and optionally its extranet address.

6. In the Analysis Services Database list, select the database that you want to use.

7. In the Cube list, select the cube that you want to use.

   Note: If you selected a custom Analysis Services server, click Apply to see a list of the databases and cubes on that server.

8. In the View Options section, select how you want to display information from the cube:

   • Click PivotChart to display the information in graphical form.

   • Click PivotTable to display the information in tabular form.

     Note: You can also display PivotChart views on top of PivotTable views by selecting PivotChart and PivotTable. Under View Definition, you can preview the results.

9. Click PivotChart and PivotTable to display the information both in tabular and graphical form.

10. Select the Show Field List check box to display a list of fields that you can add to the PivotChart data or PivotTable views.

    The PivotTable Field List dialog box will appear. Fields that are listed under Totals are dimension fields that can be added to the central area of the PivotChart view or PivotTable view. All other types of fields are dimension fields that you can add to the Column, Row, and Filter areas of the PivotChart view or PivotTable view, depending on how you want the team members to view and categorize the data.

11. Select the Show Toolbar check box to show a formatting toolbar on top of the PivotChart view or PivotTable view.

12. To specify the security categories in which the PivotChart view or PivotTable view belongs, in the Security Categories section, select a category in the Available Categories list, and then click Add.

13. Click Save to create the view.

Top of Page

Customize the predefined OLAP cubes

You can apply your organization's custom fields to pre-defined OLAP cubes so that they display the information that your team members need to further analyze the data.

1. On the Quick Launch, click Server Settings.

2. On the Server Settings page, click Configuration.

3. In the Cube Dimensions section, select the cube that you want to customize. In the Available fields list, select the custom fields that you want to add to the cube as dimensions, and then click Add.

   The fields that you add to the cube's dimension are added to the Selected dimensions list.

   Note: The list of fields contains only the fields that have been customized for your organization.

4. In the Cube Measures section, click the cube that you want to customize. In the Available Fields list, select the custom fields that you want to add to the cube as measures, and then click Add. The fields that you want to add to the cube's measures are added to the Selected measures list.

   Note: The list of fields contains only the fields that have been customized for your organization.

5. Click Save to update the OLAP cube with the new field information. The next time the OLAP cube is generated, it will contain this new information.

Note: If you added a custom field that doesn't contain any data that is associated with it, the custom field will not be displayed in the cube.

You can further customize the cube's fields by creating a Multiple Dimension Expression (MDX) script to manipulate information within the cube's fields.

1. Under Calculated Members, select the OLAP cube for which you want to create the calculated MDX script.

2. To select an MDX expression, click Insert .

   You can also delete an expression by selecting the row and clicking Delete.

3. Under Member Name, type a name for the calculated member.

4. Under MDX Expression, type the MDX script that defines the member.

5. Click Save to apply the MDX script to the OLAP cube. The OLAP cube will contain this information the next time you generate the cube.

Why can't I perform some actions in Microsoft Office Project Web Access?

Depending on the permissions settings you used to log on to Project Web Access, you may not be able to see or use certain features. Also, what you see on some pages may differ from what is documented if your server administrator customized Project Web Access and did not customize the Help to match.

Top of Page