Table reference for the relational warehouse database

TFS 2017 | TFS 2015 | TFS 2013

You can create reports and query for data by using the warehouse database. The data in the warehouse is collected from the Azure DevOps operational stores and organized in a set of tables, views, and table-valued functions that are designed for creating reports.

The warehouse contains data about:

• Builds
• Source code
• Test results and code coverage
• Work items like tasks and bugs.

The relationships between these sets of data are created in Azure DevOps and kept in the warehouse. That's why you can create queries to explore relationships between these integrated sets of data and better understand what is happening on your projects.

The data in the warehouse is stored in fact and dimension tables. This pattern for storing data in star and snowflake schemas is described in the following Microsoft article: Introduction to Dimensions.

Some other common patterns occur in the warehouse:

• All fact tables reference DimTeamProject, because all data in Azure DevOps is organized by projects and project collections. Some dimensions also reference the DimTeamProject so that you can more easily use them as project-filter parameters in your reports.
• All fact tables reference DimDate to indicate the date on which the fact occurred.
• Many fact tables reference DimPerson, sometimes more than once. For example, the work item facts reference DimPerson to indicate who a work item is assigned to and who changed it most recently.

Builds

You can query for data about builds by using four fact tables, as the following illustration shows. Each fact table uses the build dimension table DimBuild to store many details about the builds.

For more information about builds, see Build.

Source Code

You can query for data about code and other files that are under version control by using the fact tables in the following illustration. DimFile and DimChangeset are the two primary dimensions that are associated with these fact tables. DimFile provides information about files and folders, without regard to specific versions of those files. DimChangeset provides information about the changesets.

For more information about version control, see Code.

Tests

You can query for information about test results and analyze how well the tests cover the code by using the tables in the following illustration.

For more information, see Testing overview and Using Code Coverage to Determine How Much Code is being Tested.

Tasks, bugs, and other types of work items

You can query for information about tasks, bugs, and other types of work items by using the work item tables in the following illustration. Each work item fact table uses the work item dimension table to store many details about the work items.