The Microsoft Office Project Server Reporting database can become corrupt at times causing general queue job failures when publishing projects when invoking the Reporting message processor throwing exceptions that cite FOREIGN KEY constraint conflicts. Sometimes these are spurious and random in nature, and you can correct them simply by forcing a rebuild of the Project Server Reporting database.
We can explicitly trigger the reporting database rebuild operation by taking administrative backup of enterprise fields and initiate the administrative restore process.
Note: To rebuild Reporting Database, you should have administrator permissions on PWA site.
This article describes the steps involved in rebuilding the Reporting Database in Project Server environment. The following steps are applicable to Project Server 2010 and Project Server 2007.
Navigate to PWA home page -> Server Settings -> Database Administration -> OLAP Cube Settings
- Highlight the OLAP Database Name row and click “Cube Configuration” -> Remove all fields from the cube configuration, if any fields exist (This step is optional, perform only if you have any cube build issues related to enterprise custom field data)
Server Settings -> Database Administration -> Administrative Backup
In the Items for Backup section of the Backup page, select the checkbox for Enterprise Custom Fields and then click the Backup button
Server Settings -> Database Administration -> Administrative Restore
- Select “Enterprise Custom Fields” from the Choose Item selector on the Restore page
Click the Restore button and then click the OK button
System initiates the rebuilding of Reporting Database and it takes some time to complete the process.
Once restore process is completed, verify the OLAP cube building functionality by building the cube.
(Note: At this moment, you are building the cube without any custom enterprise fields.)
Once you able to build the OLAP cube successfully, try to access some out of box Excel Services reports to make sure you have clean reporting database.
The following step is optional:
If you removed or any enterprise fields in step 2, please add those fields back using OLAP cube configuration.
Rebuild the cube