Solution Starter: Microsoft Office Project Server Enterprise Reporting

  • Article

 

Patrick Conlon
Microsoft Corporation

March 2004

Applies to:
    Microsoft® Office Project Server 2003
    Microsoft Office Project Professional 2003

Summary: The Enterprise Reporting Solution Starter for Microsoft Office Project Server offers you an efficient project management reporting solution suitable for complex, distributed environments. With this solution, you can collect and report information from Project Servers across your enterprise organization. (8 printed pages)

Contents

Introduction
Overview
Enterprise Reporting Solution
Components of the Enterprise Reporting Solution
Design Considerations
Conclusion

Introduction

In today's business environment, information is power. Data held in Microsoft Office Project Server can be used as key ingredient to the organization's operational health indicators. It can be exploited in a wide variety of ways, including (but not limited to):

  • Benchmarking team performance.
  • Identifying potential upcoming skills shortages.
  • Monitoring the costs of areas such as Research and Development that have peaks and troughs in expenditure based upon project activity.
  • Ensuring the gainful deployment of the organization's resources.

Project Server makes use of open technology platforms such as Microsoft SQL Server™ and its associated Analysis Services business intelligence toolset to open up the data entered by project personnel. This openness empowers a diverse set of partners and customers to extract value from this data using simple and well-understood techniques that do not require a costly engagement with specialist staff exclusively sourced from the project management solution vendor.

This article demonstrates how the data collected in multiple Microsoft Office Project Server installations can be securely linked and rolled up to provide an enterprise view. The working code sample that this article refers to can be quickly deployed with little or no customization. The code sample uses technologies that are already included in every Project Server deployment. The code sample is available with the Enterprise Reporting Solution Starter and Microsoft Office Project 2003 SDK on the Microsoft Download Center.

Overview

Microsoft Office Project Server customers make choices when deploying the technology, and these choices can lead to a decision to implement multiple server installations. Typical reasons for this decision include:

  • Differing project management methodology usage across the business.
  • Project calendar differences leading to conflicting lockdown periods.
  • Considerations forced by geographic or global network topologies.
  • The need to run multiple disparate software versions; technology adoption rates vary, especially across conglomerated industries.
  • The number of users who will use the system exceeds that recommended for a single site.

After a customer has decided to implement more than one system, he or she may be faced with the issue of having to manually collect data from each system in order to retain a view across their business. Microsoft Office Project 2003 eases this collection by making the capture of data into Microsoft Office Excel as easy as pointing and clicking. This solution starter provides a reference infrastructure for further automating the collection of data for central reporting.

Many customers have deployed multiple installations without the need to roll up the data held within each system. Often data is collected from each system into Enterprise Resource Planning (ERP) systems, such as SAP and PeopleSoft, which then handle the aggregation of the data. One of the great strengths of Project Server is its ability to integrate with other line of business systems, and this approach is strongly recommended for those customers who require rolled-up project data to be integrated with such systems. However, ERP systems, by their nature as transactional processing systems, can be less than optimal for delivering the quality of project reporting expected by project management office personnel. This is where the Enterprise Reporting Solution Starter can help: It demonstrates how to collect reporting data from multiple systems, and then how to use the familiar Project Portfolio Analyzer to analyze the consolidated data.

By collecting data into a central point and then loading that data into a Portfolio Analyzer cube, the performance of any query against this data is much better than would be possible in a truly distributed system, and the impact of long-running queries is limited to one server.

By combining this solution starter with the Portfolio Analyzer OLAP Extensions Solution Starter, you can enhance both the depth and the breadth of the data.

Enterprise Reporting Solution

This article describes some of the opportunities for enhanced business processes created by the ability to consolidate Microsoft Office Project Server 2003 data from multiple sites. This solution enables Portfolio Analyzer to report against a cube that contains data collected and coded from each site. Two brief use case scenarios follow.

Note   These opportunities already exist when you deploy a single site system without applying this solution starter.

Scenario: Federated Portfolio Analyzer

