Customized VSTS Reporting Part IIIa - The Relational "Build" Schema

In an earlier post I promised to dig deeper into the mystery that is the TFSWarehouse schema. I don't think anyone will question the fact that you must have a firm understanding of the underlying data if you are going to develop customized reports for any data source, and Team Foundation Server is certainly no exception to this rule.

There are many different logical "segments" of data in the TFSWarehouse, so we'll break these down and take each one individually.

One of the more common questions surrounding TFS reporting is how to create useful reports on Builds. Fortunately the Builds "schema" (This is a bad term to use here, but I do it anyway - it represents the logical grouping of entities that are related to Builds) in the TFSWarehouse relational database is pretty easy to understand.

The following picture shows a logical diagram of the tables related to Build information and how they are related:

 

The major entities represented here are:

• Build - Contains information collected from MSBuild such as the Build Type, Drop Location and Start Time
• Build ChangeSet - Contains information on the changesets contained in a specific build
• Build Details - Contains information on Build Status, such as the date, duration, status and Team Project
• Build Project - Contains information about a specific instance of a build. (This is probably the most descriptive table for build dashboard type reporting)
• Build Flavor - Essentially a "snowflake" on the Build Project table that contains build "flavor" information
• Build Status - Stores the build "states" as the build progresses and is used in the Details table.
• Build Quality - Contains a quality indicator on each build

There are many ways to use this information in customized reports, but one simple use of this data would be to provide a report that listed the dates of each build, their drop locations and the changesets used by the build. The query would look like this:

   1: SELECT

   2:     BD.[Date],

   3:     B.[Build Type],

   4:     B.[Build],

   5:     BC.[Changeset],

   6:     B.[Drop Location],

   7:     B.[Build Start Time],

   8:     BS.[Build Status]

   9: FROM [TfsWarehouse].[dbo].[Build] B

  10: INNER JOIN [TfsWarehouse].[dbo].[Build Details] BD

  11: ON B.__ID = BD.__ID

  12: INNER JOIN [TfsWarehouse].[dbo].[Build Changeset] BC

  13: ON B.__ID = BC.__ID

  14: INNER JOIN [TfsWarehouse].[dbo].[Build Status] BS

  15: ON B.__ID = BS.__ID

 

Hopefully this helps make the build info stand out some and makes it a bit easier to develop build-related reports from the TFSWarehouse.