Analyzing Portfolio Data in the Portfolio Analyzer

The Portfolio Analyzer can be a powerful tool that provides people with a lot of combined information about projects and resources. Portfolio Analyzer views are typically used by managers who need to see a mix of project and resource data that are part of a family of information such as programs or portfolios of projects.

The settings you design and configure within the Enterprise Global and PWA views allow managers to see information about projects that are logically part of a grouping of projects based on Enterprise Global Project Outline Codes that you define. Enterprise Global Resource Outline Codes are also used to group and filter information about resources within each Portfolio Analyzer view. The combination of Enterprise Global definitions is therefore an important factor for people who use Portfolio Analyzer functions.

Portfolio managers typically need to see a mixture of project and resource information so that they can begin to answer questions such as the following:

• How much work is scheduled for projects within a certain portfolio of projects related to sponsors, billing centers, or other key business attributes?

• How many hours are scheduled for generic and named resources that have certain skills and other attributes within a particular time span?

• How much actual work has been accumulated for projects within particular portfolios of projects?

• What are the accumulated costs compared to baseline predictions across multiple projects?

These and other questions can be answered using Portfolio Analyzer views that you create and grant user permissions for people in the organization.

Each view can display various formats of chart data driven from the details of a pivot table that you define for specific views. The chart data can be exported to GIF graphic files to be included within Microsoft Word or other documents such as portfolio status reports. The pivot table data can be exported to Microsoft Excel for additional analysis or discussion with people who do not have PWA access.

The permissions that you grant to PWA users allow them to see and interact with specific Portfolio Analyzer views. These permissions also enable or disable the use of certain viewing details to protect sensitive business data such as project or resource costs. You can also set viewing conditions to enable or disable users from temporarily altering the view details. Portfolio Analyzer provides advanced viewing and analysis functionality, so you need to understand how this tool functions. The choices you make provide managers throughout the organization with information they need to make decisions about portfolios of projects and resources.

Building and Changing Portfolio Analyzer Views

Prior to creating new Portfolio Analyzer views, it is important for you to understand some of the technology parts. This section introduces you to these technology parts, building your base knowledge and instincts before creating Portfolio Analyzer views.

The Portfolio Analyzer Uses SQL Analysis Services OLAP Cube Data

SQL Analysis Services is one of several core technologies behind views within the Portfolio Analyzer views. SQL Analysis Services is connected to Project Server during the software installation process, so PWA views can use Online Analytical Processing (OLAP) data stored within a separate SQL database.

Figure 17.3 shows a simple flow diagram that depicts how Project Server and SQL Analysis Services are connected. The left side of the diagram shows the basic steps that Project Server takes during the OLAP rebuild process. Project Server generates instructions.

Figure 17.3. Overview of OLAP cube rebuild flow.

The OLAP cube build process uses a staging technique whereby Project Server creates the instructions used by SQL Analysis Services to regenerate OLAP cube data that includes Dimensions based on Enterprise Global Codes and resource assignment data, with Measures based on details such as work, actual work, costs, and so on. When the OLAP cube is regenerated, it contains project and resource information as of the OLAP rebuild time. Therefore, the data within the OLAP cube is a snapshot of Project Server content and is updated only when that OLAP data is regenerated.

CAUTION

Figure 17.3 also shows how the previous OLAP cube is completely cleared of previous data, so any edits or customizations you added to the OLAP cube data are lost during the rebuild process.

The OLAP regeneration process also updates other Project Server information, such as resource availability measurements, viewable within the Resource Center functions such as View Availability. This availability information is stored within other Project Server database tables that are not part of the OLAP cube data.

PAGE 209.

NOTE

If you want to learn more about the fundamentals of OLAP and Microsoft SQL Server Analysis Services, consider reviewing the following book available from Sams publishing:

Microsoft SQL Server 2000 Unleashed, Second Edition, by Ray Rakins, Paul Bertucci, and Paul Jensen, ISBN: 0672324679.

Office Web Components Are Needed for Portfolio Analyzer Views

Office Web Components (OWC) are used within Internet Explorer pages to show information in the Portfolio Analyzer views. These ActiveX components provide nice display functions within the pivot table and related graph that show data within each Portfolio Analyzer view.

CAUTION

The first time a user accesses the Portfolio Analyzer views, Project Server automatically downloads the ActiveX OWC components so that the pivot table and chart can be displayed. This download is a transparent activity that is generally hidden from the user. The download process also requires that some files be stored within the user's desktop computer Windows file system and a software registration function is performed to create Windows Registry entries. These actions require the user to have Administrator rights on the desktop machine.