Melinda is currently in an in-country Program Management role and has a good view of projects taking place in her local domain. Her manager, who has additional, global responsibilities, is impressed by her grasp of project analysis as delivered by Portfolio Analyzer and asks her to prepare some statistics to benchmark the local unit against other similar units located in other parts of the world. However, Melinda's company has implemented a regional Project Server 2003 infrastructure so this information is hard to gather.

Luckily, her forward-thinking IT department has used the Enterprise Reporting Solution Starter to produce a virtual cube that spans data from all of the production servers running Project Server 2003; she will get the data she wants in the same format that she is used to working with.

Scenario: Federated Resource Management

Talal is a global practice manager for a team of IT staff with Microsoft Windows® infrastructure skills. He is responsible for ensuring that the team can resource both short- and long-term projects. To do this, he has set up projects in each of the regional Project Server 2003 systems that the team members use to book their time and enter new tasks against. He responds to task additions using Microsoft Office Outlook but finds that he is taking a lot of time maintaining a spreadsheet that shows global resource utilization.

Implementing the global resource view from the Enterprise Reporting Solution Starter allows him to automate this data collection, which in turn enables him to concentrate on the important part of his job: smoothing peaks and filling troughs of global resource utilization.

The global practice managers can exploit another feature of this solution starter that enables support of resources that are shared across different Project Servers. By setting a specific Enterprise Resource Outline Code against a resource, and then by adjusting the resource availability in each system (to prevent overcommitment, so Resource A can be allotted 30% of their time in one system, 70% in another), this solution starter provides a rolled-up view of their assignments across the two systems.

Enterprise Reporting Solution Overview

The Enterprise Reporting solution builds on top of documented Portfolio Analyzer cube extensibility. (The Portfolio Analyzer OLAP Extensions Solution Starter and Microsoft Office Project 2003 SDK are available on the Microsoft Download Center.) By using the existing user extensibility to add functionality that consolidates data from several Project Server databases it becomes possible to build a Portfolio Analyzer cube that contains data from across the enterprise.

The following figure depicts the infrastructure configuration required to achieve enterprise reporting. It shows the distributed "local" servers together with the consolidating "central" server.

Figure 1. Configuration

The Enterprise Reporting Starter Solution has been carefully designed to minimize the work required on each local Project Server. All that is required is that you set up a SQL Server account with read-only access to a limited set of the Project Server tables. The rest of the processing takes place at the central Project Server.

This solution sources its data from each set of MSP_CUBE* SQL Server database tables created by the Portfolio Analyzer cube processing on each source system. Therefore, this process must run on each source system before collecting the data to a central point. These tables are used because the cube creation process performs the extensive formatting and data preparation work required to transform the project database into a format that can be consumed by SQL Server Analysis Services.

The data is moved into the central system using SQL Server linked servers. These are set up within the central computer running SQL Server (outside of the compiled extensibility module). This separation means that you don't need to encode SQL Server account/password combinations within the module, and the Database Administrator can continue to control access.

Components of the Enterprise Reporting Solution

This section further explains the components that make up the Enterprise Reporting solution starter. The components are classified based on whether they are part of the local or central Microsoft® Office Project Server.

Source Local Project Server System Components

Shared Enterprise Outline Codes

Portfolio Analyzer views of the project data offer you the value of manipulating your reporting data by "slicing and dicing" and "drilling down" into it based on hierarchical dimensions created from the enterprise outline codes. The Enterprise Reporting Solution Starter eases data combination by taking care of code matching; it combines the codes from each site, matching the character strings to the appropriate code numbers and then fixing the code numbers in the copied local data to match the central codes.

Because it is a string match, the local sites need only implement a part of the global code, as shown in the following figures.

Figure 2. Local site 1: Resource Outline Code 12 (indicating Country): NA:US, NA:Canada, NA:Mexico

Figure 3. Local site 2: Resource Outline Code 12 (indicating Country): EU:UK, EU:France

Figure 4. Central Site: Resource Outline Code 12 (indicating Country): NA:US, NA:Canada, NA:Mexico, EU:UK, EU:France

