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

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

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

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

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