Building OLAP Cubes and Updating Resource Tables

 < Day Day Up > 

Project Server uses Online Analytical Process (OLAP) technology to generate OLAP cubes and then stores the information in Extensible Markup Language (XML) for presentation and loading into the Project Center pages for the Portfolio Analyzer views. Also, Project Server updates the resource tables for viewing and reporting based on parameters you specify.


OLAP databases are called cubes because they combine the analysis of data from several dimensions, such as project, resource, and time, with summarized data, such as work and availability.

OLAP is designed for ad hoc data reporting. It is a way to organize large business databases. OLAP cubes are organized to fit the way you retrieve and analyze data so that you can easily create the reports you need.


OLAP cubes generated by Microsoft Project can be used by other reporting tools. You can use the tutorial and online documentation (available on Microsoft's SQL Server installation CD) to familiarize yourself with OLAP cubes and XML blobs.


All new projects saved to a Project server must be saved with the version Published first. That is, if you want to save a Draft version of a project schedule to the server, you must first save a Published version.

In addition, there is no way to exclude a project that has a Published version when generating an OLAP cube. Therefore, Draft schedules published to a Project server are included when OLAP cubes are generated. You can define an enterprise project outline code called Draft, with the attributes Yes and No, which you can use to filter out all draft versions when using the Portfolio Analyzer. Or you can use the Project dimension in the OLAP cube data to manually include or remove the specific projects that you're interested in analyzing.

When you create a Portfolio Analyzer view, you have the option to target the view to a Project Server OLAP cube on the current or a different server. You can also bind the Portfolio Analyzer view to one of several possible Project Server OLAP cubes. This flexibility allows you to create several Portfolio Analyzer views that point to a variety of OLAP cubes.

Each Portfolio Analyzer view data is as current as the OLAP cube it is bound to. The Project Server Administrator must schedule Project Server OLAP server cube updates. Although you can create multiple, different OLAP cubes, there are no utilities in Project Server to help you manage them or the Portfolio Analyzer views that bind to them. If you want to create multiple Project Server data cubes within a single Project Server instance, then the Project Web Access Administrator must manage cube regeneration using manual techniques.


You can use the Portfolio Analyzer view PivotTable Commands and Options dialog box (Data Source tab) to change the OLAP cube that the view is currently bound to.

Before Building an OLAP Cube or Updating Resource Tables

Before creating an OLAP cube or updating resource tables, all the following must be true:

  • The Microsoft Project Server must have Analysis Services installed as part of the SQL Server 2000 product installation.

  • Enterprise-level project or resource outline codes must have been created and stored in the Enterprise Global file.

  • Resources must have been added to the enterprise resource pool and assigned enterprise resource code values. For example, to be able to perform skill-based analysis using an OLAP cube, you must have defined a Skill enterprise resource outline code and assigned Skill values to the resources in the enterprise resource pool.

  • All projects to be included in the update must have been created using enterprise resources, and the projects themselves must have been published to a Project server.

Building an OLAP Cube and Updating Resource Tables

If you open Project Web Access and select Admin, Manage Enterprise Features, Specify Resource and OLAP Cube Updates, you will notice that similar input screens are used for two different purposes:

  • To generate an OLAP cube that provides the data used by the Portfolio Analyzer.

  • To update resource data. Resource data, including resource availability data, is stored in tables in the Microsoft Project Server database. These tables, which need to be updated to get new data, are used by tools such as the Build Team from Enterprise tool in Project Professional. Note that building an OLAP cube automatically updates the resource table data.

To generate an OLAP cube, you need to perform the following steps:

  1. Log on to Microsoft Project Web Access as the administrator and select Admin, Manage Enterprise Features, Specify Resource and OLAP Cube Updates.

  2. Modify the parameters on the Updates to Resources and OLAP Cube screen, as shown in Figure 25.5.

    Figure 25.5. Creating an OLAP cube automatically updates the resource tables in a Project Server database.


  3. Select the Yes radio button if you want an OLAP cube to be built when either the Update Now button is clicked or the scheduled time has expired . Select the No button if you just want to update the resource tables.

  4. Enter the OLAP analysis server name. This is the name of the machine that hosts the analysis server. This option appears only if you are building an OLAP cube.

  5. Enter a name for the OLAP cube, as well as a brief description. This option appears only if you are building an OLAP cube.

  6. Enter the date range to be used when creating the OLAP cube. The Date Range section controls the scope of the project data that is included in graphs and OLAP cubes. This option appears only if you are building an OLAP cube.

    The OLAP cube building process requires a starting date and an ending date from any of the three available Date Range options. You can select an option by marking the radio button next to one of the following options:

    • Use the earliest project start date and the latest project finish date This option provides a date range that spans the entire time period for all projects within a Project Server database. It selects the first date that any project starts and the last date that any project finishes.

    • Use the following date range at the time the cube is built This option provides a moving window across the project portfolio. The Values, The Next, and The Past define the width of the window as it moves across the data in time.

      If you choose this option, enter the number of time periods that will define the beginning and ending dates of the moving window. The time periods can be specified in days, weeks, or months. When the update process is executed, the start date is calculated from the date and time of the run and the value from the past. For example, if the update process runs on 6/1/2004 and the Past value is one month, the start date is 5/1/2004. Similarly, the ending date is calculated from the current date and time, with the Next value added to it.

    • Use the fixed date range specified below This option provides a fixed window across the project portfolio. The From and the To values define the width of the window. This date window does not move with time.

      With this option, both dates are selected from a calendar drop-down list box. You select the required date from the calendar or by typing it directly into the box.


    Use the Fixed Date Range option with care. It is very easy for this date to be changed, then not changed back, and for data to be reduced without it becoming apparent. This can in turn affect business decisions that are based on the data.

  7. Enter the date range to be used when updating the Resource Availability tables. The Date Range for Resource Availability section controls the scope of the resource data that is included in resource tables. This option appears if you are building an OLAP cube and if you are updating the resource tables.

    To update the resource availability tables, you need two dates: the starting date for update and the ending date. You can select from two options by marking the radio button next to the desired option:

    • Use the Following Date Range for Retrieving Resource Availability Information

    • Use the Fixed Date Range Specified Below

  8. Enter the update frequency to be used for creating or updating an OLAP cube. The Update Frequency option appears if you are building an OLAP cube and if you are updating the resource tables.

    There are two ways of maintaining the update frequency of OLAP cube data:

    • Scheduled If the Update Every radio button is selected, the frequency of the update is defined by the associated number of time periods, type of time period, and start date. First you need to select the elapsed time period between executions of the update process. The drop-down list box defines the number of time periods and whether the time period is days, weeks, or months. Next, you need to select the date and time for the first run in the Start Update On drop-down list boxes. The first run of the update process occurs on the date and time selected; the second and subsequent runs occur after the defined interval has elapsed.

    • Update Now To update the OLAP cube immediately, select the Update Only When Specified radio button. In this case, the data is updated only when you navigate to this page and click Update Now.

      After you click the Update Now button, you need to wait for the OLAP cube to finish building before you attempt to access the data. This might take several minutes, depending on the number and size of projects and resources included in the OLAP cube. After you click the Update Now button, you can determine when the update process has completed by clicking the Refresh button on your browser. When the update process has completed, you receive the message "The cube was successfully built on mm / dd / yy at hh : mm " (or an error message) in the Current Cube Status section, as shown in Figure 25.5.

  9. Click the Save Changes button to save the options selected and initiate any changes to the update frequency.

Although it might seem reasonable to assume that a higher frequency of data update is preferred, that is not often the case. Resource management tends to follow a cycle, as does updating of project plans. The update process should follow that process.

In many organizations this cycle is managed on a weekly basis. Timesheets are updated on a weekly basis, and project plans are updated to reflect the impact reported in the timesheets, together with any other changes on the same frequency. These updated plans are saved as the Published version of a project. Reporting and analysis need to be constant during this update cycle. A weekly update cycle should provide the timeliness necessary to analyze the data and to make informed business decisions.

 < Day Day Up > 

Special Edition Using Microsoft Office Project 2003
Special Edition Using Microsoft Office Project 2003
ISBN: 0789730723
EAN: 2147483647
Year: 2004
Pages: 283
Authors: Tim Pyron

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: