none
Where is the Fact Table for Related Current Work Items in TFS 2010

    Question

  • I need to update or create a sql report where I would like to show what are the items that have "Related" link to my top level item.

    Reading GW schema for TFS 2005 and 2005, it seems that there was a table in TFwarehouse called "Related Current Work Items ", This fact table stores links between related work items.

    Did this disappear in TFS 2010? would the answer be yes or no, can you please point me a tutorial or document, or a CTE  describing how doing a hierarchial report for an item with its related links or any other type of link in TFS 2010.

    I found a blog talking about how to do it in TFS 2005 and 2008 but not in 2010

    Thanks in advance

    Regards,

    Sunday, April 08, 2012 7:06 PM

Answers

  • Hi Eliassal,

    First, yes Current Related Work Items is no longer present in TFS 2010 -- all link data is stored in FactWorkItemLinkHistory, and can be filtered for latest vs. historical / as-of values based on date ranges.

    Please take a look at the GetWorkItemsForLinkPath() and GetWorkItemsForLinkPathXml() table-valued functions.  As its name suggests, GetWorkItemsTree[Xml]() is only for tree-based / parent-child link types, while GetWorkItemsForLinkPath() allows you to get links related to a set of "root" items via a pretty flexible "path" of specific link types.

    My apologies that this is currently hard to find; I'll see if we can improve on that.  In the meantime, I've included a basic description of the function below.  You can also query against FactWorkItemLinkHistory directly, and may need to for arbitrarily complex types of link queries -- but the function serves as an example that may cover a good set of basic needs.  Please let me know how it works for yours.

    Regards,
    Dave

    GetWorkItemsForLinkPath

    Starting with the root work items provided in @workItemIdList, traverses the link types in @linkPath, in order, to produce a graph of all work items reachable from the root work items by walks that follow the link pattern. For example, starting from several Requirements work items and following "Child, Tested By" would return all work items that are descendants (following the "Child" link type through multiple hops) of those work items and then all Tests related to those work items through "Tested by" links.

    GetWorkItemsForLinkPath returns pairs of work items. Each pair includes one of the root work items from @workItemIdList and one work item that is reachable from the root work item id, following the link type traversal pattern in @linkPath. The link type of the last link followed to reach the linked work item is also included for convenience.

    Parameters:

        @projectCollectionGUID    The GUID of the Team Project Collection that contains the work items and links

        @workItemIdList           Comma delimited list of work item ids.

    Example: 1, 2, 3, 5

        @linkPath                 Comma delimited list of link names, to be traversed in order.

    Example: Child, Tested By

        @asOfDate                 Crawl the child link graph as of this date and time. Defaults to now.

        @maxHops                  The maximum number of times to traverse each type of link in @linkPath.

    The default is 6

    Returns:

        [RootWorkItemSK]          INT               One of the original work items from @workItemIdList

        [RootWorkItemID]          INT

        [RootWorkItemType]        NVARCHAR (256)

        [RootWorkItemProjectSK]   INT               Team Project that contains the root work item

        [LinkTypeSK]              INT               The link type that was followed to inlude the linked work item

        [LinkTypeName]            NVARCHAR (256)

        [LinkedWorkItemSK]        INT               A work item reached by following the @linkPath pattern

        [LinkedWorkItemID]        INT

        [LinkedWorkItemType]      NVARCHAR (256)

        [LinkedWorkItemProjectSK]       INT               Team Project that contains the linked work item

    Friday, April 27, 2012 10:05 PM
  • Also, note that the related post Trevor linked is one possible example of creating your own queries against FactWorkItemHistory if GetWorkItemsForLinkPath() doesn't meet your needs.  As mentioned above, since you want Related links rather than tree links, you *don't* want to use GetWorkItemsTree() itself.  I think the author of the related post realized this as well, so his custom SQL is explicitly filtering on other link types as he mentions.

    Dave

    Friday, April 27, 2012 10:19 PM