Microsoft ActiveX components and other software upgrades to user desktops can be done interactively or by scheduled software changes using technology such as Systems Management Server (SMS). SMS allows Windows system administrators to automatically send software and updates to user desktop systems.

TIP

If your users do not have personal settings that grant them Administrator rights on their desktop machines, consider doing a Windows SMS file push. Alternatively, you can have a person with Administrator rights log on to the user's desktop machine and access the Portfolio Analyzer views within PWA. The OWC ActiveX functions will be stored within the Windows file system for any user on that machine.

NOTE

To find more information on SMS, see the Microsoft website at http://www.microsoft.com/smserver/default.asp

After the OWC functions are downloaded, Project Server directs Internet Explorer to use these ActiveX functions to show pivot table and/or graphs within the Portfolio Analyzer views. This enables users to see the data dimensions and measures you designed for Portfolio Analyzer views without having a licensed Office 2003 product.

The OWC pivot table functions behave much like Excel, so you can interact with the data and export the data to Excel. The internal formula values are not accessible, so you cannot modify those formulas within Portfolio Analyzer pivot table sections. You can, however, add your own custom calculated formulas as described later in this chapter.

The OWC graphic chart functions also behave much like Excel and are directly linked to the data Dimension columns, rows, and measures values within the pivot table of each Portfolio Analyzer view. When the user interacts with the Drop Area drop-down lists, the pivot table and chart displays are simultaneously updated.

NOTE

Even though the OWC functions are installed on the user's computer, this does not allow the user to modify Portfolio Analyzer views. The computer must have one Microsoft Office 2003 product to modify Portfolio Analyzer views.

End Users Need Permission to View Data Within the OLAP Cube

Each Portfolio Analyzer view connects to a specific OLAP cube for the data displayed within the view. People in the organization need to have two permission settings to use these views:

• Project Web Access Category Permissions are needed with appropriate settings that include the name of the Portfolio Analyzer view. This is typically done by assigning each user to appropriate PWA Groups with associated Category permissions.

• SQL Analysis Services OLAP cube Permissions must be set to enable read access to the data within the cube. The SQL Analysis Services manager must use the Manage Roles function to establish permission roles. Figure 17.4 shows examples of OLAP cube Manage Roles functions where several role groups are defined with individual authenticated user identifiers specified.

  Figure 17.4. Example of SQL Analysis Services OLAP Cube Manage Roles definition.

  
  
  

After you add users to the OLAP cube roles you have defined, use the Cubes tab to mark which items within the cube should be accessed (see Figure 17.5). Make sure that you check each of the cube names because those are used within the Portfolio Analyzer views.

Figure 17.5. Example of SQL Analysis Services OLAP cube settings.

TIP

Consider using the Windows Everyone security group to allow everyone to use the OLAP cube data. This reduces the overall management of the SQL Analysis Services roles because you will not need to maintain specific groups of usernames.

CAUTION

If your PWA Admin settings and processes use more than one OLAP cube for Portfolio Analyzer views, you need to use the SQL Analysis Services Manage Roles function to grant read permissions for each defined OLAP cube. If individual users receive a permissions error while attempting to display a Portfolio Analyzer view, you should inspect the settings of the OLAP permissions.

PAGE 143.

Overview of OLAP Cube Dimensions and Measures

SQL Analysis Services creates and manages the OLAP cube used within the Portfolio Analyzer views that are part of PWA. During the installation of Project Server 2003, the OLAP cube data is set to be stored within regular SQL Server databases that are later filled with project and resource data from the Project Server database. Each time the OLAP cube is regenerated from within PWA, the data becomes the core for each Portfolio Analyzer view.

NOTE

Enterprise Global Project Outline Codes are automatically included within the OLAP cube rebuild process after at least one project is published using those codes. If you later add a new outline code to the Enterprise Global, you must publish at least one project schedule with a value from the new code items before that outline code is included within the OLAP cube.

The Project Server OLAP cube contains certain information that has been compiled from data about projects, resources, resource assignments, and values, such as work and cost. These data also include cross-references to attributes about projects and resources such as Enterprise Global Outline Codes that your configuration has defined. The attributes about projects and resources are known as Dimensions within the OLAP cube.

