TFS2010: How to query Work Items using SQL on the Relational Warehouse

Grant Holliday’s blog

Senior Service Engineer, Microsoft Visual Studio Team Foundation Service

TFS2010: How to query Work Items using SQL on the Relational Warehouse

Rate This
  • Comments 0

In John Socha-Leialoha's blog post on Upgrading Team Foundation Server 2008 Reports to 2010, Part I, there is a hidden gem:

For the first time, writing reports against the warehouse using SQL is officially supported. As a rule of thumb, you’ll generally want to use the cube for historical reports, or reports that require a lot of slicing and dicing using parameters of aggregate data. The cube is really good at this sort of work. The warehouse, on the other hand, allows you to create reports that pull loosely related data together in ways not possible with the cube.

The views that begin with “v” and end with “Overlay” are used for processing the cube, and as such aren’t really meant for use in your reports.

The relational warehouse is a reasonable store to work against, since the warehouse adapters that sync data from the operational store run every 5-minutes and keep the data fresh. You should NEVER write reports directly against the WorkItem* tables in the collection database, since this is an operational store and is 100% unsupported and can cause performance problems for normal usage. The limitation of TFS2008 still exists that fields of the Html type are not pushed into the warehouse and for those you’ll have to use the Work Item Tracking object model to query and retrieve them.

Permissions

Before you can use these queries, you’ll need to be a member of the TfsWarehouseDataReader role in the Tfs_Warehouse database. Remember that the warehouse contains data from all projects on a server, so anybody who has access to query the warehouse can see this regardless of their permissions within TFS. The best way to give people access is to create an Active Directory group that contains all the users that should have access to query the relational warehouse, then add that group to the role with the following script:

USE [Tfs_Warehouse]
GO

CREATE USER [DOMAIN\TfsWarehouseDataReadersGroup] FOR LOGIN [DOMAIN\TfsWarehouseDataReadersGroup] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember N'TfsWarehouseDataReader', N'DOMAIN\TfsWarehouseDataReadersGroup'
GO

You can also use this group to give people access to query the OLAP cube in Analysis Services. See Grant Access to the Databases of the Data Warehouse for Visual Studio ALM for more information.

There are 9 views that you can query and write reports against with some level of assurance that they will work the next time that the server is upgraded:

  • CurrentWorkItemView
  • WorkItemHistoryView
  • BuildChangesetView
  • BuildCoverageView
  • BuildDetailsView
  • BuildProjectView
  • CodeChurnView
  • RunCoverageView
  • TestResultView

Now that TFS2010 has multiple project collections sharing the same relational warehouse and OLAP cube, there are a two things to consider when writing queries against the views:

  • Filter on Project GUID - since a project's name is not necessarily unique across multiple collections on the same server.
  • Make sure your joins use unique keys. For example, work item IDs are no longer unique within the warehouse

Example

Here’s an example Work Item Query (WIQL):

SELECT [System.Id], [Microsoft.VSTS.Common.StackRank], [Microsoft.VSTS.Common.Priority], [Microsoft.VSTS.Common.Severity], [System.State], [System.Title]

FROM WorkItems

WHERE [System.TeamProject] = 'DemoAgile'

AND  [System.AssignedTo] = 'Grant Holliday'

AND  [System.WorkItemType] = 'Bug'

AND  [System.State] <> 'Closed'

ORDER BY [System.State], [Microsoft.VSTS.Common.StackRank], [Microsoft.VSTS.Common.Priority], [Microsoft.VSTS.Common.Severity], [System.Id]

And here’s an equivalent query of the same data from the relational warehouse:

SELECT [System_Id], [Microsoft_VSTS_Common_StackRank], [Microsoft_VSTS_Common_Priority], [Microsoft_VSTS_Common_Severity], [System_State], [System_Title]

FROM CurrentWorkItemView

WHERE [ProjectNodeGUID] = 'a6fc4213-94c0-4361-87bf-9520e07eb096'

AND [System_AssignedTo] = 'Grant Holliday'

AND  [System_WorkItemType] = 'Bug'

AND  [System_State] <> 'Closed'

ORDER BY [System_State], [Microsoft_VSTS_Common_StackRank], [Microsoft_VSTS_Common_Priority], [Microsoft_VSTS_Common_Severity], [System_Id]

Some queries will be much faster using the WIT Object Model, but at least this gets you started with the relational warehouse.