Using Custom Reports in TFS: Part 1 – Introduction in TFS Data

Introduction

As a TFS user you generate data during your projects. Depending on the process you follow and the artifacts you store in TFS this can be a small or a large amount.

In any case, the data you gather, is stored and will be stored in TFS for the rest of your days.

During my daily work I see a lot of people using TFS but there are only a few companies that use the TFS reporting functionality to the fullest. Probably this is because people are not familiar with the tooling, the data model and the data to create useful reports.

In this blog series I will talk about the different aspects of TFS reporting. I talked about this subject on the Dutch Techdays. This session can be found here. (session is in Dutch).

Using Custom Reports in TFS: Part 1 – Introduction in TFS Data

To create good reports, you need to understand how TFS stores and processes the data in the first place. In the picture below the different data stores are described.

The TFS Configuration database (Tfs_Configuration) contains all information that is needed to keep TFS up and running. It contains information about the different project collections, server security, events, notifications, jobs etc.

The TFS Configuration database registers every Team Project Collection. The Team Project Collection database is the operational store (Tfs_DefaultCollection). All data that you gather in all of your Team Projects is written to this database. Work Items, Version Control, Builds, Project Security etc.

All Collection Databases consolidate to the TFS Warehouse (Tfs_Warehouse ) database. Not all data in the operational store is send to the warehouse. For work items you can influence which data is send to the warehouse by setting the reportable attribute on work item fields (more information about the different attributes you can find here). For example if you add a field to a work item you can choose how the field is published to the warehouse (none, dimension, measure or detail). Most of the fields you add will be dimension fields (category, team name, priority etc.).

The TFS warehouse is a relational database. Once every two hours ( interval can be changed see http://msdn.microsoft.com/en-us/library/ms244694.aspx) the TFS Cube (Tfs_Analysis) is updated with new information. Only dimensions and measures are send to the cube.

Where to look for data for my reports

The TFS Warehouse and TFS Cube are the only 2 sources that you use for reporting. In some exceptional cases it can be handy to query the Collection database as well but this is not recommended or supported.

The cube contains all historical data already processed. For example a Burndown graph or another historical graph is typically drawn from the TFS Cube.

For the reports that I built I used the TFS Warehouse most of the times. First of all because you can use SQL as language (instead of MDX on the cube) and the refresh rate is almost instant (instead of every 2 hours)

What do I need to do to get some data?

This is sometimes an interesting problem. If you only use Version Control, the data for custom reports is not very rich. To give you an idea I describe 2 Out of the Box reports and what you need in order to fill them.

	Sprint Burndown Create Sprint Work Item with start and end date Create Product Backlog Items Create Tasks Assign PBI + tasks to sprint Set remaining work on tasks Update remaining work + status every day or more !
	Velocity Create Product Backlog Items Set StoryPoints Close ! PBI’s Assign to sprint

Only data that is managed will give you valuable information!

In my next post, I will describe the different tools to create TFS Reports