Retrieving Unit Test Results for Builds in TFS 2010

Have you ever wanted to use the TFS 2010 data warehouse for retrieving the unit test pass/fail count for a specific build?  Maybe you wanted a report showing the number of unit tests that ran during each nightly build last week.  In any case, if you wanted this or something similar, you soon found out an interesting fact – picking out only unit tests from the warehouse is not trivial. 

Here is the query for doing this:

SELECT BuildName AS 'Build',
  COALESCE([Passed],0) AS Passed,
  COALESCE([Failed],0) AS Failed
FROM
(
  SELECT Outcome, BuildName, COUNT(*) AS TestCount
  FROM
   dbo.FactTestResult ftr INNER JOIN
   dbo.DimTestResult dtr
     ON ftr.ResultSK = dtr.ResultSK
   INNER JOIN dbo.DimBuild db
     ON ftr.BuildSK = db.BuildSK
  WHERE dtr.TestTypeId = '13cdc9d9-ddb5-4fa4-a97d-d965ccfc6d4b'
  GROUP BY Outcome, BuildName
) AS SourceTable
PIVOT
(
  SUM(TestCount) FOR Outcome IN ([Passed], [Failed])
) AS PivotTable
ORDER BY Build ASC

Some interesting points about this query:

• It’s SQL, so obviously you can only run it on the relational data  warehouse (TFS_Warehouse).  What’s not so obvious is the TestTypeId field – seems it only exists in the relational store, and is not carried over into the OLAP cube.  Sorry, MDX fans…
• Where does the TestTypeId  Guid value come from? This is the key for the entire query.  This value is what allows us to pull out only unit tests (and only MSTest UT’s at that).  The source of this value is in the HKLM/Software/Microsoft/VisualStudio/
  10.0/EnterpriseTools/QualityTools/
  TestTypes registry key, and its sub keys.  Using these, you can use the query to determine values for specific types of tests (web,manual, generic,etc.).
• This query will return results for all builds in your system. Add additional conditions to the WHERE clause in the inner query (the one with the joins) if you want to limit it further. 

Happy reporting!