Administrative Report Pack for Team Foundation Server 2010 and 2012

Update Dec 2012: This report pack works unmodified on TFS2012 as well as TFS2010.

One of the key components of TFS is the Data Warehouse, which is made up of a relational database and an Analysis Services cube.  In general, people don’t have a problem with the performance or operation of our Data Warehouse.  However there were two classes of problems that you’re likely to run into as your servers grow larger:

1. Processing Time – As the number of reportable fields increases, the number of dimensions that Analysis Services has to process also increases.  This increases the time it takes to process the cube and therefore the latency of the data is higher.
2. Schema Conflicts – In the simple case, when there are two fields in different collections (e.g. Priority) with the same name but a different type (e.g. String vs. Integer) this results in a schema conflict.  That project collection is then blocked from processing warehouse updates and the data in the warehouse & cube becomes stale.

Internally at Microsoft we started noticing some problems after we upgraded TFS2008 servers and consolidated them onto larger TFS2010 servers as new Team Project Collections (TPCs). There is a constraint (or feature, depending how you look at it) in the system that you can only have a single data warehouse per instance of TFS2010.  This feature enables you to do cross-project reporting for gathering data across all the projects in all collections on that server.  The downside of this though, is that when a field’s reporting settings change in one team project in one collection on a server, it can impact the data warehouse experience for everybody else on that server.

The good news is that through our dogfooding efforts on the Pioneer server, we found some issues early enough and made changes to the product before it shipped to avoid some other issues. While we were investigating and fixing these issues, we developed some reports that visualize the information that TFS stores about the health of the data warehouse to track our improvements.  Now we’re sharing those reports with you.

Please download them (below), install them, send us any feedback (comments on this blog are fine) and we’ll work on including them in the next official Power Tools.

These reports are useful to TFS administrators, operations/support teams, project administrators & end-users.  The reports in this pack display the following kinds of information:

• Recent processing times
• Current status (whether the cube is processing now and, if not, when it is scheduled to process next)
• Schema conflicts
• Most recent time that each adapter successfully ran

Interpreting the reports

In the download, there is a FAQ document which includes screenshots of what different reports mean and common questions. The contents of the FAQ are also available at Monitoring the TFS Data Warehouse – FAQ.

Requirements

• SQL Server Reporting Services 2008 or 2008 R2
• A shared datasource to which you connect the report, as the installation instructions describe how to configure.

Download: AdminReportPack.zip

Download and install this report pack to the Team Foundation Server Reporting Services Instance to monitor warehouse and cube processing status.

For information about how to install this report pack, see Installing Admin Report Pack for TFS.docx that is included in the download.

The pack includes:

• Admin Report Pack for TFS FAQ.docx
• Installing Admin Report Pack for TFS.docx
• Reports\Cube Status.rdl
• Reports\Blocked Fields.rdl
• Reports\Reportable Fields.rdl

Here’s an overview of how the reports look and what questions you can answer with them.

Cube Status

Use this report when you want to answer the following questions:

• How long is cube processing taking?
• How much time elapses between processing jobs?
• How often do the processing jobs run?
• Do errors occur with when the cube is processed?

Process Times:

Current Processing Status

This table tells you whether the warehouse is currently being processed or when it will be processed next.

The Next Run Time is in local time, and Run time is the duration of the job that is currently running.

Warehouse Job Status

This table lists all team project collections and all Data Adapter Jobs and displays how long ago the Warehouse and Cube data were updated. In addition, the table will show the schema conflicts and other Data Adapter errors that caused data to be out of date:

Showing Schema Merge Conflicts on the Warehouse Job Status View

If a conflict occurs when two schemas merge, the table will show the conflict with a link to the sub report that displays details about the blocked fields.

If you click the link that appears under the schema conflict error, a different report appears and shows the fields that are currently active and blocked for the blocked team project collection.

Blocked Field Changes

The following table shows blocked fields that have conflicts over all team project collections.

Blocked fields appear before the fields with which they are conflicting. The conflicting areas appear in bold.

Queued Field Changes

The following table shows field changes that are queued behind the blocked changes in the previous illustration.

After you resolve the blocked changes, the queued changes will then be applied to the warehouse.

Reportable Fields

This report shows all reportable fields in the deployment of Team Foundation Server. Administrators of team projects should review this report before they add a reportable field or change the properties of an existing field. The report helps prevent potential schema-merge conflicts. It lists fields across all collections, including any fields that are blocked.

Found 2 fields that match ‘Found In’

Cube Processing Status

This report shows list of recently completed Cube processing jobs.

I hope that you find these reports useful. Please comment on this post if you have questions or other feedback.