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
SELECT Outcome, BuildName, COUNT(*) AS TestCount
dbo.FactTestResult ftr INNER JOIN
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
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/
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.