Table reference for the relational warehouse database for Visual Studio ALM

You can create reports and query for data from Visual Studio Application Lifecycle Management (ALM) by using the warehouse database. The data in the warehouse is collected from the 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, and work items such as tasks and bugs. The relationships between these sets of data are created in Visual Studio ALM and retained in the warehouse. Therefore, you can create queries to explore relationships between these integrated sets of data and better understand what is happening on your projects.

In this topic:

• Builds

• Source Code

• Test Results

• Code Coverage

• Tasks, Bugs, and Other Types of Work Items

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 topic on the Microsoft Web site: Introduction to Dimensions.

Some other common patterns occur in the warehouse:

• All fact tables reference DimTeamProject, because all data in Visual Studio ALM is organized by team projects and team 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 to whom 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 the application.

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 Use version control.

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 the application 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.

For more information, see Track work with Visual Studio ALM and TFS.

See Also

Concepts

Create, customize, and manage reports for Visual Studio ALM