If you want to do reporting against TFS there are three different data sources to consider. In this post I’ll go briefly through the possible data sources and there usage, pros and cons.
TFS Relational Warehouse
The TFS relational warehouse is an SQL database dedicated for reporting data inside TFS. The TFS Warehouse has a public schema and is the supported place to do SQL reporting against the TFS Data. The information in TFS Warehouse is an extract from the production data and is updated on interval (by default 2 h)
The TFS cube is an analysis database storing aggregated information from the TFS warehouse. The TFS Cube is most suited for reporting on trends and aggregated info. Reporting tools are primary Excel for AdHoc reporting and SSRS using MDX as the query language. For those not too comfortable with the MDX language there is a tool on codeplex (http://olappivottableextend.codeplex.com) that allows you to use excel to create the query and then lets you show extract the MDX query.
TFS Production data
If you want access to all details of the TFS implementation, without the time delay you need to go directly against the TFS production data. The only supported way to do so is by using the TFS API. The downside is that you need to develop and deploy a solution wrapping the TFS API and consume it in your report.
For most cases the TFS Warehouse is your first choice, unless you want to do reporting on trends or aggregated information the TFS Cube is probably your first choice . If you can’t do with the (small) time delay or need access to RitchTextFields or other values not present in the TFS warehouse, you will need to develop and deploy your own data source using the TFS API.