Share via


Generate Reports Using 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.

Team Foundation Warehouse

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.

Build Fact Tables

Fact Table

Description

Build Details Tables

Basic information about each build, such as the build status and quality.

Build Changeset Tables

Changesets that were included in each build.

Build Project Tables

Files that were built and the platforms and flavors of the builds.

Build Coverage Tables

The extent to which the code was covered by tests that were performed against the build.

For more information about builds, see Building 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.

Fact Tables about Source Code

Fact Table

Description

Code Churn Tables

Basic information about each build.

Build Changeset Tables

Changesets that were included in the build.

Build Project Tables

Files that were built and the platforms and flavors that were used.

Work Item Changeset Tables

Links between work items and changesets.

For more information about version control, see Using 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.

Fact Tables for Tests

Fact Table

Description

Test Result Tables

Tests and their results.

Run Coverage Tables

The extent to which the code was covered by tests in a test run.

Build Coverage Tables

The extent to which the code was covered by tests that were performed against the build.

Work Item Test Result Tables

Links between work items and test results.

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.

Fact Tables for Tasks, Bugs, and Other Work Items

Fact Table

Description

Current Work Item Tables

The current state of each work item.

Work Item History Tables

The full history of each work item.

Work Item Link History Tables

The full history of links between work items.

Work Item Category Tables

The categories that are used to associate similar types of work items.

Work Item Changeset Tables

Links between work items and changesets.

Work Item Test Result Tables

Links between work items and test results.

For more information, see Track Work and Manage Workflow.

See Also

Concepts

Create, Customize, and Manage Reports for Visual Studio ALM