How to: Calculate Resource Availability in OLAP Cubes
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
To be well planned, resource capacity and availability reports must meet the needs of your entire organization. Microsoft Office Project Server 2007 gives you the flexibility to define how to calculate resource availability according to your organization's work model. This article shows how to add custom availability measures using Multidimensional Expressions (MDX) scripts in the Project Server OLAP cubes. (The content of this article was contributed by Lidiane Souza, Microsoft Corporation.)
Calculating Availability
By default, when Project Server builds the cubes, several resource measures support the creation of different resource usage reports. For example, the Resource Timephased cube contains timephased resource capacity and base capacity (also known as baseline capacity). The Assignment Timephased cube contains all task work, actual work, and overtime work, among other measures. Both cubes contain the resource list dimension that enables you to create separate reports on work accomplished, work planned, and resource capacity.
A common report requirement is to combine data in the Resource Timephased and Assignment Timephased cubes, and show resource availability across multiple projects and teams in your organization. If this is one of your requirements, use the Portfolio Analyzer cube (MSP_Portfolio_Analyzer), which includes all of the Assignment Timephased and Resource Timephased cube data.
Unlike Microsoft Office Project Server 2003, the MSP_Portfolio_Analyzer cube in Project Server 2007 does not have an availability measure by default. Because availability is calculated differently in each organization, Project Server 2007 gives you the flexibility to define how to calculate resource availability by using Project Web Access to configure the cube.
**Finding the Best Formula **You can use MDX scripts in SQL Server Analysis Services to exactly define the resource availability calculation that works best for your organization. The following three MDX scripts are examples that show different ways to calculate resource availability.
Following is the most basic formula for calculating resource availability:
Availability = [Capacity] - [Work]
The rest of this article refers to the previous formula as temporary availability. The result of the temporary availability calculation includes both active and inactive resources in your organization, regardless of whether or not they have assigned work.
.gif "Note") Note |
|---|
You must enter the MDX formulas all on one line in Project Web Access, or they do not work correctly. Here, they are broken into multiple lines only for readability. |
If the entire pool of resources is too extensive, you can restrict the results to return only the availability of resources who have assigned work, as follows.
Availability = iif(IsEmpty(Sum([Time].[Year].members,[Measures].[Work])),
null, [Measures].[Temporary Capacity])
In some cases, the previous formula filters out too many resources. For example, if your organization has new resources who are not yet assigned any work, the availability view or report does not show the new resources.
To include new resources in the calculation, use the following formula:
Availability = iif(IsEmpty(([Resource List].[All Resource List],
[Time].[All Time], [Measures].[Work])) AND
IsEmpty(([Resource List].[All Resource List], [Time].[All Time],
[Measures].[Temporary Capacity])),
null, [Measures].[Temporary Capacity])
The IIF function evaluates to false only if the value of the expression is zero. For any other expression value, IIF evaluates to true. The IsEmpty function returns true if the evaluated expression is an empty cell value. Otherwise, IsEmpty returns false. The IIF and IsEmpty MDX functions help to evaluate resource and time data, and to filter the data.
Validating MDX Syntax If you modify the examples or write your own MDX formula to calculate resource availability, ensure that the MDX syntax is correct.
.gif "Important note") Important |
|---|
If you enter an invalid formula for any of the Project Server cubes, the cube build process fails. Be sure the formulas do not contain line breaks. |
In SQL Server Analysis Services 2000, you can use the MDX Sample application to validate the syntax. In Analysis Services 2005, you can use the Enterprise Manager to select an MDX query, recreate the query, and then validate the syntax.
In addition to validating the MDX syntax, it is also good practice to verify that the measure behaves the way you want in a test cube before you add the query and measure to the production cube. You can make modifications before you make the cube available to the rest of the organization.
After you write and validate the MDX formula, use the following procedure to add it to the appropriate cube.
To add an MDX formula to the cube:
Start the Project Web Access instance you need to add the resource availability formula to.
Open the Server Settings page, in the Cube section, click Configuration.
In the Calculated Measures section of the Cube Configuration page, select MSP_Portfolio_Analyzer in the Cube drop-down list, and then click Insert.
NoteMSP_Portfolio_Analyzer is the only cube that contains work and capacity measures.
In the Member Name column, type Availability (or another name you want). Do not type brackets around the name.
In the MDX Expression column, paste your validated MDX formula.
Click Save.
The next time Project Server builds the cubes, the Availability calculated measure will be present.