Dimensions serve as the primary method to filter and group information about projects and resources when you create a Portfolio Analyzer view. Figure 17.6 shows the Field List box that contains typical Dimensions available for use within a Portfolio Analyzer view. These Dimensions can include attributes such as Projects (names of each project), Time (Years, Quarters, Months, and Days), Resources (names of each resource), and other attributes you define within the Enterprise Global Project and Resource Outline Codes.

Figure 17.6. Portfolio Analyzer Field List box example.

As you drag and drop the Dimensions from the Field List box to the row, column, or filter areas of the pivot table, those Dimensions become part of the cross-references to the Measures of data in the OLAP cube.

CAUTION

As of Project Server 2003 SP1, the OLAP cube data does not include information about tasks within project schedules. This means that task-level attributes and details are not available to see within Portfolio Analyzer views.

Measures are values that can be accumulated down columns and across row totals within the pivot table. These values include predefined values such as Work, Baseline Work, Actual Work, Cost, Availability, and so on. Figure 17.6 also shows some of these values within the Field List box. These values can be dragged into the Totals and Fields data area of the pivot table where the accumulated values show as column and row totals.

Dimensions also include the Enterprise Global Custom Outline Codes you have defined for projects and resources. These Dimensions allow you to create Portfolio Analyzer views that can be grouped and filtered based on the outline code definitions you create.

When Project Server spawns the processes to rebuild the OLAP cube, it prepares the type of data to be included within the OLAP cube data. This includes instructions to SQL Analysis Services about which parts of Project Server database content should be assembled within the OLAP cube database.

Table 17.1 shows values that are not included within the OLAP cube as of Project Server 2003 SP1.

Table 17.1. Dimension and Measure Type Limitations

Dimension or Measure Type	Limits and Notes
Time.Weeks	The Time dimension does not automatically include Weeks, as desired by most organizations.
Task data	Task names and details are not part of the default OLAP Dimensions or Measures. If you need task data, you should extend the OLAP cube with .NET custom functions.
Enterprise Global Custom Fields	Custom fields such as text, numbers, flags, and so on are not included within the OLAP cube content.
Enterprise Global Task Outline Codes	Task outline code data is not included within the OLAP cube. If you need this data within the cube, you need to use .NET techniques to extend the OLAP cube content.
Dimension counters	There are no automatic count functions to enable you to just count the number of occurrences of a given Dimension. You can include customized column formulas, but row and column totals or subtotals may not accumulate as desired.
Percentage values	Default Measures do not automatically include percentages such as %Complete, %Work Complete, and so on. You need to create custom formulas for this type of data.
Rule-based formatting	There are no built-in pivot table format controls that change font, color, and so on based on some numeric condition or threshold value.

NOTE

If you need to include the types of data in Table 17.1, consider customized Portfolio Analyzer view formulas or extending the OLAP cube data using .NET technology.

PAGE 793.

Designing Project Web Access Portfolio Analyzer Views

Portfolio Analyzer views are much like any other PWA view except that each Portfolio Analyzer view connects to an OLAP cube as the source of the data. Each Portfolio Analyzer view is therefore connected to the OLAP cube that contains a complex mix of project, resource, and other attribute data.

You need at least three conditions if you want to create or modify a Portfolio Analyzer view:

• PWA permission is necessary to allow the view author to use the Admin tab, which also contains the Views functions. This means that each view author must belong to a PWA user Group with Category permissions that enable the Manage Views function.

• An Office 2003 software license is needed because the PWA Views edit function checks for this license before enabling view authoring or modification functions.

• SQL Analysis Services permission to the OLAP cube is also needed before the Portfolio Analyzer view author can connect to and use the OLAP cube data.

TIP

If you start a Portfolio Analyzer view with a workstation that does not have an Office 2003 product license, you will not be able to edit that view on a workstation that does have an appropriate Office 2003 product license. You need to delete the invalid view and re-create it on an appropriate workstation with an Office 2003 license.

A blank Project Server 2003 installation has no predefined Portfolio Analyzer views, so you must create and modify these views to suit your business needs. The business requirements from groups across the organization should specify how the Portfolio Analyzer view data will be used during the normal business cycles. These requirements then allow you to design each Portfolio Analyzer view and organize the format of those views.

Chapter 10, "Creating Project Web Access Project and Resource Views," describes the basic mechanics to paint a Portfolio Analyzer view, but it does not go into a lot of detail about what type of data is beneficial to show within these views.

PAGE 225.

As you define each Portfolio Analyzer view, consider the audience who will use the view(s) to get information about projects, resources, work, and so on. When you design these views, consider that each Portfolio Analyzer view may be defined with the following general data themes:

• Project-centric views allow the end users to see the data with the primary focus on portfolios of projects. These views use Dimensions that have strong reference to projects and attributes about projects. Typical project-centric views might have Dimensions such as business programs or portfolios, sponsoring organizations, life cycle phases, and so forth. Each project-centric view is usually enabled for viewing by people who belong to PWA security groups with permissions to see and use Project Center functions.

• Resource-centric views provide end users the ability to see resources and attributes that are important about resources. These views typically show resources grouped by attributes such as Resource Breakdown Structure (RBS), skills, system knowledge, working location, and so on. Each resource-centric view is usually enabled for people who belong to PWA security groups with permissions to see and use Resource Center functions.

• Assignment-centric views enable the end users to see attributes about resource assignments within projects. Although these views cannot show tasks or task details, these views can show resources' work within projects over a span of time.

• Mixed views allow the end users to see attributes about projects, resources, and assignments over a span of time. These views often mix several different kinds of information that include projects, resources, and assignments. This type of view can be confusing to novice users of Portfolio Analyzer views, so carefully consider the audience for this type of view.

TIP

If you load the sample database that Microsoft provides on the Project Server 2003 installation media, you can see several Portfolio Analyzer view examples. This gives you a starting point for designing your own views.

Figure 17.7 shows a simple example of a project-centric Portfolio Analyzer view that uses project portfolio attributes on the far left side as the primary grouping filter. Notice how each filter Dimension can be expanded to show the numeric data, such as work, along a time Dimension across the top of the pivot table. The graph also shows how the projected work increases and decreases over time.

Figure 17.7. Project-centric view showing anticipated work for time periods.

Project-centric views such as Figure 17.7 allow Project Center users to easily filter data by project attributes so that they can visualize project data such as work, baseline work, cost, and so forth. A view like this also allows the project and portfolio managers to predict the anticipated cost and, therefore, cash flow across time periods in the future.

Figure 17.8 shows an example of a resource-centric Portfolio Analyzer view. On the left side, you can see how resource names form the primary filtering criteria about resources in the Enterprise Global Resource Pool. Resource and staff managers can use this type of view to understand the work load demand for each resource they manage. This forms a good starting point for managers to use Resource Center views for more details about which tasks resources are assigned to complete.

Figure 17.8. Resource-centric view showing work or actual work compared to baseline work.

Notice how a view like this allows the end user to also compare Measures such as actual work compared to baseline work for a time period.

Assignment-centric views, as shown in Figure 17.9, provide special information about resource assignments on tasks within projects included within the OLAP cube. Although assignment data does not show task-level details, this assignment data can be useful while analyzing the work load demand for certain resource characteristics such as technology skills, knowledge of manufacturing systems, or other attributes, such as the ability to lift weight while performing work activities.

Figure 17.9. Assignment-centric views can show demand for skills.

Portfolio Analyzer views that show assignment information also use attributes from Enterprise Global Resource Outline Codes that you define within the Enterprise Global configuration settings. The specific outline code data attached to a resource assignment within each Project schedule is used to group OLAP assignment data.

CAUTION

Some assignment attributes such as multivalue Enterprise Global Resource Outline Codes do not automatically show up within Portfolio Analyzer views. Project managers must use special Microsoft Project Professional usage views to assign which specific attribute is being used for each task assignment.

PAGE 574.

Figure 17.10 shows a mixed mode view that shows project, resource, and assignment data. A view such as this allows the end user to expand or contract attributes about projects and resources. Mixed mode views such as this enable project and resource managers to see which resources are working on projects throughout programs or projects.

Figure 17.10. Mixed mode views show project and resource data.

Mixed mode views also provide valuable information about individual projects or resources. The end user can use a variety of filters to focus attention on specific data.

TIP

If you create a mixed mode view, consider setting the column width and headers to narrow display so that people with smaller display monitors can start with a simple display of information. If you make the columns wide, users must scroll right and left to see the information on the view.

Formatting Portfolio Analyzer Pivot Tables

Portfolio Analyzer views can be formatted to make them more appealing and more productive for the people throughout your organization. You should design certain format schemes for each view and save those formats within the views.

You can set color and text font formats when you create a Portfolio Analyzer view by using the Commands and Options functionwithin the view as described in Chapter 10. This section expands on those concepts to provide more details on formatting functions within Portfolio Analyzer views.

The Commands and Options function uses an object-oriented approach summarized by the phrase "Pick an object and then take an action."

After the Command and Options function is visible, you can select different object areas within the pivot table or chart. As you select each object, the Commands and Options function changes to match the context of the object you select. Each Commands and Options display enables a set of control tabs that allow you to manipulate the selected object.

Figure 17.11 shows how you can use the Commands and Options function to format Totals rows and columns.

Figure 17.11. Use the Commands and Options function to change formats.

To access the Commands and Options function within a Portfolio Analyzer view, follow these steps:

1.	Right-click on a pivot table column or row items and select the Commands and Options menu item near the end of the list as shown in Figure 17.11.
2.	Select the Format tab and then the Background Color as shown in Figure 17.12. You can also use the Format tab to set the font style, size, and so on. Figure 17.12. Set the Background color, font, and style for rows and column Totals.
3.	Use the Filter and Group tab to filter out data to include the Top, Bottom, or percentage of the values in the cube, as shown in Figure 17.13. Notice how a funnel icon appears (next to Resource) for Dimensions that have a filter applied. Figure 17.13. Set a filter to show part of the cube data in the view.
4.	When you select another object within the pivot table, you can then control the format for that item, as shown in Figure 17.14 where the Remaining Capacity column is selected. Notice how you also can use a numeric format template such as ###,###.0 to indicate the format of numeric values. In this case, the format template indicates that the values should have two fields of three characters with a separating comma and a single trailing decimal fraction value appended to the right end of the values. Figure 17.14. Select other objects to control those formats.

CAUTION

The Commands and Options function enables you to control other aspects about the data, more than just formats. Do not change the Data Source tab options unless you know how to connect to other OLAP cube data sources. Save your intermediate views before manipulating these settings; otherwise, you may lose your work.

Formatting Portfolio Analyzer Charts

The Commands and Options function can also be used to control the formats of Portfolio Analyzer charts. You can control conditions such as the following:

• Change the Chart type Enables you to see different chart configurations and show mixed graphic styles within the same chartfor example, bars and lines.

• Add or Change Axes and Titles Allows you to alter the appearance and scale of the vertical axis or even add another axis to reflect a dual-scale chart. You can also add major and minor grid tick marks as an aid to viewing data across the chart.

• Change the Colors and Patterns Provides controls that offer a variety of colors and fill patternsfor example, adding color gradient fill for bar charts.

• Alter Font Types and Formats Using various character styles, colors, and formats such as bold, italics, and underlines.

Figure 17.15 shows an example chart that has several modifications. Note the use of features such as the chart title "Cost Variance as a % of Cost to Baseline Cost," the "Cost" label on the left-vertical axis, the "Cost Var % of BL Cost" label on the right-side axis, the "Projects" label along the bottom axis, major vertical-grid tick marks, the use of a curved line superimposed on the bar chart, and so on.

Figure 17.15. Portfolio Analyzer view chart example.

Figure 17.16 shows how a right-click on a blank area within the chart provides a menu where you can select the Commands and Options function.

Figure 17.16. Right-click in the chart to see Commands and Options.

TIP

The Chart and pivot table functions use an object-oriented technique for Commands and Options. First you pick an object and then take an action. For example: Click on different sections of a chart and watch the Commands and Options window change as the selected object gains focus.

After you select the Commands and Options menu item, you can navigate to various areas of the chart by selecting an object or using the drop-down menu item as shown in Figure 17.17. Notice how the list shows all the active chart areas that can be modified.

Figure 17.17. Chart area sections available from the drop-down list.

Suppose that you are creating a Portfolio Analyzer view with a chart and you want to add a chart title like that shown within Figure 17.15. When you select a blank area in the background of the chart, you can select the icon to Add a Title as shown in Figure 17.18.

Figure 17.18. Look for icons like Add a Title on the chart.

When you use the Add a Title icon, you can select that title and change the chart title caption text as shown in Figure 17.19. You can also use text formatting functions to control font style, font size, bold, italics, underline, and color.

Figure 17.19. Enter the Caption text for the chart title.

TIP

When using the Commands and Options function to edit text, as in a chart title, make sure that you press the Enter key to complete the action within the text entry data field.

The Commands and Options function also provides controls for the overall chart type or individual data measures within the chart. Figure 17.17 shows how you can select an item such as "Cost Var as % of Base Cost" and then control how that item is plotted within the chart area. Figure 17.20 shows how the chart Type can be set to use different line, bar, curve, or other display modes for a specific measure. Notice how a Smooth Line style can also show individual data item markers along the curve. If you hover over those data items, a pop-up item appears showing the value of the specific data marker.

Figure 17.20. Change the chart graph display for individual measures.

TIP

Try changing the Miter, Marker Shape, Fill Type, and other controls when you select chart items such as curves, markers, and so on. These allow you to make the chart items stand out or make them easier to find as the end users view the chart or export charts to GIF graphic formats.

The Commands and Options function also allows you to place additional measurement axes on the chart like the one on the right side of Figure 17.15, shown previously. Say that you want to add this type of axis to your chart, and you want the new axis to have a different scale than the original axis on the left. You can use the simple steps as follows:

1.	Right-click on the chart background and select the Commands and Options menu item.
2.	Make sure that Chart Workspace is selected from the pull-down menu as shown in Figure 17.21. Figure 17.21. Select the Chart Workspace to add an axis.
3.	Select the Series Groups tab and select the Series Item, such as Cost Var as % of. Then click OK to create a logical data measures group that will use the new axis. When you click the Add button, a new axis is automatically created that is associated with the selected data measure grouping, as shown in Figure 17.22. Figure 17.22. Select the data series to establish a chart "group." TIP If you make a mistake while creating a new series grouping, use the Operation drop-down list and select the Merge with Group 1 function. Then click OK to rejoin the series into a single logical group.
4.	Adjust the scale of this new axis to suit the criteria of the data measures. In this case, the scale is percentage values, so the maximum and minimum values are set to decimal fractions, as shown in Figure 17.23. Figure 17.23. Adjust the scale of a chart Axis.
5.	The last step you may want to take is to add axis titles so that people will understand the difference between an axis on the left versus the new one on the right side of the chart. Select the Add a Title icon, as shown previously in Figure 17.18. Then select the Format tab and enter the new title in the Caption entry area as shown in Figure 17.24. Figure 17.24. Give the chart axis a title.

Building Custom Formulas Within Portfolio Analyzer Views

Portfolio Analyzer views can contain customized formulas whereby you insert additional calculated data columns within the measures section of the pivot table. This section introduces custom calculated data values so that you can understand some possibilities to show special data conditions not part of the default OLAP cube data.

The calculated data measures allow you to use certain formula functions, but you must first understand some of the principles behind these functions:

• Calculated measures use SQL Analysis Services MDX language functions to display customized column data. These functions are listed within the Multidimensional Expressions (MDX) function list within the compiled help files for SQL Analysis Services documentation.

• Default OLAP measures can be used as variables within custom formulas, so you can reference measures such as work, baseline work, availability, actual work, and so forth.

• Some customized formulas may not total as expected for subtotal or grand total columns or rows. This is especially true when using MDX functions, such as DistinctCount, that return the number of items within a referenced return set.

• Debugging formulas can be difficult because the formula parsing functions return basic error codes that do not always provide clear descriptions of the error.

• Custom formulas are calculated on the end user computer, so the performance of Portfolio Analyzer views may be slow for users with slow computers.

TIP

Designing and coding customized calculated formulas can be frustrating. Consider engaging the help of a software developer who has experience using the SQL Analysis Services MDX language functions.

You can easily add custom formulas while creating or modifying a Portfolio Analyzer view. Suppose that you want to create a customized data measure column called Remaining Availability that shows the difference between assigned work versus the calendar availability for resources within the Enterprise Global Resource Pool. Furthermore, you want to graphically display a chart that shows any condition where a resource is overloaded, as shown in Figure 17.25 where the bars for overloaded resources display below the horizontal zero axis.

Figure 17.25. Example Portfolio Analyzer view showing remaining availability for resources.

The following steps illustrate how to create this type of view with a customized formula:

1.	Click on the Calculated Totals and Fields icon within the pivot table as shown in Figure 17.26. Figure 17.26. Use the Calculated Totals and Fields to create a custom formula.
2.	Select the Calculation tab to enter a formula and enter the Calculation Name, also known as the Caption text, within the Captions tab.
3.	From the Insert Reference To list, select an appropriate Measures field such as Availability, as shown in Figure 17.27. You can also type in the formula if you are comfortable with writing formulas by hand. Figure 17.27. Use the Insert Reference To list to build formulas.
4.	Enter an arithmetic operator such as "-" (minus sign) followed by another field such as Work Total.
5.	Click the Change button, and the formula is entered within the new column as shown in Figure 17.28. Figure 17.28. Custom formula result.

TIP

Use embedded newline characters (Enter key) placed within the formula to make the formula easier to read.

