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
.png "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
.png "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
.png "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. |
Fact Table for Related Current Work Items
.png "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. |