Error message when you try to build an OLAP cube in Project Server 2003 or in Project Server 2002: "The cube scheduled to be built on Date Time failed."

When you try to build an OLAP cube in Microsoft Office Project Server 2003 or in Microsoft Project Server 2002, you receive the following error message:

During the cube build process, all the data in the staging tables is removed. It is the cleanup of these tables that may cause a time-out.

Typically, this problem occurs with the MSP_CUBE_ASSN_FACT table because this table holds more data than the other staging tables. Sometimes, this problem may also occur with the MSP_CUBE_RES_AVAIL_FACT table or the MSP_CUBE_TIME_BY_DAY table. During the cleanup of the staging tables, SQL statements are made that may take a long time to complete and may sometimes time-out.

The following SQL statements are made during the cleanup of the staging tables:

• DELETE from MSP_CUBE_ASSN_FACT
• DELETE from MSP_CUBE_RES_AVAIL_FACT
• DELETE from MSP_CUBE_TIME_BY_DAY

To work around this issue, use one of the following methods, as appropriate.

Note We recommend that you truncate the following tables before you modify the SQLTIMEOUT registry entry:

• MSP_CUBE_ASSN_FACT
• MSP_CUBE_RES_AVAIL_FACT
• MSP_CUBE_TIME_BY_DAY

For example, truncate the tables before every cube build as a SQL job.

Method 1: Truncate the tables in SQL Query Analyzer

To work around this issue, use SQL Query Analyzer to issue a TRUNCATE TABLE statement to remove the data. To do this, follow these steps:

1. Start SQL Query Analyzer.
2. In the SQL Server box, click (local), type sa in the Login name box, type a password in the Password box, and then click OK.
3. On the Query menu, click Change Database.
4. In the Select database of Your_Server_Name box, click ProjectServer, and then click OK.
5. In the Query box, type the following commands. Press ENTER after each command.
   TRUNCATE TABLE MSP_CUBE_ASSN_FACT
   TRUNCATE TABLE MSP_CUBE_RES_AVAIL_FACT
   TRUNCATE TABLE MSP_CUBE_TIME_BY_DAY
6. On the Query menu, click Execute.
   
   The following message appears in the Messages box:
   The command (s) completed successfully.
7. Quit Query Analyzer.

Method 2: Create an automated job in SQL Server Agent

To prevent this problem, include the TRUNCATE TABLE statements in an automated job by using SQL Server Agent. To do this, follow these steps:

1. On the computer that is running SQL Server, start SQL Enterprise Manager.
2. Expand Microsoft SQL Servers, expand SQL Server Group, expand (Your_SQL_Server) (Windows NT).
   
   Note If you have not named the server that is running SQL Server, the default name of the server will be "(local)".
3. Expand Management, and then expand SQL Server Agent.
4. Right-click Jobs, and then click New Job.
5. Click the General tab, and then type a name for this job. For example, type Table_Cleanup.
6. Click to select the Enabled check box, and then click an owner for this job in the Owner list. Leave the default values in the other boxes. You may type a description for this job in the Description box.
7. Click the Steps tab, and then click New.
8. In the Step Name box, type a name for the step. For example, type Step 1.
9. In the Type box, click Transact-SQL Script (TSQL), and then click ProjectServer in the Database list.
10. In the Command box, type the following commands. Press ENTER after each command.
    TRUNCATE TABLE MSP_CUBE_ASSN_FACT
    TRUNCATE TABLE MSP_CUBE_RES_AVAIL_FACT
    TRUNCATE TABLE MSP_CUBE_TIME_BY_DAY
11. Click OK.
12. Click the Schedules tab, and then click New Schedule.
13. In the Name box, type a name for the schedule. For example, type Cleanup_Schedule.
14. Click to select the Enabled check box.
15. Under Schedule, configure an appropriate schedule to run this job. Run the job for about 45 minutes before you build the OLAP cube.
16. Click OK, and then click the Notifications tab.
17. Select an appropriate method to notify the operator when the job is completed.
18. Click OK.
19. Quit SQL Enterprise Manager.

Method 3: Increase the time-out value for the OLAP cube build process

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

By default, the OLAP cube build time-out value is set for 30 minutes (1800 seconds). You can increase the time-out value. When you increase the time-out value, the cube data staging process in SQL server has more time to complete successfully.

Note For single server deployments, you will have to apply the following registry key on the front-end Web server on with Project Server is installed. If you have a multiple server deployment of Project Server, apply the following registry key on your views servers if the Views service is offloaded.

To increase the time-out value, follow these steps:

1. On the server, click Start, click Run, type regedit, and then click OK.
2. Locate the following registry subkey:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\11.0\MS Project
3. On the Edit menu, point to New, and then click Key.
4. Type CubeProcess as the name of the new key, and then press ENTER.
5. Right-click the CubeProcess registry key, point to New, and then click String value.
6. Type SQLTIMEOUT as the name of the new registry entry, and then press ENTER.
7. Right-click SQLTIMEOUT, and then click Modify.
8. Change the value of the SQLTIMEOUT registry entry. By default, this value is set at 1800 seconds (30 minutes). You can increase the time-out value in 15-minute increments until the cube build process no longer times out. For example, to set the time-out value to 45 minutes, change the value of the SQLTIMEOUT registry entry to 2700. Or, to set the time-out value to 60 minutes, change the SQLTIMEOUT registry entry to 3600.
9. Close Registry Editor.

You may notice some improvement in cube build performance if you use a combination of methods 2 and 3. The delete process can be time consuming, especially when there are large amounts of data to remove.