# Thursday, 29 May 2014

*Check out the 2015 update to this post

I’ve been answering a lot of questions on Team Foundation Server reporting over the last couple of weeks so I thought I’d put some of that information in a blog post.

Team Foundation Server is an amazing tool for storing information about your development projects and all that information can be retrieved if you know what the options are.

This post is targeted at TFS 2013 but most of it is relevant for TFS 2010 & TFS 2012

How does data flow in TFS?

This is probably the most concise reference of TFS reporting architecture and the various options that I can find: Create, customize, and manage reports for Visual Studio ALM

 

image

In a nutshell, when you work as normal in TFS - checking in source code, executing builds, running tests and completing work - all that information is being stored in transactional, operational stores within the Team Project Collection database (there is a separate collection database for each Team Project Collection that you create). 

These stores are designed to provide high transaction speed and data integrity in normalised tables meaning data isn’t duplicated.  As a result, it’s not very easy to work with these tables, it’s difficult to know which tables to use to get the data you want and queries can be slow because of all the joins required. 

A number of data adapters then populate a single, relational, data warehouse with the latest data from the stores.  Note that reportable data from all team project collections is published to that warehouse (Tfs_Warehouse) meaning that you can report across multiple collections (obviously, you should be careful who you give permissions to!)

Tfs_Warehouse has a schema that is much easier to understand because it is optimised for queries and reporting instead of transactions.  Additionally, this database can be on a completely different server so queries won’t slow down TFS.  The data warehouse is organised in a star-like schema into fact tables and dimension tables.

Finally, and every 2 hours by default, an Online Analytical Processing (OLAP) database (Tfs_Analysis) is populated to allow easy reporting on that information and this is where most of the standard reports will pull their data from.

Team Foundation Data Stores and Reports Overview

You can read more about the architecture used for TFS reporting in Components of the TFS data warehouse

Types of Report

What types of reporting are available in TFS?

  • Work Item Queries
  • Work Item Reporting
  • Out-of-the-box Excel Reports
  • Out-of-the-box SSRS Reports
  • Team Web Access Charting
  • Team Web Access standard reports
  • Custom Excel Reports
  • Custom SSRS Reports
  • SQL Queries
  • TFS API
  • REST API

Let’s look at them in a little more detail

Work Item Queries

Work Item queries are probably the simplest way of pulling information from TFS.  The data retrieved is always up to date as queries do not rely on the data warehouse. 

There are 3 types of query

For more information on Work Item Queries see Query for work items

TFS 2013 Update 2 adds the option to query on Work Item Tags.  If you have not installed TFS 2013 Update 2 or if you have TFS 2012 then  you cannot include tags in your Work Item Queries

Work Item Reporting

Work Item Reporting is the easiest route into custom reporting with TFS.  You can take a Flat List Query and within Excel click the New Report button on the Team menu

image

or in Visual Studio, click the Create Report in Microsoft Excel

image

From there you can choose to generate a report for the current state and/or the trend over time

Expanded New Work Item Report dialog box

TFS will generate a workbook with a number of reports on separate worksheets and a table of contents.  You are free to customise any of these reports and save the result for later use.

Here is an example of editing an Excel report Custom Burnup chart with TFS and Excel

For more detail, see Creating Reports in Microsoft Excel by Using Work Item Queries

Out-of-the-box Excel Reports

Depending on your chosen process template, and if you have installed SharePoint as part of your TFS deployment then you may have access to a number of standard Excel reports. 

These are included with both MSF templates (Agile & CMMI) but not with the Visual Studio Scrum template.  That said, with a little tweaking they could work with the Scrum template (eg, swap references to Story Points field used by MSF Agile to the Effort field used by Scrum)

They can be accessed through the SharePoint portal or from the Documents tab in Team Explorer.

Here is the Task Progress report as an example.

Task Progress Excel Report

See Excel Reports for more detail

Out-of-the-box SSRS Reports

Again, depending on your chosen process template (and assuming you have installed Reporting Services) then you will have access to a number of reports automatically when you create your Team Project.  They can be accessed via Web Access, SharePoint and Team Explorer.