The results of calculated formulas can also be displayed within the graphic charts associated with the pivot table. Figure 17.29 shows how the Remaining Availability can be displayed using a split vertical axis and conditional formatting to display negative values that stand out compared to other data.

Figure 17.29. Graphic charts can show customized calculations.

CAUTION

Make sure that you understand how end users interpret the meaning of customized formula results. Figure 17.29 shows an example of how information such as Availability is displayed when the user opens subvalues within the pivot table. Notice how the rows show 2,080 hours of availability for each row. Also notice how the Remaining Availability rows may also show unexpected values as in Figure 17.30. This is a general condition of data fields such as Availability that is derived from each resource personal calendar within the Enterprise Global Resource Pool. But that value does not consider the assignments within each project schedule, so use caution when designing Portfolio Analyzer views that show this type of data.

Figure 17.30. Use caution when designing views with calculated data columns.

If you make a mistake while building a calculated formula, the MDX language formula parser issues cryptic errors like that shown in Figure 17.31. You must correct the offending error before any of the pivot table values are displayed. This error is a result of a missing closing brace "]" to the right of the Work.

Figure 17.31. Formula errors may be difficult to resolve.

TIP

Save the general view changes before you create a customized calculated formula. If you cannot correct a formula syntax error, you may have to use the view Cancel button to abandon the view edits and recover a previous working view design.

NOTE

For more information about SQL Analysis Services MDX functions, look on the SQL Analysis Services machine for compiled help files such as C:\Program Files\Microsoft SQL Server\80\Tools\Books\olapdmad.chm.

Enterprise Global Resource Single-Value Versus Multivalue Outline Codes

All the Enterprise Global Project Outline Codes and Enterprise Global Resource Outline Codes 1 through 19 are considered single-value codes because you can select a single value from the defined list. Single-value codes are used as dimension attributes when the OLAP cube is regenerated.

Enterprise Global Resource Outline Codes 20 through 29 are considered multivalue codes because you can select multiple values from the same list. This condition is perfect for representing resource attributes such as skills that project managers and resource managers can use to search for resources with these kinds of attributes. These attributes can be helpful when using the Match function within PWA and Project Professional Build Team functions because the query filter uses the "Contains" clause to search for substring matches within the composite items selected for each resource.

Multivalue resource codes are not used when the OLAP cube is generated because the OLAP build process cannot uniquely identify which specific codes are used when a resource is assigned to tasks within a project schedule.

Consider the following example and questions. Your Project Server 2003 Enterprise Global configuration includes the definition of three multivalue resource codes: Computer Language Skills, Software Applications, and Manufacturing Machine Tools. Becky Young is a resource within the Enterprise Global Resource Pool, and she has a number of attributes established from each of these three multivalue codes. Becky has been added to the project team for several projects and has been assigned to several working tasks.

Resource and staff managers may need to answer the following questions:

• Are all of Becky's skills used within each project?

• Which of Becky's specific skills are being used for each task she is assigned to work?

• How can Portfolio Analyzer views be used to determine how much work is predicted for each of Becky's skills used within projects?

Each of the Enterprise Global Resource Outline Codes 20 through 29 also has an associated single-value code item identified with the same code number as the multivalue counterpart. These single-value codes can be used when making a resource assignment to a task within the project schedule, within the Usage views. So when Becky is assigned to each working task, the project manager can select which of the specific multivalue codes is being used for each task.

When the single-value codes are established for each task assignment, the OLAP cube automatically contains Measures such as work, cost, actual work, and so forth. These Measures then have a direct relationship to the single-value outline code settings as OLAP Dimensions. Therefore, the pivot table contains the appropriate direct relationship to the defined multivalue codes.

Figure 17.32 shows an example of setting single-value codes for each resource assignment within a project schedule. The Develop Preliminary Investigation Plan task has Steve Masters and Bradley Beck assigned to complete the work activity. The project manager used the Task Usage view to set which of the multivalue Skills codes is used for each resource on that particular task.

Figure 17.32. Single-valued codes set for each resource assignment in a project schedule.

The end result of using the multivalue and associated single-value code is that the Portfolio Analyzer views can show Resource Assignment Measures in the pivot table as shown in Figure 17.33. This allows resource managers and others to see if the work distribution is based on worker skills used within all project schedules.

Figure 17.33. Multivalue code settings can be displayed within the pivot tables and graphs.

PAGE 577.

The specific details used within the Enterprise Global Resource Outline Codes depend on your business requirements. You need to carefully consider how these attribute codes are used within Portfolio Analyzer views and other PWA functions.

