TFS Warehouse Work Item Tracking Compensating Records
Compensating Records
What are compensating records?
First a little background. Whenever a work item is updated a new revision is created. These revisions are moved over to the Warehouse and stored in the various Work Item Dimension and Fact tables. Each revision will be in the Warehouse – however each date will not have a revision. Something like (ignoring actual schema for now):
ID |
Revision |
Date |
Remaining Work |
1 |
1 |
4/10/2009 |
50 |
1 |
2 |
4/15/2009 |
40 |
1 |
3 |
4/20/2009 |
30 |
1 |
4 |
4/25/2009 |
20 |
Now if you wanted to ask a question about work items on a specific date then one way you can answer your question is to get the latest revision of the work item created before that date.
For example say you want to know the Remaining Work for a Work item on 4/22/2009. You could get all the work items whose Date is <= 4/22/2009 (Revisions 1, 2, and 3); then from these find the one with the largest Revision (Revision 3). This gives you a Remaining Work of 30.
However this requires you to do a MAX in your query and doesn’t work particularly well in the Cube. So we have added compensating records. These records are primarily used for running sum computations. Each one effectively negates, or compensates, the previous revision of that work item.
For example, using the same data from above:
1. 4/10/2009 – Work Item 1 is added.
ID |
Revision |
Date |
Remaining Work |
Record Count |
Is Compensating |
1 |
1 |
4/10/2009 |
50 |
1 |
No |
2. 4/15/2009 – Work Item 1 is revised.
ID |
Revision |
Date |
Remaining Work |
Record Count |
Is Compensating |
1 |
1 |
4/10/2009 |
50 |
1 |
No |
1 |
1 |
4/15/2009 |
-50 |
-1 |
Yes |
1 |
2 |
4/15/2009 |
40 |
1 |
No |
3. 4/20/2009 and 4/25/2009 – Work Item 1 is revised.
ID |
Revision |
Date |
Remaining Work |
Record Count |
Is Compensating |
1 |
1 |
4/10/2009 |
50 |
1 |
No |
1 |
1 |
4/15/2009 |
-50 |
-1 |
Yes |
1 |
2 |
4/15/2009 |
40 |
1 |
No |
1 |
2 |
4/20/2009 |
-40 |
-1 |
Yes |
1 |
3 |
4/20/2009 |
30 |
1 |
No |
1 |
3 |
4/25/2009 |
-30 |
-1 |
Yes |
1 |
4 |
4/25/2009 |
20 |
1 |
No |
Now you can answer the same question as above by getting all the revisions of work items older than 4/22/2009 (Revisions 1, 1’, 2, 2’, and 3) and summing their Remaining Work. This gives you a Remaining Work of (50 + -50 + 40 + -40 + 30) = 30. This should be faster than without the compensating records (and it makes the Cube happy!).
What about that Record Count column?
You can apply the same running sum logic to the Record Count to get the number of work items as of a date. So you could ask how many work items were there on 4/22/2009. Again we sum up the values for all rows with a Date <= 4/22/2009 and we get (1 + -1 + 1 + -1 + 1) = 1. Not terribly exciting with our data, but you can see how if we had more work items this could be useful.
Again – with TFS
Alright so let’s look at some example using the TFS 2008 schema. Say I want to get the number of Active and Resolved Work Items assigned to me as of a given date. First thing I need to know is what tables I should look at:
Info needed in query |
Warehouse Table |
Active and Resolved Work Items |
[Work Item] filtered on [System_State] |
Assigned to me |
[Work Item History] or [Current Work Item] joined to [Person] on [Work Item History].[Person] = [Person].[__ID] or [Current Work Item].[Person] = [Person].[__ID] |
Work Items as of a date |
[Work Item History] filtered on [Date] |
Putting this together I see I need [Work Item History], [Work Item], and [Person]. So let’s build the query:
SELECT wi.[System_State]
,SUM([Record Count]) AS [Count]
FROM [Work Item History] AS wih
LEFT JOIN [Work Item] AS wi
ON wi.[__ID] = wih.[Work Item]
LEFT JOIN [Person] AS p
ON p.[__ID] = wih.[Assigned To]
WHERE [Date] < CONVERT(DATETIME, '2009-03-19', 126) AND
p.[Person] = N'Nick Ericson' AND
wi.[System_State] IN ('Active', 'Resolved')
GROUP BY wi.[System_State]
System_State |
Count |
Resolved |
2 |
Active |
6 |
I can change the query slightly to get a better query plan (please send any further optimizations along – I’ll add them):
SELECT wi.[System_State]
,SUM([Record Count]) AS [Count]
FROM [Work Item History] AS wih
LEFT JOIN [Work Item] AS wi
ON wi.[__ID] = wih.[Work Item]
WHERE wih.[Assigned To] = (SELECT TOP 1 [__ID] FROM [Person] WHERE [Person] = N'Nick Ericson') AND
wih.[Date] < CONVERT(DATETIME, '2009-03-19', 126) AND
wi.[System_State] IN ('Active', 'Resolved')
GROUP BY wi.[System_State]
One thing I should note that can be confusing is that I am passing in 2009-03-19 00:00:00.000 for the Date. Most people would consider this not to be 19th, but instead the 18th (or so barely the 19th that we shouldn’t use it for determining how many bugs were Active on the 19th). However in the Warehouse we truncate the times off of the Date field. So if a work item was marked active at 6 PM on the 19th the Date field in the Warehouse will have 2009-03-19 00:00:00.000. This is a long way of saying that 2009-03-19 is the 19th, not the 18th.
Alright getting the counts for someone on a given day was fairly straight forward. You may want to add in some logic to return Active and Resolved counts of 0 when the query returns NULLs – but this depends on how you will build your report.
Trending
How about a trend report? You basically need to execute the queries from the section above over a range of dates. We can use a Cross Apply here (again send along your optimized queries):
SELECT d.[Date] AS [Date], ca.*
FROM [Date] AS d
CROSS APPLY
(
SELECT wi.[System_State]
,SUM([Record Count]) AS [Count]
FROM [Work Item History] AS wih
LEFT JOIN [Work Item] AS wi
ON wi.[__ID] = wih.[Work Item]
WHERE wih.[Assigned To] = (SELECT TOP 1 [__ID] FROM [Person] WHERE [Person] = N'Nick Ericson') AND
wih.[Date] < d.[__ID] AND
wi.[System_State] IN ('Active', 'Resolved')
GROUP BY wi.[System_State]
) AS ca
WHERE d.[__ID] BETWEEN CONVERT(DATETIME, '2009-03-19', 126) AND CONVERT(DATETIME, '2009-03-21', 126)
Date |
System_State |
Count |
3/19/2009 |
Active |
6 |
3/19/2009 |
Resolved |
2 |
3/20/2009 |
Active |
7 |
3/20/2009 |
Resolved |
2 |
3/21/2009 |
Active |
7 |
3/21/2009 |
Resolved |
2 |
Comments
- Anonymous
March 20, 2009
PingBack from http://blog.a-foton.ru/index.php/2009/03/21/tfs-warehouse-work-item-tracking-compensating-records/