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
  • 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
  • Did that help?

     

     

     

     

     


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

    Friday, April 13, 2012 3:20 PM
  • 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
  • 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
  • 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