View, upload, and organize reports (Reporting Services Reports)

Open the team project report site

For example, this is the standard Backlog Overview report available with the Scrum process template

Backlog Overview report

For a full list of available reports see Reports (SQL Server Reporting Services)

To view the reports, you must be assigned or belong to a group that has been assigned the Browser role in SQL Server Reporting Services. For more information, see Grant permissions to view or create reports in TFS.

Team Web Access Charting

Charts are new in TFS 2013 and are a bit like Work Item Reports but a lot simpler to create and not as customisable or detailed.  The big benefit is that you can pin these charts to your Home page in Team Web Access (you’ll need TFS 2013 update 2 for that) so they become very visible to your team

My good buddy, Colin, has an excellent write up on reports here although it pre-dates Update 2 so doesn’t mention pinning to the home page or customising colour on the reports (read about that here)

image

You can view charts with a Standard TFS Client Access License but to create them you must have a Full license which means Visual Studio Premium, Ultimate or Test Professional.  See more about Access Levels for Team Web Access here.

Team Web Access Charting

Team Web Access standard reports

Team Web Access has some useful reports which can be accessed very easily.  The reports are non-customisable but the good thing about them is that they do not rely on the data warehouse cube so they are never out of date.

Velocity

The Velocity diagram shows the number of Story Points (or whatever unit you are using to estimate User Stories/Backlog Items) scheduled and completed across iterations.  This will help with forecasting and release planning for your project.

Cumulative Flow Diagram

The CFD Diagram shows up to 30 weeks of data displaying the number of Work Items and their state over a date range.  If bulges are showing up on your CFD it could be an indicator of a bottleneck in your development process

Example CFD chart after a week

TFS 2013 Update 2 adds the ability to specify a start date for your CFD which can be very useful if you have a long running project.

image

Sprint Burndown

Finally, your Sprint Burndown shows the number of hours (assigned to the Tasks) your team has committed to in the sprint.  As work is completed (or added) then the value for Remaining Work on the Y-axis will change and an ideal trend line will show you when you are likely to complete the work if the current work rate continues

Sprint progress chart showing ideal trendline and actual work completed

TFS 2013 Update 2 finally added the much requested feature to be able to remove weekends from the TWA Burndown chart.  This means the burndown does not show the “steps” of a flat line when no work is completed at the weekend which could be a little demoralising.  Giles covers it in more detail here

image

Custom Excel Reports

Now you get into some proper custom reporting.  I still feel that the Work Item Query Reports covered earlier in the article are the easiest way to get started with Excel but you can connect to the Analysis Database manually if you like.

Take a look at Create a Report in Microsoft Excel for Visual Studio ALM which describes how to connect Excel to the TFS Analysis database.  Doing it this way means you are creating a report from scratch which is great if you know what you want but there is definitely a steeper learning curve.  Start with reading Perspectives and measure groups provided in the Analysis Services cube for Visual Studio

Custom SSRS Reports

If you have a SQL Server Reporting Services expert on your team then they may prefer to generate a report that way.  The benefit of using SSRS is that the report can be published to Team Foundation Server and it will be available alongside the OOB Reports in the Reports tab of Team Explorer or the Reports website.  It also allows for much more detailed reports than excel and allows you to plot multiple data points on the same report.

You can use:

Both report authoring tools can target Tfs_Analysis and Tfs_Warehouse although you may find Report Designer more complex to begin with.  You can view a comparison of the two tools here.

It may be easier to start with an existing report and customise it.  As well as the OOB reports, there are a number of sample reports available such as the Community TFS Report Extensions

Create a Detailed Report using Report Designer

Table reference for the relational warehouse database for Visual Studio ALM

Perspectives and measure groups provided in the Analysis Services cube for Visual Studio

If you create a custom report then you may want to add it to your Process Template so it is automatically available for any new projects created.

Add reports to the process template

SQL Queries

The majority of the reports we’ve discussed so far pull their data from the Analysis Services OLAP cube but although it’s generally more difficult, there is no reason why you can’t grab data directly from the relational data warehouse

