Work Items Schema

Data about work items is organized around four fact tables in the data warehouse for Team System. The following sections describe the contents of each fact table and the relationships that they have to the dimension tables. They also describe the dimension tables that are specific to the work item schema. For more information about fact tables and dimension tables, see Fact Tables and Dimension Tables.

The fact tables for work items are:

  • Fact Table for Current Work Items (dbo.Current Work Item)

  • Fact Table for Work Item Changesets (dbo.Work Item Changeset)

  • Fact Table for Work Item History (dbo.Work Item History)

  • Fact Table for Related Work Items (dbo.Related Current Work Item)

The fact tables for work items reference the following dimension tables:

  • Dimension Table for Work Items (dbo.Work Item)

  • Dimension Table for Changesets (dbo.Changeset)

  • Dimension Table for Builds (dbo.Build) (see Build Schema)

  • Dimension Table for Team Projects (dbo.Team Project) (see Shared Dimensions)

  • Dimension Table for People (dbo.Person) (see Shared Dimensions)

  • Dimension Table for Dates (dbo.Date) (see Shared Dimensions)

  • Dimension table for Areas (dbo.Area) (see Shared Dimensions)

  • Dimension Table for Iterations (dbo.Iteration) (see Shared Dimensions)

Fact Table for Current Work Items

Schema for the work item fact tables

This fact table stores the current version of all work items. The following table describes the columns in the fact table.

Field

Description

__ID

Identifying number for the record in the table (used internally).

Logical Tracking ID

Identifying number that is used by the work item tracking warehouse adapter to determine how much work item history is already present in the warehouse.

__LastUpdatedTime

Date and time the record was last inserted or updated.

__TrackingId

Identifying number that is used by the warehouse infrastructure to track the record.

Work Item

Foreign key to the Work Item dimension table.

Assigned To

Foreign key to the Person dimension table representing the Assigned To value.

Changed By

Foreign key to the Person dimension table representing the Changed By value.

Created By

Foreign key to the Person dimension table representing the Created By value.

Area

Foreign key to the Area dimension table.

Iteration

Foreign key to the Iteration dimension table.

Team Project

Foreign key to the team project dimension table that indicates the team project.

Date

Foreign key to the Date dimension table.

System_CreatedDate

Foreign key to the Date dimension that indicates the date the work item was created.

Microsoft_VSTS_Common_ActivatedDate

Foreign key to the Date dimension that indicates the date the work item was last activated.

Microsoft_VSTS_Common_ResolvedDate

Foreign key to the Date dimension that indicates the date the work item was last resolved.

Microsoft_VSTS_Common_ClosedDate

Foreign key to the Date dimension that indicates the date the work item was last closed.

Microsoft_VSTS_Test_TestName

Name of the test that was run on the task code.

Microsoft_VSTS_Test_TestId

ID number of the test that was run on the task code.

Microsoft_VSTS_Test_TestPath

Full path of the test that was run on the task code.

Microsoft_VSTS_Build_FoundIn

Foreign key to the Build table that indicates the build in which a work item was found. In the MSF for Agile Software Development process template, this is only available with the Bug work item type.

Microsoft_VSTS_Build_IntegrationBuild

Foreign key to the Build dimension table that indicates the build which contains the code changes that resolved this work item.

_Microsoft_VSTS_Scheduling_RemainingWork

Estimate of the number of hours remaining to complete the task.

_Microsoft_VSTS_Scheduling_CompletedWork

Number of hours that have been completed for this task.

_Microsoft_VSTS_Scheduling_BaselineWork

Number of hours of work from the baseline plan.

Microsoft_VSTS_Scheduling_StartDate

Foreign key to the Date dimension table that indicates the start date associated with this work item.

Microsoft_VSTS_Scheduling_FinishDate

Foreign key to the Date dimension table that indicates the finish date associated with this work item.

Fact Table for Work Item Changesets

Work Item Changeset Schema

This fact table stores links between work item and changesets. The following table describes the columns in the fact table.

Field

Description

__ID

Identifying number for the record in the table (used internally).

__LastUpdatedTime

Date and time the record was last inserted or updated.

__TrackingId

Identifying number that is used by the warehouse infrastructure to track the record.

Work Item

The record ID of the work item in the work item fact table.

Changeset

The record ID of the changeset in the changeset fact table.

Team Project

The team project where the link is stored.

Fact Table for Work Item History

Work Item History Schema

This fact table contains the full history of all work items. It uses a compensating record strategy to record the history at any point in time. The following table describes the columns in the fact table.

Field

Description

__ID

Identifying number for the record in the table (used internally).

Logical Tracking ID

Identifying number that is used by the work item tracking warehouse adapter to determine how much work item history is already present in the warehouse.

Record Count

A 1 or -1. When a new work item is created, a record is inserted with Record Count set to 1. When the work item is revised, two records are inserted. The first is identical to the previous record, except that it has -1 instead of 1 for the Record Count. The second has 1 for the Record Count and the rest of the record represents the new values in the work item.

Revision Count

This value is set to 1 for each revision of a work item. For records where Record Count is set to -1 (indicating a change to a work item), this value is NULL. Selecting rows that have revision count set to 1 will return all revisions of the work items.

State Change Count

