Rebuild the Data Warehouse and Analysis Services Cube

Article
02/04/2013

Whenever you move, restore, rename, or fail over the data-tier server for Team Foundation, you must rebuild the warehouse and cube to access high-level reports. Also, if you move or delete a team project collection, you must rebuild the warehouse and cube. The warehouse for Team Foundation aggregates all operational data from other subsystems in Visual Studio Team Foundation Server. The warehouse corresponds to the relational database, Tfs_Warehouse, and the cube corresponds to the SQL Server Analysis Services database, Tfs_Analysis

When you rebuild the warehouse or cube, Team Foundation Server performs the following sequence of actions:

Takes the databases offline.
Drops the schema for both databases.
Re-creates the schema for both databases.
Brings both databases back online.
Restarts the warehouse adapter jobs.
Processes the cube according to its regular processing interval.

With the rebuild operation, you re-create both the relational database and the cube. Team Foundation Server rebuilds the relational database from the operational data stores. It then re-creates the SQL Server Analysis Services cube and processes the cube to populate it by using data from the relational database. If you want to rebuild just the cube, you can use the TFSConfig RebuildWarehouse command-line tool. For more information, see RebuildWarehouse Command.

Important

You should not have to rebuild the data warehouse during normal operations. Depending on the amount of data in the data warehouse, the rebuild operation can take several hours to finish, during which time reports are not available.

If you encounter schema conflicts, you cannot resolve this by rebuilding the data warehouse. To modify the reporting configuration for team project collections, you can use the Edit function that is provided on the Reporting page of the administration console for Team Foundation. Also, you can manually process the warehouse and cube on demand. For more information, see the following topics:

In this topic

To rebuild the data warehouse for Team Foundation, you must perform the following sequence of steps:

Verify That Services and Application Pools are Running and That TCP/IP Is Enabled for SQL Server
Rebuild the Data Warehouse
Restart Services
Verify That Reports Can Be Accessed

Required Permissions

To perform these procedures, you must have the following permissions:

You must be a member of the sysadmin security group on the server or servers that are running the instance of SQL Server that hosts the databases for Team Foundation Server.
You must be a member of the sysadmin security group on the server for the Analysis Services database for Team Foundation.
You must be a user of the Tfs_Warehouse relational database.
You must be a member of the TFSEXECROLE database role.
You must be a member of the Team Foundation Administrators security group and the Administrators security group on the server or servers that are running the administration console for Team Foundation.
Your server-level Administer warehouse permission must be set to Allow.
Your View project-level information permission on the team project must be set to Allow to run and view a report. Also, you must be a member of the Browser role in SQL Server Reporting Services.

For more information, see Team Foundation Server Permissions.

In addition to these permissions, you might need to address the following requirements on a computer that is running Windows Server 2008 or Windows Vista:

To follow a command-line procedure, you might need to open an elevated Command Prompt by clicking Start, right-clicking Command Prompt, and clicking Run as Administrator.
To follow a procedure that requires Internet Explorer, you might need to start it as an administrator by clicking Start, clicking All Programs, right-clicking Internet Explorer, and then clicking Run as administrator.
To access Report Manager, reports, or Web sites for Reporting Services, you might need to add these sites to the list of trusted sites in Internet Explorer or start Internet Explorer as an administrator.

For more information, see the Microsoft Web site.

Verify That Services and Application Pools are Running and That TCP/IP Is Enabled for SQL Server

The services that SQL Server requires must be running for you to complete the steps in this procedure. You stop Reporting Services so that users do not access reports while you are rebuilding the data warehouse. Also, for a dual-server deployment, the TCP/IP protocol must be enabled for each instance of a SQL Server database.

Note

By default, TCP/IP is disabled when you install SQL Server.

To verify that services and application pools are running and that TCP/IP is enabled for SQL Server

Log on to the appropriate server, open Computer Manager, and then verify that the services and application pools in the following table are running:

Log on to the server that hosts this program	Component
SQL Server Analysis Services	SQL Server Analysis Services (MSSQLSERVER or TFSInstance).
Team Foundation databases	SQL Server (MSSQLSERVER or TFSInstance). SQL Server Agent (MSSQLSERVER or TFSInstance).
Application tier	Microsoft Team Foundation Server Application Pool.

For more information, see Stop and Start Services, Application Pools, and Websites.

Log on to the server that hosts Reporting Services, and stop the following services:
- SQL Server Reporting Services (TFSINSTANCE).
- ReportServer or ReportServer$InstanceName (application pool).
  
  Note
  
  You manage ReportServer for SQL Server 2005 but not for SQL Server 2008.
Make sure that TCP/IP has been enabled for SQL Server on the data-tier server. For more information, see How to: Enable the TCP/IP Protocol for a Database Instance.