Previous State and State Change Count

Today I am going to look at two interesting fields in the Warehouse related to Work Items – Previous State, and State Change Count.

The Previous State field stores the state a Work Item was in before it entered its current state. This field is in the [Work Item] table in the relational/dimensional Warehouse and in the [Work Item] Dimension in the Cube.

The State Change Count measure counts the number of changes in State between a Work Item revision and its previous Revision (actually instead of 0 it will have a value of NULL – so it will either have a value of 1 or NULL). It is in the [Work Item History] table in the relational/dimensional Warehouse and in the [Work Item History] measure group in the Cube.

The easiest way to understand their values is with an example. In this example we are going to create a new Work Item and then edit it several times. The table shows the values in the Warehouse (of course all of these are not in the same table in the Warehouse – but showing it like this makes the example simpler).

1. Create a new Work Item.

ID	Rev	State	Previous State	State Change Count	Date
1	1	Active	NULL	1	1/1

 

The Previous State is NULL because prior to Revision 1 the Work Item had no state. The State Change Count is 1 because the Revision changed from state NULL to state Active.

2. Modify a field other than state (i.e. Title)

ID	Rev	State	Previous State	State Change Count	Date
1	1	Active	NULL	1	1/1
1	2	Active	NULL	NULL	1/2

 

The State the Work Item was in, before it was in its current state, is still NULL. The state of Revision 2 didn’t change from Revision 1 so the State Change Count is NULL.

3. Modify the state (i.e. Resolve the Work Item)

ID	Rev	State	Previous State	State Change Count	Date
1	1	Active	NULL	1	1/1
1	2	Active	NULL	NULL	1/2
1	3	Resolved	Active	1	1/3

 

Now the State the Work Item was in before it was in its current state is Active. The state changed so the State Change Count is 1.

4. Modify a field other than state (i.e. Title)

ID	Rev	State	Previous State	State Change Count	Date
1	1	Active	NULL	1	1/1
1	2	Active	NULL	NULL	1/2
1	3	Resolved	Active	1	1/3
1	4	Resolved	Active	NULL	1/4

 

5. Modify the state (i.e. Reactivate the bug)

ID	Rev	State	Previous State	State Change Count	Date
1	1	Active	NULL	1	1/1
1	2	Active	NULL	NULL	1/2
1	3	Resolved	Active	1	1/3
1	4	Resolved	Active	NULL	1/4
1	5	Active	Resolved	1	1/5

 

Alright I think it is clear what the values will be for these fields… now what good are they?

The Previous State serves as a cache of state the work item before it entered the current state. Without this one would have to walk back work item revisions until you found one that had a different state. The Previous State can be used to answer questions like:

For my Work Items that are currently Active, how many of them have been reactivated (Previous State = Resolved)? A large number of reactivated Work Items may indicate developers are not doing enough unit testing.

The State Change Count gives us a way to quickly identify revisions where the state changed. Using this along with the Previous State you can identify things like resolved rates per day or incoming rates per day. For example if you looked for Work Item revisions with State = Resolved, Previous State = Active, and State Change Count = 1 you would get those revisions that were resolved – and the dates. Note that if a work item was reactivated then resolved again you would get it back two times – this may or may not be what you want for your report.