The TFS Reporting Guide is generally a good resource for TFS reporting but it has some good examples of running T-SQL queries directly against the TFS Data Warehouse relational database.  For example, check out the Mean Time Between Failure (MTBF) Report which uses the CurrentWorkItemView to retrieve the current state of each work item from the dimension table.  The sample query fetches all Work Items of type Bug that are in a Closed state in the chosen Team Project and calculates the average time difference in hours between them.

image

You can also query the TFS operational stores but it’s not advised and you should really use the TFS API instead.  For example How to generate a report of active users who log onto the TFS server

TFS API

One of the great things about TFS has always been it’s customisability and there has always been a reasonably well documented API for pushing and pulling information to/from TFS.  For example you can create a TFS Work Item with about 3 lines of C# code.  You can also use the TFS API to create reports in a supported way.  If you have a look at the WCF Service sample for a Build Duration Report in the TFS Reporting Guide Data Warehouse section.

You’ll need to become familiar with Extending Team Foundation too.

REST API

A little while ago an OData Service was released for TFS to allow clients to be created for smartphones, tablets and non-Windows operating systems as long as they supported http requests. 

Team Foundation Server OData API

OData Service for Team Foundation Server v2

TFS Reporting Guide also has a whole section on this

This has now been enhanced as described in A new API for Visual Studio Online.  However, at this stage this API is only available for Visual Studio Online but it will likely be available for on-premise installations in the next version of TFS.

Some Questions

What about Visual Studio Online?

Visual Studio Online (formerly Team Foundation Service Preview) does not use SQL Reporting Services and so there is no data warehouse to connect the standard reports.  You still have the Team Web Access reports and the option to use the TFS API but in addition it is worth checking out the very cool REST API for Visual Studio Online which should make it easier to pull information from TFS Online

What permissions do I need to view and create reports?

Permissions for Excel Reports That Connect To the Analysis Services Cube

What is the permission needed to view and create a Excel report for TFS Analysis Services cube?

If you prefer to use Report Builder to edit or add Reporting Services Reports then it is slightly different, you’d need to have your account added to the Content Manager role for Reporting Services.

Grant permissions to view or create SSRS reports in TFS

I’m using Microsoft Test Manager with the Visual Studio Scrum template and the OOB reports are lacking.  What can I do?

There are some standard reports for testing such as the Test Plan Progress Report and the Test Case Readiness Report.  If that is not enough then it is worth checking out the (now slightly old but still relevant) blog posts here:

Test Case Management (TCM) Reporting - Frequently Asked Questions - Part 1

Test Case Management (TCM) Reporting - Frequently Asked Questions - Part 2

which gives a lot more information on custom excel reporting.  

Are there metrics associated with Builds and Continuous Integration and can we access historic build success ratios and failures etc.?

There is a full build log with diagnostics available for all builds (you can set a retention policy on them) and also the information is published to the data warehouse.  There are OOB reports (Build Summary, Build Success over Time) and options for custom reporting as discussed above.  See Analyze and report on build details and build coverage using the Build perspective for more detail.

I want to my reports to update more frequently

You can change the process control setting

Managing the data warehouse and analysis services cube

Change a Process Control Setting for the Data Warehouse or Analysis Services Cube

My TFS data warehouse is broken

Check your account permissions, review log files and rebuild the warehouse.

Manually process the data warehouse and analysis services cube for Team Foundation Server

TFS Admin Console - Rebuild the Data Warehouse and Analysis Services Cube

TFSConfig Comman Line - RebuildWarehouse Command

I don’t have Reporting Services or SharePoint installed

No problem, get them installed, start generating your reports or sharing your Excel workbooks with your colleagues.

Install Reporting services

Install SharePoint

Upload reports to a team project

Summary

Hopefully someone will find this post useful and I know I am likely to redistribute a lot of the information within it in the future.  You can see that there are many options for pulling data and generating reports from Team Foundation Server and you just need to figure out which best suits your needs.

Thanks,

Richard



.

Thursday, 29 May 2014 12:41:49 (GMT Daylight Time, UTC+01:00)  #    Comments [0]