All replies

  • Hi Eliassal, 

    Thanks for your post.

    As far as I know, there’s no the “RelatedCurrentWorkItems” table in TFS 2010 Tfs_Warehouse database.

    To create the work items hierarchical report in TFS 2010, we usually create the work item Query to tracking work items in TFS 2010, please refer to: http://blogs.msdn.com/b/bharry/archive/2009/05/18/tfs-2010-work-item-tracking.aspx.

    Or according the work item Query to create Excel Report, please refer to this: http://blogs.msdn.com/b/sunder/archive/2010/03/02/reporting-in-team-foundation-server-part-7-excel-reports-from-work-item-queries.aspx and http://blogs.msdn.com/b/ukvsts/archive/2011/09/29/using-excel-to-easily-create-reports-from-tfs-work-items.aspx

    Edit Report in Microsoft Excel, please refer to this document: http://msdn.microsoft.com/en-us/library/ms244692.aspx.   

     


    John Qiao [MSFT]
    MSDN Community Support | Feedback to us



    Tuesday, April 10, 2012 7:01 AM
    Moderator
  • Thanks for this pointers. However, they don't respond to my needs as I absolutly need to access data in the warehouse.

    Why ? Because I have introduced a new WIT based on Req WIT "MyReqItem" with some reduced types. This has related link type to user stories and other types of links to other WOR items.

    The requirement overviw progress and overview are based on the parent/child relationship which allows rolling up "remaining an completed hours" up to the requirement. I need to do a custom report based on my new introduced WIT as the top level, then dispaly is a master/child report all user stories and tasks linked to it.

    So the question again :can you please point me a tutorial or document, or a sql loop or CTE  describing how doing a hierarchial report for an item with its related links or any other type of link in TFS 2010.

    Thanks in advance

    Tuesday, April 10, 2012 8:21 PM
  • Perhaps this will help: http://msdn.microsoft.com/en-us/library/ms244691.aspx#WorkItems

    Trevor Hancock (Microsoft)
    Please remember to "Mark As Answer" the replies that help.

    Wednesday, April 11, 2012 3:32 PM
    Owner
  • Did that help?

     

     

     

     

     


    Trevor Hancock (Microsoft)
    Please remember to "Mark As Answer" the replies that help.

    Friday, April 13, 2012 3:20 PM
    Owner
  • I didn't have time to go through this. I had a quick look , I think it provides some good scripts but only Parent/child hierarchy

    I will give it a go and see if I can do something for related and affected by item.

    Friday, April 13, 2012 4:00 PM
  • Yes it helped,  however, one thing I was not able to understand and use the "GetWorkItemsTree" functions

    GetWorkItemsTree(@TeamProjectCollectionGuid, wi.System_Id, N'Child', DEFAULT)

    at least was not able to make it work outside this query by providing different arguments (N'Related'.....

    Do you have stand alone script using this function with diffeenet mix of arguments especially not Child or parent link type

    Wednesday, April 18, 2012 1:19 PM
  • Please review this thread to see if it helps:

    http://social.msdn.microsoft.com/Forums/en/tfsreporting/thread/46b973fd-5dc4-4798-9461-5e1d9c3a96de


    Trevor Hancock (Microsoft)
    Please remember to "Mark As Answer" the replies that help.

    Wednesday, April 25, 2012 4:26 PM
    Owner
  • Thanks, I have gone througfh this thread. As indicated even in the thread, the blogger as myself needs information on what is the use of GetWorkItemsTree. Is there any docs describing this function and how it can be used
    Wednesday, April 25, 2012 8:54 PM
  • There is SQL on this thread that may help.

    Trevor Hancock (Microsoft)
    Please remember to "Mark As Answer" the replies that help.

    Friday, April 27, 2012 4:43 PM
    Owner
  • Trevor, nothing in this thread talks about GetWorkItemsTree. It is only mentioned by the thread creator that he was able to find a solution but he was not able to make this function works. I need information on GetWorkItemsTree

    Thanks

    Friday, April 27, 2012 5:05 PM
  • Hi Eliassal,

    First, yes Current Related Work Items is no longer present in TFS 2010 -- all link data is stored in FactWorkItemLinkHistory, and can be filtered for latest vs. historical / as-of values based on date ranges.

    Please take a look at the GetWorkItemsForLinkPath() and GetWorkItemsForLinkPathXml() table-valued functions.  As its name suggests, GetWorkItemsTree[Xml]() is only for tree-based / parent-child link types, while GetWorkItemsForLinkPath() allows you to get links related to a set of "root" items via a pretty flexible "path" of specific link types.

    My apologies that this is currently hard to find; I'll see if we can improve on that.  In the meantime, I've included a basic description of the function below.  You can also query against FactWorkItemLinkHistory directly, and may need to for arbitrarily complex types of link queries -- but the function serves as an example that may cover a good set of basic needs.  Please let me know how it works for yours.

    Regards,
    Dave

    GetWorkItemsForLinkPath

    Starting with the root work items provided in @workItemIdList, traverses the link types in @linkPath, in order, to produce a graph of all work items reachable from the root work items by walks that follow the link pattern. For example, starting from several Requirements work items and following "Child, Tested By" would return all work items that are descendants (following the "Child" link type through multiple hops) of those work items and then all Tests related to those work items through "Tested by" links.

    GetWorkItemsForLinkPath returns pairs of work items. Each pair includes one of the root work items from @workItemIdList and one work item that is reachable from the root work item id, following the link type traversal pattern in @linkPath. The link type of the last link followed to reach the linked work item is also included for convenience.

    Parameters:

        @projectCollectionGUID    The GUID of the Team Project Collection that contains the work items and links

        @workItemIdList           Comma delimited list of work item ids.

    Example: 1, 2, 3, 5

        @linkPath                 Comma delimited list of link names, to be traversed in order.

    Example: Child, Tested By

        @asOfDate                 Crawl the child link graph as of this date and time. Defaults to now.

        @maxHops                  The maximum number of times to traverse each type of link in @linkPath.

    The default is 6

    Returns:

        [RootWorkItemSK]          INT               One of the original work items from @workItemIdList

        [RootWorkItemID]          INT

        [RootWorkItemType]        NVARCHAR (256)

        [RootWorkItemProjectSK]   INT               Team Project that contains the root work item

        [LinkTypeSK]              INT               The link type that was followed to inlude the linked work item

        [LinkTypeName]            NVARCHAR (256)

        [LinkedWorkItemSK]        INT               A work item reached by following the @linkPath pattern

        [LinkedWorkItemID]        INT

        [LinkedWorkItemType]      NVARCHAR (256)

        [LinkedWorkItemProjectSK]       INT               Team Project that contains the linked work item

    Friday, April 27, 2012 10:05 PM
  • Also, note that the related post Trevor linked is one possible example of creating your own queries against FactWorkItemHistory if GetWorkItemsForLinkPath() doesn't meet your needs.  As mentioned above, since you want Related links rather than tree links, you *don't* want to use GetWorkItemsTree() itself.  I think the author of the related post realized this as well, so his custom SQL is explicitly filtering on other link types as he mentions.

    Dave

    Friday, April 27, 2012 10:19 PM
  • I had to do something similar in Visual Studio 2013.  It is actually pretty easy using GetItemsForLinkPath. Here is my code--I hope somebody finds it useful. It basically crawls Child & Related links of all Project work item types and creates a table to capture:

    - project info

    - "parent" info 

    - work item type info

    This was used to create a project dashboard. 

    You're welcome. :) 

    declare @TeamProjectNodeSK int -- Needed to get the @TeamProjectCollectionGuid below
    select @TeamProjectNodeSK = ProjectNodeSK from GetProjectNodeInfoFromReportFolder(N'/TfsReports/')  -- This table-value function returns the ProjectNodeSK: the Surrogate Key of a team project under a certain area path. Set the path to the project node for your TFS project.

    declare @TeamProjectCollectionGuid nvarchar(36) -- Needed for CROSS APPLY with GetWorkItemsForLinkPath() below
    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
      CWIV_Project.System_Id as [Project ID],
      CWIV_Project.System_Title as [Project Title],
      CWIV_Project.System_State as [Project State],
      (
      SELECT TOP 1 -- TOP 1 required because we are following Child link types (which can have only one Parent so Top 1 isn't required) and Related link types which do not
                   -- necessarily have a parent so there could be multiple SourceIDs. This means that the result set cannot be used to reconstruct the entire hierarchy as it relates to 
      -- Related bugs. At this point I do not see that this is matters and it keeps this query clean.
      cwiv_source.System_Id

    FROM dbo.vFactLinkedCurrentWorkItem
    INNER JOIN dbo.DimWorkItemLinkType
      ON vFactLinkedCurrentWorkItem.WorkItemLinkTypeSK = DimWorkItemLinkType.WorkItemLinkTypeSK
    INNER JOIN dbo.CurrentWorkItemView cwiv_source
      ON cwiv_source.WorkItemSK = vFactLinkedCurrentWorkItem.SourceWorkItemSK
    INNER JOIN dbo.CurrentWorkItemView cwiv_target
      ON cwiv_target.WorkItemSK = vFactLinkedCurrentWorkItem.TargetWorkitemSK
    WHERE cwiv_target.System_Id = LinkedItem.LinkedWorkItemID
           AND  DimWorkItemLinkType.LinkName = LinkedItem.LinkTypeName

      ) [Source Work Item ID],
      (
      SELECT TOP 1
      cwiv_source.System_Title

    FROM dbo.vFactLinkedCurrentWorkItem
    INNER JOIN dbo.DimWorkItemLinkType
      ON vFactLinkedCurrentWorkItem.WorkItemLinkTypeSK = DimWorkItemLinkType.WorkItemLinkTypeSK
    INNER JOIN dbo.CurrentWorkItemView cwiv_source
      ON cwiv_source.WorkItemSK = vFactLinkedCurrentWorkItem.SourceWorkItemSK
    INNER JOIN dbo.CurrentWorkItemView cwiv_target
      ON cwiv_target.WorkItemSK = vFactLinkedCurrentWorkItem.TargetWorkitemSK
    WHERE cwiv_target.System_Id = LinkedItem.LinkedWorkItemID
           AND  DimWorkItemLinkType.LinkName = LinkedItem.LinkTypeName

      ) [Source Work Item Title],
      (
      SELECT TOP 1
      cwiv_source.System_WorkItemType

    FROM dbo.vFactLinkedCurrentWorkItem
    INNER JOIN dbo.DimWorkItemLinkType
      ON vFactLinkedCurrentWorkItem.WorkItemLinkTypeSK = DimWorkItemLinkType.WorkItemLinkTypeSK
    INNER JOIN dbo.CurrentWorkItemView cwiv_source
      ON cwiv_source.WorkItemSK = vFactLinkedCurrentWorkItem.SourceWorkItemSK
    INNER JOIN dbo.CurrentWorkItemView cwiv_target
      ON cwiv_target.WorkItemSK = vFactLinkedCurrentWorkItem.TargetWorkitemSK
    WHERE cwiv_target.System_Id = LinkedItem.LinkedWorkItemID
            AND  DimWorkItemLinkType.LinkName = LinkedItem.LinkTypeName

      ) as [Source Work Item Type],
    LinkedItem.LinkTypeName AS [Source Work Item Link Type],
      LinkedItem.LinkedWorkItemID AS [Work Item ID],
      (SELECT System_Title from CurrentWorkItemView CWIV WHERE CWIV.System_ID = LinkedItem.LinkedWorkItemID) AS [Work Item Title],
      (SELECT System_State from CurrentWorkItemView CWIV WHERE CWIV.System_ID = LinkedItem.LinkedWorkItemID) AS [Work Item State],
      (SELECT System_AssignedTo from CurrentWorkItemView CWIV WHERE CWIV.System_ID = LinkedItem.LinkedWorkItemID) AS [Work Item Assigned To],
      
      LinkedItem.LinkedWorkItemType as [Work Item Type],
      (SELECT AreaPath from CurrentWorkItemView CWIV WHERE CWIV.System_ID = LinkedItem.LinkedWorkItemID) AS [Area Path],
      (SELECT System_State from CurrentWorkItemView CWIV WHERE CWIV.System_ID = LinkedItem.LinkedWorkItemID) AS [Iteration Path],
      (select CASE WHEN EXISTS (
    SELECT TOP 1 *
    FROM
    dbo.vFactLinkedCurrentWorkItem
    WHERE 
      (vFactLinkedCurrentWorkItem.TargetWorkItemSK=LinkedItem.LinkedWorkItemSK)
      and (vFactLinkedCurrentWorkItem.SourceWorkItemSK=LinkedItem.RootWorkItemSK)
     ) THEN 'True'
     ELSE 'False' END) AS [Is Linked To Project]
      
    FROM CurrentWorkItemView AS CWIV_Project
    outer APPLY GetWorkItemsForLinkPath(@TeamProjectCollectionGuid, CWIV_Project.system_id, 'Child, Related', DEFAULT , DEFAULT) LinkedItem
    left outer Join dbo.CurrentWorkItemView CWIV_linked ON 
      CWIV_linked.System_Id = LinkedItem.LinkedWorkItemID

    WHERE (CWIV_Project.System_WorkItemType = 'Project')


    Tuesday, November 04, 2014 1:37 AM