Figure 17.34 shows a variation of a mixed mode view whereby resource managers can look for local resources in projects. This is a common situation whereby project or resource managers do not use entities from the Enterprise Global Resource Pool, or they may accidentally create local resources by typing in the names of resources instead of using the Build Team functions in PWA or Project Professional.

Figure 17.34. Mixed mode view showing local resources in projects.

A Portfolio Analyzer view like this is a valuable starting point to inform project managers that they need to exchange local resources for those found within the Enterprise Global Resource Pool.

TIP

If project schedules have resources that are not part of the Enterprise Global Resource Pool, those resources will show up as local resources within the schedules. Use the resource name filter to uncheck all resources except the resource called local. You can then see which projects have work assigned to those non-Enterprise Global resource entities. Use the Grand Total rows and columns to quickly find this data.

Manipulating Portfolio Analyzer Views

The PWA view administrators can create Portfolio Analyzer views and also allow end users to manipulate those views to suit their individual analysis needs. If the View toolbar is turned on and the user's desktop has an Office 2003 product installed, the user can use the toolbar features to temporarily alter a view.

Pivot Table Toolbars

When you create Portfolio Analyzer views, you can optionally allow the users to temporarily alter the view by using the available Office Web Components (OWC) toolbars above the graph or pivot table sections of the view. Figure 17.35 shows an example of these pivot table toolbar functions, which are explained as follows:

• Logo If you click on the Microsoft Office logo symbol a pop-up window shows the current version of OWC.

• Copy Allows you to place the selected range of rows or columns into your computer copy/paste buffer. You can then paste the copied data into another application, such as Excel.

• Sort Ascending and Sort Descending Enable you to invert the order of data within a selected Dimension item.

• Auto Filter Provides a quick toggle method to clear or reapply all individual filtered Dimensions.

• Show Top/Bottom Items Enables you to remove all data except those Measures that meet the selected criteria range. This is a useful tool to find the top or bottom percentage of items that meet a specified Measures range entered within the Commands and Options window that appears when you select this toolbar icon.

• Autocalc This OWC option is disabled for Portfolio Analyzer views.

• Subtotal This function toggles subtotal and grand total value displays for the selected Dimension item.

• Calculated Totals and Fields Provides an option to create a new calculated total field formula with the Commands and Options control window.

• Show As % This icon provides a method to change a Measures column data format to a percentage value.

• Collapse and Expand Provides a simple way to collapse or expand the selected Dimension.

• Hide Details and Show Details These OWC options are disabled for Portfolio Analyzer views.

• Refresh Data Reconnects to the OLAP cube and refreshes the information in the view.

• Export to Excel This icon starts Microsoft Excel and sends the pivot table information to Excel for more analysis.

• Commands and Options Opens the Commands and Options window to allow formatting and other customization functions.

• Field List Click on this icon to show the Measures and Dimensions field list so that items can be added to the pivot table display.

• Help This icon opens a general OWC help window that allows you to better understand features and functions of the OWC pivot table.

Figure 17.35. Toolbar functions allow users to manipulate a Portfolio Analyzer view.

NOTE

If the toolbar is not displayed or is grayed out, you may not have permission to alter the view, or your desktop does not have an Office 2003 component installed. Expand the View Options tab at the top of the Portfolio Analyzer view page to see whether the Show Toolbar item can be selected.

Using the Data Field List to Modify Views

The data Field List contains a mix of data types including Dimensions and Measures held within the OLAP cube. The author of Portfolio Analyzer views uses the Field List to load the Measures and Dimensions into the pivot table areas as depicted previously in Figure 17.6. When the view is saved, it contains the structure and format the author intended.

Portfolio Analyzer view users can be given the permission to temporarily modify a default view by adding or removing Dimensions and Measures within the pivot table. They can select the Field List icon on the pivot table toolbar and then move the data to different locations within the view or drag additional items from the Field List into the pivot table areas.

The Field List manipulation functions provide each user a lot of flexibility to show the data they need to make decisions about projects, resources, and assignments. The Portfolio Analyzer views therefore become a powerful decision support analysis tool.

Saving Personal Links to Retain View Modifications

Users of Portfolio Analyzer views may be given the permission to temporarily alter the view content. After a user modifies a view, she may want to retain those modifications to be used at a future time.

The user can use the Save Link view function, at the top of each view, to specify a personalized name for the view. A saved link allows you select this link to restore the view with personalized modifications. These links retain the original view in addition to the personal changes, but this action does not affect the original default view for other users.