November 9, 2010

Force rebuild of Project Server Reporting Database

I recently had a client who had both projects missing from the cube that should have been there, and projects that had been previously deleted still showing up in the cube. A query against the Reporting database did show the deleted projects were still there, although they were not in the Published database. Thinking a rebuild of the reporting database would do the trick, I proceeded with a solution I found. Unfortunately after the successful rebuild of the reporting database, I could not get my cube to build, despite some of the resolutions I found. The specific error received was “Invalid call to GetJoinForCustomFieldDimension. Cannot join "Task Non Timephased" fact with a custom field of entity Assignment” The steps below outline the procedure that worked for my specific scenario.

1. Logon to Project Web Access with Administrator credentials
2. Remove all fields from the cube configuration.
3. Select Administrative Backup from the Database Administration section on the Server Settings Page in Project Web Access
4. In the Items for Backup section of the Backup page, select the checkbox for Enterprise Custom Fields and then click the Backup button and click the OK button when prompted by the system
5. Select Administrative Restore from the Database Administration section on the Server Settings Page in Project Web Access
6. Choose Enterprise Custom Fields from the Choose Item selector on the Restore page
7. Click the Restore button and then click the OK button when prompted by the system

Now the reporting database will take some time to rebuild depending on the volume of data and available server resources. After the successful rebuild I performed these steps (primarily because of the issues I encountered when first rebuilding the cube)

1. Rebuild the cube.
2. View data in Data Analysis
3. Add items previously removed from the cube configuration
4. Rebuild the cube
5. View data in Data Analysis