The code fix-up in the central MSPOLAPBREAKOUT.DLL will map the two local site's data together by matching the codes in the central code. If a match isn't found then "No Value" will be used.

Linked Server Account

The SQL Server database on the local Project Server holds the information used to populate the local Portfolio Analyzer cubes. This information can be read by this solution only if it can connect to the local server with an account that has access to the data. To avoid the hard-coding of user ID/password information in the solution code, the solution uses SQL Server linked servers to access the data through the central computer running SQL Server.

SQL Server linked server technology securely stores connection details that enable programs that run on the central computer running SQL Server to access the data held on each local server as if it were present on the central server. The linked server connection is used to seamlessly set up the connection that allows queries to run transparently.

The SQL Server linked server definition for each local database requires that the local project database owner create a user ID with read-only access to a subset of its data. For details on the user ID and its access requirements, see the following implementation section and the account definition script.

Central Project Server System Components

Linked Server Definitions

The central server must be able to access the data it requires on each local server, preferably without hard-coding user ID/password information. It does this by predefinition of a linked server that uses the local accounts (as defined earlier). Each local server requires an individual linked server definition on the central computer running SQL Server, as well as customizations (to register the linked server name) in the MSPOLAPBREAKOUT.DLL module.

MSPOLAPBREAKOUT.DLL

This module is compiled from the Microsoft Visual Basic® project distributed with the Enterprise Reporting Solution Starter. It is registered only on the central server and then is invoked by the cube-building process automatically whenever the central Project Server recreates its Portfolio Analyzer cube. The module must be customized as part of the deployment of this package. The customization can be as simple as defining the linked servers that the module should use to collect data, or as complex as altering the Enterprise Resource Code (EROC) that the module and associated SQL Server code use to identify globalresources.

This module connects to the central computer running SQL Server with the account used for Portfolio Analyzer cube processing. The account is passed in as a parameter when the DLL is invoked.

SQL Server Stored Procedures and Functions

The MSPOLAPBREAKOUT.DLL module accesses the data it needs through a combination of queries hard-coded in the source (typically where the exact makeup of the query varies, depending upon the linked server that is being accessed) and unvarying SQL Server stored procedures, functions, views and tables that are stored in the central database.

Design Considerations

The Enterprise Reporting Solution Starter attempts to leverage existing infrastructure wherever possible, which makes it easier and faster to deploy, leading to quicker realization of its benefits. The two key design considerations for the solution starter are:

  • To ensure that it is possible to reuse existing enterprise code functionality and combine outline codes from disparate systems.
  • To provide a mechanism for marking "enterprise resources" that might be spread across multiple project servers.

Value of Enterprise Codes

Project Server includes functionality that takes the enterprise outline codes associated with projects, resources, and assignments and adds them to the Portfolio Analyzer cube as dimensions, connected to the detailed data on projects and assignments. The Enterprise Reporting Solution Starter code matches the local data on each Project Server to the codes in the central server. This is a purely textual match and as such allows for different subsets of the code to be deployed on each local server, as long as the absolute path to each of the leaves can be matched to an equivalent path in the code on the central server.

Code paths that don't result in a match either result in a Null value (when the enterprise outline code is not even defined on the central server), or are set to "No Value", the bucket for bad codes.

Marking Shared Resources

This solution starter uses Enterprise Resource Code 19 to enable a resource to be tagged as "Global" (this code number and value are used in the solution code; to change it, the source code and one SQL script must be customized). When a resource is tagged as "Global" its data is copied over and assigned to the central server's instance of the user. This allows assignment and availability data for a resource that is spread across multiple servers to be combined and viewed as one entry.

By exploiting the current structure of the Portfolio Analyzer cube, the current Portfolio Analyzer user interface can be used to query the data.

Conclusion

The Enterprise Reporting Solution Starter illustrates the flexibility of Microsoft Office Project Server 2003 and its underlying components. Data from multiple Project Server databases can be securely accessed and returned to a central system where it can then be accessed by familiar reporting tools.

This solution delivers the ability to perform enterprise-wide rollup across common enterprise outline codes, and the ability to track the usage of resources across multiple project servers.