Agile SCM Talk

Team Foundation Server 2010 includes support for Data Warehouse and Analysis Cube Views.

The company, Amrein Engineering, sells a web part that uses SQL statements to source data.

You can implement this web part very easily to do some very nice reporting of TFS data.

For example, you cannot use "time" as a query criteria in Team Explorer. You can only use the date. That is, you can ask for all work items changed by Joe Black on 9/11/2011. But you cannot ask for changes between 3 and 4 PM on that date. However, you can qualify SQL queries by date and time.

Using the web part from Amrein I was able to:

1. Develop a SQL query for work items changed between two timestamps
2. Implement the query in the web part to display on the TFS team portal
3. Add filters and formating to the web part

I was able to display the work item ID as a hyperlink that brought up the work item in the browser or in the Team Web Access editor.

Here's a couple examples of SQL queries against the TFS_Warehouse DB, dbo.WorkItemHistoryView view. Note: using ASOF queries with this view requires the use of the "System_RevisedDate > [ASOF Date]" and the "RecordCount > 0" conditions because of compensating records. "Whenever a work item is updated, a pair of records is added to the warehouse. The first record negates the previous record. This makes querying faster." See book 'Professional Team Foundation Server 2010'

/****** WORK ITEMS CHANGES IN THE PAST 24 HOURS   ******/
SELECT	 [System_ChangedDate] as Changed
		,[System_ChangedBy] as ChangedBy
		,[System_WorkItemType] as WIT		
		,[Microsoft_VSTS_Common_StackRank] as Seq
		,[System_Id] as ID
		,[System_Title] as Title
		,[System_State] as Ste
		,[System_Reason] as Reason
		,[System_AssignedTo] as Assigned		
		,[Microsoft_VSTS_Scheduling_StoryPoints] as Points
		,[Microsoft_VSTS_Build_FoundIn] as FoundIn
		,[Microsoft_VSTS_Build_IntegrationBuild] as FixedIn
		,[Microsoft_VSTS_Common_Priority] as Pri
		,[Microsoft_VSTS_Common_Severity] as Sev
		,[Microsoft_VSTS_Scheduling_OriginalEstimate] as Est
		,[Microsoft_VSTS_Scheduling_RemainingWork] as Rem
		,[Microsoft_VSTS_Scheduling_CompletedWork] as Act
		,[IterationPath] as iPath
		,[AreaPath] as aPath
  FROM [Tfs_01Warehouse].[dbo].[WorkItemHistoryView]
  WHERE [AreaPath] LIKE '\Cw%'
  AND	([System_ChangedDate] BETWEEN DATEADD([DAY], -1, GETDATE()) AND GETDATE())
  AND	RecordCount > 0
  AND	[System_ChangedBy] <> 'Build Admin'
  ORDER BY 	[System_ChangedDate] DESC

/****** WORK ITEMS CHANGED HISTORY  ******/
SELECT System_Id as ID
		,max(System_WorkItemType) as WIT
		,max(System_Title) as Title
		,max(System_ChangedBy) as ChangedBy
		,max(System_ChangedDate) as Changed		
		,max(AreaPath) as aPath
  FROM [Tfs_01Warehouse].[dbo].[WorkItemHistoryView]
  WHERE [AreaPath] LIKE '\Cw%'
  AND	[System_ChangedBy] = 'Joe Black' 
  AND  ([System_ChangedDate] BETWEEN '9/1/2011 15:00' AND '9/1/2011 16:00')
  AND	[System_RevisedDate] >= '9/1/2011 16:00'
  AND	RecordCount > 0
  GROUP BY [System_Id]
  ORDER BY [System_Id]