NOTE
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.
TIP
OLAP cubes generated by Microsoft Project Professional can be used by other reporting tools. You can use the tutorial and online documentation (available on Microsoft’s SQL Server 2000 installation CD) to familiarize yourself with OLAP cubes and XML blobs.
NOTE
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. But don’t worry. You can define an enterprise project outline code called Draft, with the attributes Yes and No, that 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, the new view automatically binds to the name of the current OLAP cube. There is only one copy of a named OLAP cube available, so the view will always get its data from that named cube. If a new OLAP cube is generated with a new name, the view continues to obtain its data from the OLAP cube it is bound to.
Portfolio views do not automatically bind themselves to the most recent OLAP cubes. Portfolio Analyzer view data is as current as the OLAP cube it is bound to.
Although you can create multiple, different, OLAP cubes, there are no utilities in Project Professional to help you manage them or the Portfolio Analyzer views that bind to them. You can use a PivotTable’s Commands and Options dialog box (Data Source tab) to change the OLAP cube that the view is currently bound to.
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.
If you open Web Access and select Admin, Manage Enterprise Features, Specify Resource and OLAP Cube Updates, you’ll 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. | |
2. |
Modify the parameters on the Updates to Resources and OLAP Cube screen, as shown in Figure 28.1. Figure 28.1. Creating an OLAP cube automatically updates the resource tables in a Project Server database.
|
3. | |
4. |
Enter the OLAP 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 data 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 one of the following options:
|
7. |
Enter the data range to be used when updating the Resource Availability tables. The Date Range for Resource Availability Tables 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:
|
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:
|
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.