Work Item History tables

TFS 2017 | TFS 2015 | TFS 2013

You can query for historical data about bugs, tasks, and other types of work items by using FactWorkItemHistory and the associated dimension tables as the following illustration shows. Historical data provides information about the status of a work item or a value of a field for a work item as it changed over time. Progress and burndown charts are examples of reports that are built from work item history tables. The data is stored by using compensating records.

For information about the measures and dimensions that are associated with these tables in the SQL Server Analysis Services cube, see Test cases and work items.

FactWorkItemHistory is associated with the following dimension tables:

• DimArea

• DimIteration

• DimPerson

• DimTeamProject

• DimWorkItem

  You can use the following sample query to find the historical workload trend for the period between September 21, 2009 and September 30, 2009 for certain user stories. For each user story in the team project, this query returns information about the total completed work, the original estimated work, the remaining work, and the total story points for every day during that period.

declare @TeamProjectNodeSK int  
select @TeamProjectNodeSK = ProjectNodeSK from GetProjectNodeInfoFromReportFolder(N'/TfsReports/VSTSDF/ProcessDev10')  
-- This table value function returns the ProjectNodeSK: the Surrogate Key of a team project under a certain area path.  
  
declare @TeamProjectCollectionGuid nvarchar(36)  
select @TeamProjectCollectionGuid = pc.ProjectNodeGUID from DimTeamProject p inner join DimTeamProject pc on p.ParentNodeSK = pc.ProjectNodeSK where p.ProjectNodeSK = @TeamProjectNodeSK  
-- This query finds the team project collection GUID by joining TeamProject.ParentNodeSK to TeamProject.ProjectNodeSK  

select   
    d.DateSK  
    ,wi.System_Title  
    ,wi.System_Id  
    ,coalesce(sum(wih_child.Microsoft_VSTS_Scheduling_CompletedWork),   0) as Total_CompletedWork, -- Finds the total number of hours of completed work.  
    coalesce(sum(wih_child.Microsoft_VSTS_Scheduling_OriginalEstimate), 0) as Total_OriginalEstimate --Finds the total number of hours of original estimate.  
    ,coalesce(sum(wih_child.Microsoft_VSTS_Scheduling_RemainingWork), 0) as Total_RemainingWork--Finds the total number of hours of remaining work.  
    ,coalesce(sum(wih_child.Microsoft_VSTS_Scheduling_StoryPoints), 0) as Total_StoryPoints --Finds the total story points.  
from  
    DimDate d  
cross apply  
    DimWorkItem wi  
cross apply  
    GetWorkItemsTree(@TeamProjectCollectionGuid, wi.System_Id,        
N'Child', d.DateSK) wit   
left join            
    FactWorkItemHistory wih_child     
        on wih_child.WorkItemSK = wit.ChildWorkItemSK  
where  
    d.DateSK >= N'2009-09-21 00:00:00.000'   
    and d.DateSK <= N'2009-9-30 00:00:00.000'  
    and wi.TeamProjectSK = @TeamProjectNodeSK   
    and wi.System_WorkItemType = N'User Story'   
    and wi.System_ChangedDate <= d.DateSK  
    and wi.System_RevisedDate > d.DateSK  
    and wi.System_State = N'Active'  
    and (wih_child.RecordCount != -1 or wih_child.RecordCount is null)  
group by d.DateSK, wi.System_Id, wi.System_Title  

For more information, see these articles:

• COALESCE (Transact-SQL)
• Test cases and work items
• Burndown
• Test Team Progress
• Table reference for the relational warehouse database