How to get the daily data of "Microsoft.VSTS.Scheduling.CompletedWork"?

Question

We need to get the daily data from the "Microsoft.VSTS.Scheduling.CompletedWork"field (which is detailed in Workload, scheduling and time tracking field references). However I get data from the Analysis database and found that it only records one last new data,and can't get the historical data.

For example the task of ID 3356, who's "CompletedWork" is 3 hours in 2016/8/4, and I get the exact 3 hours-data from the Analysis database in the second day, 2016/8/5, as the pictures in this post show.

Then on the 2016/8/5, I update the "CompletedWork" from 3 hours to 4 hours and I get the exact 4 hours-data from the Analysis database in the second day, 2016/8/6. However the 3 hours-data of 2016/8/4 is lost. Well, How can I get the historical data of "Microsoft.VSTS.Scheduling.CompletedWork"?

Answer 1

First of all, it's important to understand that the CompletedWork is a cumulatieve data field. So when one user enters 3 and another enters 4, the total number of hours worked on the field is 4 not 7.

The warehouse has a granularity of a day and keeps that data int he cube, though the relational warehouse tables will store all the changes to the reportable fields on a per-revision bases. You can't easily query this data using the qube or Excel Power Pivot and they're lost in the Dim* and fact* tables, but you can write a SQL query against tfs_warehouse and iterate through the tables containing the workitem data (tbl_workitems[are|were|latest]). This is much slower and much harder to build unfortunately.

Your other alternative is to use the TFS Client Object Model and query the WorkItemStore object directly. You'll be able to query all work items of interest and iterate through them and their revisions. The API for workitems is relatively easy to use and is well documented.

If you're on TFS 2015 you can also use the new REST api to query workitem data and revisions.