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


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 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.

clip_image004  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 !
clip_image006  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


7 Responses to “Using Custom Reports in TFS: Part 1 – Introduction in TFS Data”

  1. Renevo,

    Can you please help me understand the queries to generate the above mentioned reports i.e. SprintBurnDown and Velocity?

    I am getting bogged down between FactWorkItemHistory and FactWorkItemLinkHistory. I am not sure where and how to write those queries on TFS_WareHouse database.

    Thanks in advance.


    • Don’t look too much at the tables. Try to use the Views from the TFS_Warehouse Tables instead. Use the facttables to enrich your dataset when needed. WorkItemHistory contains all information about all revisions. LinksHistory all about links and revisions.. SO that seems a lot of duplicate data. You can use a Common Table expression to retrieve the last revision for example..

  2. I am looking for a way to export current sprint burn down chart data using SQL Select or API


  1. TFS Reports–Unleash them with PowerPivot | The Road to ALM - July 17, 2014

    […] Introduction in TFS Data […]

  2. Create a Trend chart in Visual Studio Online | The Road to ALM - September 1, 2014

    […] some of my previous posts on this blog, I really like reports. I wrote about how to customize them (here, here and here), how you should use them in your ALM implementation and how to use PowerPivot to […]

  3. Tfs 2010 Data Warehouse Refresh | Nitanitaa - July 16, 2016

    […] Using Custom Reports in TFS: Part 1 – Introduction in TFS … – 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 … […]

%d bloggers like this: