Troubleshooting the Data Warehouse

Article
12/06/2010

This topic lists the following common issues that might occur when you work with the data warehouse for Team Foundation Server.

Data Stops Flowing from the Operational Store to the Data Warehouse
Data in a Report is Outdated and Does Not Appear to Be Updated

If you cannot resolve a problem after reviewing these tips and those in the individual help topic for each error message, visit the forums for Visual Studio Team System on the Microsoft Web site. These forums provide threads that you can search for information about a variety of troubleshooting topics and are monitored to provide quick responses to your questions.

Data Stops Flowing from the Operational Store into the Data Warehouse

If you suspect that data has stopped flowing into the data warehouse from the operational store, you can troubleshoot the problem by:

Confirming that the data flow has actually stopped.
Identifying the adapter that failed.

The following procedures will help you resolve the problem.

To confirm that data has stopped flowing

Make one or more of the following changes:
- Check in a changed file to source control.
- Update a work item.
- Publish a test result.
Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
In the Server Type list, in the Connect to Server dialog box, select Database Engine, and then click Connect.
In Microsoft SQL Server Management Studio, click New Query.
Confirm the change appears in the appropriate tables by running the following query:
```
select * from [work item] where __id = (select max(__id) from [work item]) 
```
If the change does not appear, the data has stopped flowing.

To identify the failed adapter

In a text editor, open \Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Web Services\Web.Config.
In the line <add name="General" value="0" />, replace "0" with "3" and save the changes.

This change enables debug messages.
On the application-tier computer for Team Foundation Server, click Start, and then click Run.
In the Open box, type inetmgr.
In the left pane of Internet Information Services Manager, click the Team Foundation Server node.
On the Action menu, click Open.
By using a program that listens to system trace events, start tracing on the application tier.

Many freeware programs provide tracing and debugging capabilities using the .NET tracing capabilities, or custom applications can be built to provide this functionality. For more information about how to use tracing and debugging capabilities, see "Trace Listeners" on the Microsoft Web site.
On the Capture menu, select all capture commands. Keep this program open to see all DebugView messages.
In the left pane of Internet Information Services Manager, expand the Warehouse node, and then click the v1.0 node.
In the right pane, click warehousecontrolled.asmx.
On the Action menu, click Browse.
In the ControllerService, click Run, and then click Invoke.
By using your trace monitoring application from step 7, review the log for trace exceptions that indicate which adapter is not functioning correctly.

Data in a Report is Outdated and Does Not Appear to Be Updated

If you suspect that the data in one or more reports is outdated and is not being updated or you receive the following error message: "An error has occurred during report processing. (rsProcessingAborted)," there may be a problem with the automatic updating of the data warehouse.

Data in the operational stores (work item tracking, version control, build, and integration services) is pulled into a relational database (TfsWarehouse) and then into an OLAP cube on a scheduled basis. The reports are rendered by querying the cube. By default, the Windows service that is named Visual Studio Team Foundation Server Scheduler calls the warehouse Web service every hour to initiate the processing of the warehouse. The cube and reports should be available as this process continues in the background.

Before you start the troubleshooting, you should be a member of the Administrators security group on the application tier and data tier. The database and cubes are located on the data tier, whereas the reporting services site and reports are located on the application tier.

You can troubleshoot the problem by:

On the application tier, turn off Internet Explorer friendly error handling so that you can see all the messages.
Confirm whether the Windows service that is named Visual Studio Team Foundation Server Scheduler is running.

The Visual Studio Team Foundation Server Task Scheduler service signals the warehouse Web service to aggregate data and process the cube. Data is pulled from all the operational systems into the database TfsWarehouse, from there, data is processed into the cube.
Confirm the processing of the data pulled into the warehouse.
Manually process the warehouse.
Browse the reports.

The following procedures will help you resolve the problem.

To turn off friendly error handling in Internet Explorer

On the application tier, start Internet Explorer.
On the Tools menu, click Internet Options.
In the Internet Options dialog box, click the Advanced tab.
Clear the check box Show friendly HTTP error messages.

To confirm that the Visual Studio Team Foundation Server Task Scheduler service is running

On the data tier, click Start, point to Administrative Tools, and then click Services.
In the Services window, scroll to Visual Studio Team Foundation Server Scheduler, and confirm that the Status column says Started.

If the service is not started, right-click Visual Studio Team Foundation Server Scheduler, andthen click Start.
Determine the most recent time that the scheduler called the warehouse to process by browsing to %ProgramFiles%\Microsoft Visual Studio 2008 Team Foundation Server\TfsServerScheduler.

The XML file in that directory records the last time warehouse processing was tried.

To confirm that the processing pulled data into the warehouse

Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
In the Server Type list, in the Connect to Server dialog box, select Database Engine, and then click Connect.
In Microsoft SQL Server Management Studio, click New Query.

Determine how much data made it into the data cube by running the following query:

For Visual Studio Team System 2008 with Service Pack 1

