1

If I pull up a task for a particular project there is a field called original estimate. The only place I can find the original estimate is in the Tfs_Warehouse database in the FactWorkItemHistory table under the Microsoft_VSTS_Scheduling_OriginalEstimate column.

Can someone clarify how this value works? The reason I ask is it changes in the FactWorkItemHistory table for the same task. There are some positive entries and negative entries. If I sum up all of the Microsoft_VSTS_Scheduling_OriginalEstimate values by Task and ProjectNodeSK I come up with the amount that shows up in the TFS task UI. I'm just wondering why the value changes over entries. In fact, I also would be curious as to why there are multiple WorkItem entries for the same task. I figured (incorrectly) that the WorkItem table would be a one to one as far as the Task in the TFS UI goes.

1

The warehouse stores each work item revision in order to allow you to query the value of the original estimate at a specific point in time. This is why the same work item is listed multiple times in a number of tables, including the one you're using.

The warehouse also stores the delta between revisions so that you can do fun statistical things with them. each workitem should be represented once in the DimWorkItem table. There's also a CurrentWorkItem table for the latest snapshot. Each *Fact table stores point-in-time values. You'll also find a couple of useful views, including *WorkItemsAre and *WorkItemsWere, the first contains the latest snapshot of data and is likely what you're after. The second is a more stable view of the point-in-time fact tables.

More details on the warehouse schema can be found here:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.