Used to show activity like bug resolve rates or task completion rates. When the State field changes, the State Change Count is set to 1. State Change Count is set to NULL for any revision of the work item that does not change the State, and for all the records that have a Record Count of -1.

__LastUpdatedTime

Date and time the record was last inserted or updated.

__TrackingId

Identifying number that is used by the warehouse infrastructure to track the record.

Work Item

Foreign key to the Work Item dimension table.

Assigned To

Foreign key to the Person dimension table representing the Assigned To value.

Changed By

Foreign key to the Person dimension table representing the Changed By value.

Date

Foreign key to the Date dimension table. This indicates the date that the work item was last changed.

Created By

Foreign key to the Person dimension table representing the Created By value.

Area

Foreign key to the Area dimension table.

Iteration

Foreign key to the Iteration dimension table.

Team Project

Foreign key to the Team Project dimension table.

System_CreatedDate

Foreign key to the Date dimension that indicates the date the work item was created.

Microsoft_VSTS_Common_ActivatedDate

Foreign key to the Date dimension that indicates the date the work item was last activated.

Microsoft_VSTS_Common_ResolvedDate

Foreign key to the Date dimension that indicates the date the work item was last resolved.

Microsoft_VSTS_Common_ClosedDate

Foreign key to the Date dimension that indicates the date the work item was last closed.

Microsoft_VSTS_Test_TestName

Name of the test that was run on the task code.

Microsoft_VSTS_Test_TestId

ID number of the test that was run on the task code.

Microsoft_VSTS_Test_TestPath

Full path of the test that was run on the task code.

Microsoft_VSTS_Build_FoundIn

Foreign key to the Build table that indicates the build in which a work item was found. In the MSF for Agile Software Development process template, this is only available with the Bug work item type.

Microsoft_VSTS_Build_IntegrationBuild

Foreign key to the Build dimension table that indicates the build which contains the code changes that resolved this work item.

_Microsoft_VSTS_Scheduling_RemainingWork

An estimate of the number hours of work remaining to complete the work for the selected dimension.

_Microsoft_VSTS_Scheduling_CompletedWork

The number of hours that have been completed for the selected dimensions.

_Microsoft_VSTS_Scheduling_BaselineWork

The number of hours of work from the baseline plan for the selected dimensions.

Microsoft_VSTS_Scheduling_StartDate_Date_ID

Foreign key to the Date dimension table that indicates the start date associated with this work item.

Microsoft_VSTS_Scheduling_FinishDate_ID

Foreign key to the Date dimension table that indicates the finish date associated with this work item.

Related Work Item Schema

This fact table stores links between related work items, as the following table describes.

Field

Description

Current Work Item_TrackingIDLeft

Foreign key to Current Work Item fact table for the source of the link.

Current Work Item_TrackingIDRight

Foreign key to Current Work Item fact table for the target of the link.

__LastUpdatedTime

Date and time at which the record was most recently inserted or updated.

You can join this fact table with the fact table for Current Work Items to query for work items and linked work items.

FROM [Related Current Work Item] Link
INNER JOIN [Current Work Item] Left
On Link.[Current Work Item_TrackingIDLeft] = Left.__TrackingID
INNER JOIN [Current Work Item] Right
On Link.[Current Work Item_TrackingIDRight] = Right.__TrackingID

Dimension Table for Work Items

This dimension table stores the values of work item fields that are not otherwise stored in shared dimensions. For example, the values of the State field are stored in the dimension table for work items, but the values of the Assigned To field are stored in the shared person dimension. The following table describes the columns in the dimension table for work items.

Field

Description

__ID

Identifying number for the record in the table (used internally).

Work Item

The identifier; derived from the work item ID and revision.

Previous State

State of the previous revision of the work item.

__LastUpdatedTime

Date and time the record was last inserted or updated.

__DimensionMemberActive

Whether the dimension member has been fully populated (used internally).

System_Id

Represents the work item ID as the work item is known in the operational system.

System_Title

Title of the work item.

System_State

State of the work item.

System_Rev

Work item revision.

System_Reason

Reason associated with the last state change.

System_WorkItemType

The type of work item (Bug, Task, and so on).

Microsoft_VSTS_Common_Issue

Value used to mark a work item to appear in the issue report.

Microsoft_VSTS_Common_ActivatedBy

Person who last activated the work item.

Microsoft_VSTS_Common_ResolvedBy

Person who last resolved the work item.

Microsoft_VSTS_Common_ClosedBy

Person who last closed the work item.

Microsoft_VSTS_Common_Priority

A subjective importance rating used to determine which tasks to complete first. Valid values are 1, 2, and 3.

Microsoft_VSTS_Common_Triage

Sub-states for a task awaiting a triage decision (used during the Proposed state). Valid values are Pending (default), More Info, Info Received, Triaged.

Microsoft_VSTS_Common_ExitCriteria

Flag to determine whether this work item should be tracked on the project checklist.

Microsoft_VSTS_Common_Rank

Stack rank used to prioritize work.

Microsoft_VSTS_Scheduling_TaskHierarchy

A string representing Microsoft Project context for the given task.

See Also

Concepts

Fact Tables

Dimension Tables

Current Work Item Perspective

Work Item History Perspective

Other Resources

Relational Schemas