Is the TFS Database(s) schema documented and how do I create new reports?

 

The seven TFS databases created by Team foundation Server should not be used directly for reporting but rather the TFS Data Warehouse is created for this purpose. Team Foundation Server includes a data warehouse where data from work item tracking, source control, builds, testing tools, and third-party tools (if any) are stored. This data is used by Team Foundation for its built-in reporting functionality. The warehouse includes both a relational and an OLAP database.

The relational database is organized in an approximate star schema, and the OLAP database is a data cube derived from the relational database. Each Team Foundation tool contributes one or more facts to the data warehouse. Each type of fact has a primary relational table and a cube in the OLAP database that draws from that table. Each fact is composed of dimensions, measures, and details. Dimensions are properties that are used to slice the data in a report. For example the Work Item facts include Type and State dimensions. Dimensions are realized in the relational database as tables, with foreign key references to the dimension tables from the primary fact table. Measures are properties that are aggregated (counted, added, and so on) to provide the quantitative values in reports. The Code Churn cube provided by the source control tool, for example, includes Lines Added, Lines Deleted, and Lines Modified measures. Measures are columns in the primary fact table. Details are columns that appear in the relational database only. They are generally longer text strings like work item titles that are primarily used in list reports that can be drawn as efficiently from a relational database as it can from an OLAP database. Details are columns in the primary fact table that do not appear in the OLAP cube. All the facts have a project dimension that can be used to slice the data in the warehouse by team project. 

To create a new report open Visual Studio 2005 and create a new “Business Intelligence” project of type “Report Server Project Wizard” In the wizard change the data source type to “Microsoft SQL Server Analysis Services”.   Choose edit on the connection string and type the name of your TFS Server, under the databases drop down you should see “TFSWarehouse” selecting this will now enable to see all the measures and dimensions to create custom reports.

TFS Reporting features   

http://msdn2.microsoft.com/en-us/library/ms194922.aspx

Data Warehouse Architecture: 

http://msdn2.microsoft.com/en-us/library/ms244687.aspx

Understanding the Cube

http://msdn2.microsoft.com/en-us/library/ms244711.aspx

Relational schema ref.:            

http://msdn2.microsoft.com/en-us/library/ms244691.aspx

Fact tables:                          

http://msdn2.microsoft.com/en-us/library/ms244679.aspx

Dimension tables:                         

http://msdn2.microsoft.com/en-us/library/ms244714.aspx

Relationships:             

http://msdn2.microsoft.com/en-us/library/ms244707.aspx

OLAP design:                           

http://msdn2.microsoft.com/en-us/library/ms244710.aspx

Data warehouse admin:            

http://msdn2.microsoft.com/en-us/library/ms244706.aspx

Customizing work item types

http://msdn2.microsoft.com/en-us/library/ms404850.aspx

Work item type schema ref

http://msdn2.microsoft.com/en-us/library/aa337615.aspx

Additional Reports

http://blogs.msdn.com/tompatton/

Directions for modifying reports

http://blogs.msdn.com/team_foundation/archive/2005/02/09/370075.aspx

Building Reports in Excel

http://msdn2.microsoft.com/en-us/ms244699.aspx