Do More Than Manage
Gord Schmidt's Ideas for Doing More with Microsoft Project Server
Summary Task

Rebuilding Views and OLAP Cube to Remove Corruption

I had a customer that was having problems building the Project Server OLAP cube and also was experiencing some performance problems. It turns out both were symptoms of the same problem. Publishing a project was taking far longer than it should and in PWA OLAP "cube initiated" seemed to be the permanent status as it never got to cube "is being built".

Microsoft Support was able to determine that there was corruption in the cube and view tables on the server and suggested the following remedy:

  1. Check in all projects. (This means every project manger!)
  2. Ensure all users are off of the system
  3. Create a backup of your SQL Server.
  4. Rebuild the View and Cube tables in SQL Server by running the following scripts scripts available on the Project Server CD in Query Analyzer:
    • From the CD: \support\database\Viewtbls.sql
    • From the CD: \support\database\Cubetbls.sql
  5. If you have previously applied Microsoft Project Server 2003 Service Pack 1 to your system, apply the database portion again.
  6. Use the View Populator Tool to republish all of the project plans.
  7. Build the cube (either manually from PWA or as regularly scheduled) after the View Populator Tool is finished.

This approach can correct corruption problems in the view and cube tables without having to fall back to a previous backup. As the view and cube tables are just calculated based on other tables, there is no data loss in just regenerating them.

Posted by Gord Schmidt on Monday, February 28, 2005 | Permalink | Resolve