What are the table relationships by member, task and effort time in Team Foundation Server?

Question

I need to extract some data from TFS data base and I can't find the tables that I need. We using TFS for measure productivity (projects, user stories, tasks, etc) and I must do an productivty indicators with Excel connecting directly to TFS data base indicating the effort time by user and task. I have the memberships table but no the relations to workitems/task and the effort time

SELECT * FROM [ADObjects] where ObjectCategory = 2 order by DisplayName

Version of TFS 15.117.27024.0

EDIT

Ok, I finded the way to access the data that I wanted with a sql query:

 SELECT o.SamAccountName as Usuario
        ,MAX(Microsoft_VSTS_Common_ClosedDate) AS Fecha
        ,[WorkItem]
        ,SUM([Microsoft_VSTS_Scheduling_CompletedWork]) as Horas
        ,MAX([System_Rev]) AS UltimoMovimiento
        ,System_Id as WorkItemId
  FROM [tfs_warehouse].[dbo].[WorkItemHistoryView] wi
  join [Tfs_OurDomain].dbo.ADObjects o on o.DisplayName = wi.System_AssignedTo and o.DomainName = 'DomainName'
  where System_State = 'Closed' and System_WorkItemType in ('Bug','Task') and System_AssignedTo is not null
  and o.SamAccountName = 'MemberName' 
  and Microsoft_VSTS_Common_ClosedDate between '20180501' and '20181101'
  group by [WorkItem],o.SamAccountName,System_Id
  order by o.SamAccountName asc

Answer 1

Querying directly against the TFS_*Collection databases and the TFS_Configuration databases is not supported. The schema can change between even minor updates and breaking changes have occurred in past releases at regular intervals. Running queries against this database may negatively impact the performance of your TFS installation.

To support reporting TFS has shipped with a Reporting capability. The TFS Warehouse (if enabled on your installation) contains most of the data you're looking for in an easy queriable format. These warehouse tables contain a more limited set of data and the schema is kept the same within major releases and hasn't changed much even between different major releases. The process hasn't changed since it's introduction but the functionality is on it's way out when the Azure DevOps Analytics Service is available for on-premise TFS installations. This will expose the data through an ODATA feed.

The analytics service is currently forecasted for TFS 2019:

Analytics - Available for Azure DevOps Server Reporting 2019

If the Warehouse Cube isn't available for your TFS installation, you can use the TFS REST API's to query all the data you're after. The following API's will give you access to the data you need:

• Query Work Items Revisions for Reporting.
• Query Work Item Links for Reporting
• Map work to teams
• Query teams & team members

Excel can be extended through .NET and using that functionality to query the REST API you can then use the .NET Extensibility in Excel to populate lists in excel with the data from these REST APIs and drive your reports from those lists.