Use TfsWarehouse 
select Setting as [Last Cube Processing] from _WarehouseConfig where ID ='LastProcessedTime' 
-- total revisions in the relational warehouse
use TfsWarehouse 
select top 1 __LastUpdatedTime as [LastVCWHWrite] from dbo.[Code Churn] with(nolock)orderby __LastUpdatedTime desc 
select top 1 __LastUpdatedTime as [LastWITWHWrite] from dbo.[Work Item History] with(nolock)orderby __LastUpdatedTime desc 
use TfsWarehouse 
select count(*)as [WH Revisions] from [dbo].[Work Item History] with (nolock) 
where [Record Count] <>-1 
use TfsWorkitemTracking 
select count(*)as [WIT Revisions] from [dbo].[WorkitemsLatestAndWere] with (nolock) 
use TfsWarehouse 
select max([Changeset ID]) as [WH Changeset] from [dbo].Changeset with (nolock) 
where __DimensionMemberActive = 1 
use TfsVersionControl 
select max(ChangeSetId) as [VC Changeset] from tbl_Changeset with (nolock) 
-- identities
use TfsWarehouse 
select Property_Value as [Warehouse Identity Id] from _PropertyBag 
where Property_Key ='CSS Identity Sequence Id' 
use TfsIntegration 
select max(sequence_Id) as [Integration Identity Id] from tbl_security_identity_cache  
-- structure
use TfsWarehouse 
select Property_Value as [Warehouse Structure Id] from _PropertyBag 
where Property_Key ='CSS Structure Sequence Id' 
use TfsIntegration 
select max(sequence_Id) as [Integration Structure Id] from tbl_nodes with (nolock)

For Visual Studio Team System 2008 (without service packs)

use TfsWarehouse
select Setting as [Last Cube Processing] from _WarehouseConfig where ID = 'LastProcessedTime'
-- total revisions in the relational warehouse
use TfsWarehouse
select top 1 LastUpdatedTime as [LastVCWHWrite] from dbo.[Code Churn] with (nolock) order by LastUpdatedTime desc
select top 1 LastUpdatedTime as [LastWITWHWrite] from dbo.[Work Item History] with (nolock) order by LastUpdatedTime desc
use TfsWarehouse
select count(*) as [WH Revisions] from [dbo].[Work Item History] with (nolock)
where [Record Count] <> -1
use TfsWorkitemTracking 
select count(*) as [WIT Revisions] from [dbo].[WorkitemsLatestAndWere] with (nolock)
use TfsWarehouse
select max ([Changeset ID]) as [WH Changeset] from [dbo].Changeset with (nolock) 
where DimensionMemberActive = 1
use TfsVersionControl
select max(ChangeSetId)  as [VC Changeset] from tbl_Changeset with (nolock)
-- identities
use TfsWarehouse
select Property_Value as [Warehouse Identity Id] from _PropertyBag 
where Property_Key = 'CSS Identity Sequence Id'
use TfsIntegration
select max(sequence_Id) as [Integration Identity Id] from tbl_security_identity_cache
-- structure
use TfsWarehouse
select Property_Value as [Warehouse Structure Id] from _PropertyBag 
where Property_Key = 'CSS Structure Sequence Id'
use TfsIntegration
select max(sequence_Id) as [Integration Structure Id] from tbl_nodes with (nolock)

If no data is present, or data is severely latent, either the scheduler is not signaling processing or there is a problem in the warehouse aggregating data.

To manually process the warehouse

Log on to the application tier.
Open https://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx.
Click GetWarehouseStatus, and then click Invoke.

Wait for the service to idle; it should return ProcessingAdapters (schema modifications), ProcessingOlap (schema modifications), ProcessingAdapters (pulling data), ProcessingOlap (processing the cube), and then idle.
Open https://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx.
Click Run and then click Invoke.

This starts the processing of the warehouse. The service returns True or False whether it has started processing (asynchronously).
You can repeat step 3 to monitor the processing.
To verify, you can repeat step 2 to validate data was moved over and then if there is a problem, see the "To gather error data" procedures in the following section for the time range of the processing.

To open the reports

In Team Explorer, expand the Reports node, and browse the individual team reports.

Each report has the time the warehouse last processed, expressed in the following form:

Report Generated: yyyy/mm/dd hh:mm:ss by domain\user; Last Warehouse Update: yyyy/mm/dd hh:mm:ss

The data should be current and less than an hour old.
If the data is more than an hour old, disable caching for the report:
1. In Internet Explorer, open http://<server>/Reports/.
2. In Report Manager, click the team project name, and then click the report.
3. Click the Properties tab and then click Execution.
4. Select Do not cache temporary copies of this report and then click Apply.
5. Click the View tab, and view the report.

To gather error data

Review the application event log to determine whether there are any errors, as shown as a red X, from the source 'Warehouse'. The error and error stack in that event log can help identify the problem.
If all the steps are leading to errors, gather a trace to help the support specialist or member of the product team diagnose the issue.
Use a tool such as DBMON to capture debug output.
In a text editor, open \Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Web Services\Web.Config.
In the line <add name="General" value="0" />, replace "0" with "4" and save the changes.

Change History

Date	History	Reason
July 2010	Corrected the directory path for the Web.config file, corrected the query to determine whether data has stopped flowing, and added a code sample for SP1.	Customer feedback.