In the last post we saw the final result of what we’re aiming for – the Bug’s Life report. In this post, we’ll start working on the report by actually extracting the data.
The TFS Reporting Data Sources
TFS supplies us with two different data sources for reporting needs:
- The data warehouse – A relational data store that is designed using a snowflake schema and is named Tfs_Warehouse. By default, this database is updated within 30 minutes of an anything ‘interesting’ happening in TFS. We query this database using the SQL language.
- The cube – A multidimensional OLAP cube. By default, the cube updates within 2 hours of something happening in TFS, and takes its data from the relational store. We query the cube using the MDX language.
For further details on these sources, see the “Running TFS 2010 Reports” module of the Introduction to TFS 2010 Training Kit. In this post, we’ll use the data warehouse and write our query in SQL. For this purpose, I’ll be using SQL Server Management Studio.
Choosing The Right View
Since we’re dealing with work items, the proper place to start is with the relevant views. These contain columns for all work item fields, across all team projects and team project collections. There are two main views that might be interesting for our type of query:
- The CurrentWorkItemView – This view holds the current state of all work items. Using this view is similar to running work item queries, in that we get back the most recent data.
- The WorkItemHistoryView – This view differs from the previous one in that it also contains the entire history of each work item – including all changes to all fields. In essence, this view contains the same information that you might see in a work item’s History field.
Writing the Actual Query
Obviously, the second view is what we’re interested in. So let’s try this simple query:
SELECT System_Id, System_ChangedDate, System_State, System_Reason, System_ChangedBy, RecordCount, StateChangedCount FROM dbo.WorkItemHistoryView WHERE System_Id IN (10158, 11025, 11026)
Notice that we’re pulling out all of the fields that are of interest to us (and a couple more as well, as I will explain momentarily). The warehouse stores fields according to their reference names, as set in the work item definition.
Here are the results:
So much data for 3 bugs, each with 3 state transitions? Turns out that history tables/views in TFS work with a technique called compensating records. In simple terms, this means that whenever we wish to change the value of some field, we need to insert two records into the database: the first cancels the existing state (‘compensates’ for it) while the second contains the new values. The RecordCount field contains the value –1 for a compensating record, and 1 when actual values are set.
So given a hypothetical work item with fields Field1, Field2 and Field 3, if we changed the value of Field1 from ‘value1’ to ‘value4’, we’ll see something like this in the database:
The records with the bolded values are the ones that enter new information. The additional record in between effectively ‘zeros out’ the state of the work item during the transition. For a more complete explanation see here.
In a similar manner, the StateChangeCount field will contain the value 1 when a change has taken place in the System_State column – just what we need! So we need to add an additional clause to our WHERE statement:
SELECT System_Id, System_ChangedDate, System_State, System_Reason, System_ChangedBy, RecordCount, StateChangedCount FROM dbo.WorkItemHistoryView WHERE System_Id IN (10158, 11025, 11026) AND StateChangeCount = 1
The results now:
Much better! Note also that for each record in our results, the RecordCount field is also equal to 1. This tells us that we are indeed looking at information that was new at the time it was entered.
The Final Query
Here now is the final query that we end up with:
-- String splitting code adapted from http://stackoverflow.com/questions/2647/split-string-in-sql DECLARE @tblBugIds table ( BugId varchar(10) ) DECLARE @bugId varchar(10) DECLARE @intPosition int DECLARE @allBugIds nvarchar(255); SET @allBugIds = LTRIM(RTRIM(@bugIdsParam)) + ',' SET @intPosition = CHARINDEX(',', @allBugIds, 1) IF REPLACE(@allBugIds, ',', '') <> '' BEGIN WHILE @intPosition > 0 BEGIN SET @bugId = LTRIM(RTRIM(LEFT(@allBugIds, @intPosition - 1))) IF @bugId <> '' BEGIN INSERT INTO @tblBugIds (BugId) VALUES (@bugId) END SET @allBugIds = RIGHT(@allBugIds, LEN(@allBugIds) - @intPosition) SET @intPosition = CHARINDEX(',', @allBugIds, 1) END END SELECT System_Id, System_ChangedDate, System_State, System_Reason,
System_ChangedBy FROM dbo.WorkItemHistoryView WHERE System_Id IN (SELECT * FROM @tblBugIds) AND StateChangeCount = 1 AND System_WorkItemType = 'Bug'
This query contains a few additional things:
- Code for breaking up a user parameter containing a string of comma-separated bug ID values into a temporary table. This is used to handle a variable number of bug IDs in the query.
- A further stipulation in the WHERE clause that a work item must be of type ‘Bug’ (although the query would work just as well with all other work item types)
Now that we know how to get the data, it’s time to put it into an actual report. In the next post we’ll open up Report Builder, build a layout for our report and add our query into it. Till next time!