VSTS, TFS and Custom Reporting

One topic of conversation that seems to come up on a regular basis both around the office and around the 'net is how to make "better" the reports that ship with Visual Studio Team System and Team Foundation Server. I always take pause when I hear this, because I've never really considered it the job of the software manufacturer to provide reports for every conceivable use-case, and when you're talking about a product as vast and all-encompassing as VSTS/TFS is, I can't even imagine how many reports that would take. Then I stop and wonder if by "better" people really mean, "show the data that I want to see".

Team Foundation Server ships with a very well thought out (at least in my opinion) reporting infrastructure;  a data warehouse and an OLAP cube. These databases are designed with the idea that customers will use the data contained therein to create custom reports that represent the data elements they want to see (Rumor has it that the next VSTS release, "Rosario" will have even more to offer here, but I wouldn't know anything about that). The schema for both is simple enough that you don't need to be a TSQL or MDX genius in order to figure out how to develop compelling reports. For example, here's a subset of the WorkItem history schema in the TFS Warehouse database (this picture stolen from MSDN Patterns and Practices team by the way):

 

The logical representation above is implemented in a series of dimension tables and a fact table. The fact table schema looks like this:

As you can see, you can look at this schema and pretty easily tell how you can slice and dice data here to generate customized reports.

Given all this good information, I thought it would be a good idea to write a series of mini-articles on how to develop customized reports in VSTS/TFS. Over the next week I'll post the following here on this blog:

1. Getting started with VSTS/TFS customized reporting - What you need and how to configure the report development environment.
2. Creating a simple report using Visual Studio - How to use the wizard to create simple reports from both the relational and OLAP databases.
3. Deep dive into the TFS Warehouse schema - What data is available and what you can do with it
4. Deep dive into the TFS Warehouse OLAP cube - What data does the cube provide and how the built-in metrics help you.
5. Putting it together - developing specific reports to answer specific business requirements

If you do read this blog (and I know there's at least 1 of you out there - Hi Julian!) and would like to see something specific on this topic, please don't hesitate to let me know. (comment here so others can see)