Service Engineer, Microsoft Visual Studio Online - Team Foundation Server
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:
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:
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.
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:
Here’s an overview of how the reports look and what questions you can answer with them.
Use this report when you want to answer the following questions:
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.
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:
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.
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.
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.
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’
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.
The tbl_jobqueue error I was getting was due to use of the wrong database when creating the required data source "Tfs2010ConfigurationDS" (described in "Installing Admin Report Pack for TFS.docx" in the zip file. Fixing the database as well as the connect string got the Cube Status report working for me.
You are correct, Bob. I had the connection string for Tfs2010ConfigurationDS pointing to Tfs_Warehouse. I changed it to Tfs_Configuration and the Cube Status report works fine now. The installation instructions were a little fuzzy on what database to use for that data source.
thank you very much.it will be very good report.but when I run "Cube Status" report,I get this error:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'dsProcessingStatus'. (rsErrorExecutingCommand)
Query (11, 22) Parser: The syntax for 'q' is incorrect.
could you tell me where is the problem?
thanks to "Bob" and "Gregory_F" I change the connection string and my error gone.thank you all.
We added the Cube status report to our TFS server to check optimisation of the databases. On Running it we can see the Optimise Database job running but there is no result status.
Start Time End Time Result Message
7/14/2013 1:00:01 AM 7/14/2013 1:06:00 AM [Optimize Databases]:
Should this have a success or failure message associated with it?
@Grant - Will these reports be ported to TFS 2013 in the near future? Thanks!