Administrative Report Pack for Team Foundation Server 2010 and 2012

Grant Holliday’s blog

Senior Service Engineer, Microsoft Visual Studio Team Foundation Service

Administrative Report Pack for Team Foundation Server 2010 and 2012

  • Comments 21

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:

image

 

Current Processing Status

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

image

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:

image

 

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.

image

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.

image

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.

image

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.

image

Found 2 fields that match ‘Found In’

Cube Processing Status

This report shows list of recently completed Cube processing jobs.

image

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

  • Grant, we are currently running TFS 2008, will these reports translate down?

  • @Matt - No, these are TFS2010 only.  TFS2008 used a different architecture for processing the warehouse (the processing history isn't stored anywhere) and the blocked fields situation doesn't exist, because there's only one server.

  • Grant seems to be a possible bug in the reports? Any ideas?

    social.msdn.microsoft.com/.../b7994619-424f-4f90-80e1-720684c11039

  • @Allen - We're testing a fix to the reports now.  This post will be updated once it's validated.

  • @Allen - the download link has been updated with a fixed report that includes the 'Optimize Databases' job.

  • @Grant - I downloaded and tested the new version and all looks good. Thanks!

  • Grant - Thanks for fixing the report and I've downloaded a copy and updated our TFS servers. It's all working now! :) Would love to see additional useful reports being released. ;)

  • Thanks for the great reports!

  • Hi Grant, I downloaded the zip file and expanded it but do not see the install doc.

  • @Carol - I just downloaded it myself and extraced it. There is a file in the AdminReportPack sub directory called "Installing Admin Report Pack for TFS.docx". If you are still having problems, send me an email via blogs.msdn.com/.../contact.aspx and I will send you the doc.

  • I am trying to run the reports and I am getting the following error when running the Cube Status report:

    An error has occurred during report processing. (rsProcessingAborted)

    Query execution failed for dataset 'dsProcessingStatus'. (rsErrorExecutingCommand)

    Invalid object name 'tbl_jobqueue'.

    I am running this with the admin account and it should have full access to everything.  Can you point me in the right direction to fix this error?

  • I am getting same error as previous comment, can anyone help me to fix this?

    An error occurred during client rendering.

    An error has occurred during report processing.

    Query execution failed for dataset 'dsProcessingStatus'.

    Invalid object name 'tbl_jobqueue'.

  • Grant - Thanks for the great reports! One question regarding the Reportable Fields report. I would like to add a column for "unused", could I just use report builder to add this parameter?

    Thanks again

    Cheers

  • Did anyone find an answer for the Invalid object name 'tbl_jobqueue' error mentioned above?

  • I am also getting the 'tbl_jobqueue' error message and have not found a resolution. I have installed the Administrative Report Pack on two different TFS instances with no success.

Page 1 of 2 (21 items) 12