The Reporting Database and Report Data Service

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.

Microsoft Office Project Server 2007 stores data in four Microsoft SQL Server databases. The Draft, Published, and Archive databases all must be installed on one server; you can install the Reporting database (RDB) on a different server in the Windows SharePoint Services server farm. This article helps you understand how queue messages and serialization in the Report Data Service (RDS) get data to the RDB, and how changing project data affects the RDB.

Data changes and other processes in Project Server result in queue jobs for the RDS that update data in the RDB. Figure 1 shows the relationships between the databases and the events that trigger data flows between each database.

Figure 1. Data flows between Project Server databases

You can optimize the RDB for access to Project data by:

• Using relational query tools.

• Writing custom code designed to extend Project Server functionality.

• Using the Project Server Portfolio Analyzer cube-building process.

The Reporting database is tightly synchronized with the Published database. The Published database contains all published plans, timesheets, and metadata such as the following:

• Resource and user definitions

• Custom field definitions

• Lookup table definitions

• Timesheet data and definitions

• Calendar definitions

In addition, the RDB also contains data extracted from the following lists in SharePoint project workspace sites:

• Project issues

• Project risks

• Project deliverables

How Data Gets to the RDB

The diagram in Figure 1 shows how data flows from the Published database to the RDB. These data flows are triggered when data is created, changed, or deleted by Project Server Interface (PSI) calls made directly by or on behalf of users working with Project Web Access or Microsoft Office Project Professional 2007. After processing the request, a PSI method notifies the RDB of pending changes by placing a message on the project queue. Project Server then executes the message asynchronously.

Each message maps to a specific queue processor, which is a program that manages data synchronization from the Published database or SharePoint list data to the RDB. The collection of queue processors is called the Report Data Service (RDS). The decoupled interface of the RDS offers several advantages:

• Queue processing does not add to user perception of response time because control returns to the user after the job is queued. Despite changes that can result in large amounts of processing to make data “report friendly,” the decoupled interface shields the user from the processing.

• The RDB can be taken offline without the loss of data or failed PSI calls that would result from a tightly-coupled system.

• Users can see status of the Reporting jobs on the Manage Queue Jobs page in Project Web Access (click Server Settings, and then click Manage Queue).

Table 1 shows the job (message) types that make up the RDS. All RDS messages have controlled serialization to minimize the chance of database transaction deadlocks and to ensure ordered arrival of data. Serialization control varies by job type.

The message serialization cannot prevent race conditions in all cases. For example, a project publish request can arrive before previously created resources and custom field definitions. The likelihood of race conditions increases when a server-side application uses the PSI to create and edit objects, because the natural time delays that occur during user actions might not be present. In this case, the message creates retries, to wait for dependent data to appear, causing increasing time delays on the queue.

Although the developer should be aware of serialization, there is no need to program for race conditions because no reporting events occur until the data has arrived in the RDB.

In Table 1, "always one UID" refers to a specific GUID that is used to serialize all messages of that type. The job types are message type values in the QueueMsgType enumeration.

Table 1. Queue job types and serialization in the RDS

Job Type	Serialization Control
ReportingAttributeCubeSettingsSync	Always one UID
ReportingBaseCalendarSync	Always one UID (the same as ReportingResourcesCapacityRangeSync)
ReportingCustomFieldMetadataSync	Custom Field UID
ReportingEntityUserViewRefresh	Always one UID
ReportingFiscalPeriodsSync	Always one UID
ReportingLookupTableSync	Lookup Table UID
ReportingProjectDelete	Project UID
ReportingProjectPublish	Project UID
ReportingResourcesCapacityRangeSync	Always one UID
ReportingResourceSync	Resource UID
ReportingSyncGlobalData	Always one UID
ReportingTimesheetAdjust	Timesheet UID
ReportingTimesheetClassSync	Always one UID
ReportingTimesheetDelete	Timesheet UID
ReportingTimesheetPeriodDelete	Always one UID
ReportingTimesheetPeriodSync	Always one UID
ReportingTimesheetSave	Timesheet UID
ReportingTimesheetStatusSync	Timesheet UID
ReportingWSSSync	Project UID

An additional layer of serialization handles interactions between non-queue serialized messages. For example, when changing a resource and at the same time changing the resource's base calendar, Project Server uses Microsoft SQL Server application locks (sp_applock) to enqueue (place on the queue) and dequeue (take off of the queue) parts of the schema.

Notes on RDS Job Types

When incoming data cannot be serialized at a higher level of granularity or where the risks of deadlocks are high, "always one UID" refers to a specific GUID that controls serialization for all messages of that type.

Many RDS jobs move data incrementally. You can keep the data that must be moved to a minimum, for example, by tracking changes to data within a project or SharePoint list or by identifying which of several custom fields have changed.

Some actions can generate a large amount of processing and data movement, as described in the following examples.

• ReportingBaseCalendarSync   A base calendar can address thousands of resources. If the base calendar changes, for example, when you change a working day to a nonworking day, then the RDS must recalculate the base capacity (also known as baseline capacity) and resource-specific capacity for all resources affected by that calendar.

• ReportingResourcesCapacityRangeSync   The capacity range governs the number of months of capacity data that the RDS maintains. If you extend the capacity range, then the RDS must obtain additional capacity information for all resources.

• ReportingResourceSync   This RDS processor is called whenever you add a resource, change a resource definition, or delete a resource. Deleting a resource can cause all of a resource's active assignments to be updated. The assignments of a deleted resource are mapped to the resource that represents Generic Local Resource.

  Note
  Use caution before deleting widely used resources such as Project Auditor.

See Also

Concepts

Resource Management Data

PSI Methods for the RDB