Get the TFS 2015 Work Item Hierarchy in SQL Server

Question

I'm trying to create a report in Tableau to generate a tree structure of all the work items in TFS 2015 and their respective hierarchy Such as

Epic->Features->User Story->Task

But repeated attempts to create the sql query have failed. Could you please help me with the SQL query that could help fetch all the work items and display their hierarchy?

Thanks.

Answer 1

So, i have found this query, which is working and can help you create a tableau workbook for the hierarchy:

 WITH    cte
          AS ( SELECT   DimTeamProject.ProjectNodeName ,
                        System_WorkItemType ,
                        DimWorkItem.System_Id ,
                        FactWorkItemLinkHistory.TargetWorkItemID ,
                        DimWorkItem.System_Title,
                        DimWorkItem.System_State,
                        DimWorkItem.Microsoft_VSTS_Common_ActivatedDate,
                        DimWorkItem.Microsoft_VSTS_Scheduling_TargetDate,
                        DimWorkItem.System_CreatedDate,
                        DimWorkItemLinkType.LinkName,
                        TeamProjectSK,
                        system_rev,
                        row_number() over( partition by system_id,TeamProjectSK, FactWorkItemLinkHistory.TargetWorkItemID order by system_rev desc ) rownum
               FROM     DimWorkItem ,
                        DimTeamProject ,
                        FactWorkItemLinkHistory,
                        DimWorkItemLinkType
               WHERE    DimWorkItem.TeamProjectSK = DimTeamProject.ProjectNodeSK 
                        AND DimWorkItem.System_Id = FactWorkItemLinkHistory.SourceWorkItemID
                        and DimWorkItemLinkType.WorkItemLinkTypeSK = FactWorkItemLinkHistory.WorkItemLinkTypeSK
                        /*    -To Test the Query using the project Name of our choice-    */
                        --AND ProjectNodeName =
                        AND System_State in ('ACTIVE','NEW')
                        /* -System Revisions are created when the entry is modified. Onlt the latest entry will have the below revised date-  */
                        AND System_RevisedDate = '9999-01-01 00:00:00.000'
                        AND DimWorkItemLinkType.Linkname IN ( 'Parent',
                                                     'child' )
               GROUP BY DimTeamProject.ProjectNodeName ,
                        DimWorkItem.System_Id ,
                        FactWorkItemLinkHistory.TargetWorkItemID ,
                        DimWorkItem.System_Title ,
                        System_WorkItemType,
                        DimWorkItem.System_State,
                        TeamProjectSK,
                        DimWorkItemLinkType.LINKName,
                        DimWorkItem.Microsoft_VSTS_Common_ActivatedDate,
                        DimWorkItem.Microsoft_VSTS_Scheduling_TargetDate,
                        DimWorkItem.System_CreatedDate,
                        system_rev
             )
    SELECT distinct t1.ProjectNodeName ,
            t1.System_Id requirement_Id ,
            t1.System_WorkItemType,
            t1.System_Title requirement_title ,
            t2.System_Id Change_request_id ,
            t1.LinkName,
            t2.System_Title Change_Request_Title,
            t1.Microsoft_VSTS_Common_ActivatedDate,
            t1.System_CreatedDate,
            t1.Microsoft_VSTS_Scheduling_TargetDate,
            t1.System_State,
            T1.rownum
    FROM    cte t1
            INNER JOIN cte t2 ON t1.TargetWorkItemID = t2.System_Id
                                  and t1.rownum = 1

    ORDER BY t1.System_Id;

Used a CTE to find get the complete hierarchy, its faster and more efficient than the query posted before.