Work Item History Perspective
The Current Work Item Perspective enables queries and reports based on the current state of work items on the server. In contrast, the Work Item History perspective provides views on the historical information about work items over time. This perspective is based on the Work Item History relational table. You can use this perspective to answer questions such as:
What was the total count of active bugs each day in the last iteration?
How many scenarios were active each month during the last year?
How many bugs of each priority have been active each day in the last month?
These questions require that you use totals as they appeared at a particular point in time. For these types of questions, the Work Item Count measure is used. This measure is a calculated measure, and appears in the Work Item perspective.
Note
In order to use perspectives with the Team Foundation cube, you must use Microsoft SQL Server 2005 Enterprise Edition or SQL Server 2005 Enterprise (64) Edition on the data tier. SQL Server 2005 Standard Edition that ships as part of Team Foundation Server, does not support the use of perspectives. When you use SQL Server 2005 Standard Edition, the cube elements from all perspectives reside in the Team System data cube.
Another type of query that can be performed by using the historical information in the Work Items perspective, answers questions regarding the activity on a particular day (instead of the total number of items on a particular day). You can use the State Change Count and Revision Count measures to answer questions such as:
How many bugs were closed each day in the last month?
How many bugs were reactivated in the last milestone?
How many priority = 1 bugs did a certain set of users close during a particular week, or iteration?
How many QA Tasks which had their Issue flags set have been closed each week this year?
Besides these measures, any field marked in the process template as playing the role of a measure (that is, reportable="measure") will cause a new calculated measure to appear that enables point-in-time reporting such as the Work Item Count measure. For example, the MSF for Agile Software Development process template declares the scheduling fields of Active, Remaining, and Baseline work as measures. These measures enable reporting to be performed on projects that use integration with Microsoft Project. Projects that are based on the MSF for Agile Software Development process template include measures for these values. You can use these measures to answer questions such as:
How much outstanding and remaining work has a set of work items had over the last month?
How much work was completed by a set of developers?
How much additional work was created after a particular date?
Measures
The following table describes the measures included in the Work Item History perspective. The scheduling measures listed here are included with the default process templates. When measures in the cube are based on fields in a process template, they use the reference name of the originating field, but have a localized translation for the measure name seen when you browse the cube with Microsoft Excel or other reporting tools.
| Measure | Description |
|---|---|
Cumulative Baseline Work |
The number of hours of work from the baseline plan for the selected dimensions. The reference name of this measure is Microsoft_VSTS_Scheduling_BaselineWork. |
Cumulative Completed Work |
The number of hours of work completed for the selected dimensions. The reference name of this measure is Microsoft_VSTS_Scheduling_CompletedWork. |
Cumulative Count |
Cumulative count records the number of work item revisions that have occurred for the selected dimensions. |
Cumulative Remaining Work |
An estimate of the number hours of work remaining to complete selected work items. The reference name of this measure is Microsoft_VSTS_Scheduling_RemainingWork. |
Revision Count |
Revision count records the number of work item revisions that have occurred. This is useful when you view detailed history about work items. For example, a query that returns the Revision Count, and is sliced by the Changed By dimension and filtered by a date range results display the number of times each person has modified a work item. This measure is also useful when displaying the detailed history of a particular work item. |
State Change Count |
State Change Count records the number of times that work items change state. When it is used together with the dimensions in the Work Item History perspective, it can be used to display results for Bug Activations in a particular Area over a particular time range. |
Work Item URL |
The uniform resource locator (URL) for the work item. A URL specifies the protocol that will be used by Web browsers to locate Internet resources and includes the name of the server on which the resource resides, and, optionally, the path of a resource. |
Hidden Measures
In order to build the calculations that provide point-in-time totals, several hidden measures are used. These hidden measures are not exposed to client tools such as Microsoft Excel or Report Designer, but are present in the cube definition of the deployed cube. Hidden measures perform a calculation using the MDX LastChild function that aggregates the total for the measure as-of a particular date.
| Measure | Description |
|---|---|
LastChild Record Count |
Hidden measure used in the calculation of "Work Item Count." |
LastChild Microsoft_VSTS_Scheduling_RemainingWork |
Hidden measure used in the calculation of "Remaining Work." |
LastChild Microsoft_VSTS_Scheduling_CompletedWork |
Hidden measure used in the calculation of "Completed Work." |
LastChild Microsoft_VSTS_Scheduling_BaselineWork |
Hidden measure used in the calculation of "Baseline Work." |
Shared Dimensions
The following table describes the shared dimensions that are included in the Work Item History perspective. You can aggregate the measures along each of these. The Dimension Use column indicates the name of the dimension as it pertains to the measures in the Work Item History perspective. For all work items, there are a common set of dimension uses defined in the Team System cube. These dimension uses are listed as "Common" in the Origin column. Besides these common dimension uses, new dimension uses can be defined by designating fields as "reportable" in the process template definition. For more information about how to use the optional reportable attribute and its values, see Defining Work Item Type Fields. The MSF process templates contain such dimensions as indicated by the values CMMI, Agile, or Common (if the attribute is common to both) in the Origin column in the following table.
For more information about the shared dimensions, see Shared Dimensions.
| Dimension Use | Dimension | Origin | Description |
|---|---|---|---|
Team Project |
Team Project |
Common |
The team project associated with the work item. |
Area |
Area |
Common |
The Area in which the work item is classified. |
Iteration |
Iteration |
Common |
The Iteration in which the work item is classified. |
Date |
Date |
Common |
The date dimension records the date on which a work item was changed. |
Assigned To |
Alias |
Common |
The alias of the person to whom the work item is assigned. |
Assigned To |
Person |
Common |
The name of the person to whom the work item is assigned. |
Changed By |
Alias |
Common |
The alias of the person who changed the work item. |
Changed By |
Person |
Common |
The name of the person who changed the work item. |
Created By |
Alias |
Common |
The alias of the person who created the work item. |
Created By |
Person |
Common |
The name of the person who created the work item. |
Changeset |
Changeset |
Common |
The set of changesets associated with the set of work items that meet the filter criteria. |
Changeset ID |
Changeset |
Common |
The ID of the changeset associated with the set of work items that meet the filter criteria. |
Found In build |
Build |
Common |
The build in which the bug was found. |
Integration Build |
Build |
Common |
The build in which the bug was fixed. |
Target Resolved Date |
Date |
CMMI |
The date and time the work item is targeted to be resolved. |
Proposed Date |
Date |
CMMI |
The date the work item was proposed. |
The Work Item Dimension
The following tables describe the attributes that are included in the Work Item Dimension. This dimension contains all attributes of all work items that are deployed on the server. Every work item definition contains a set of common fields, and these fields will always be attributes in the work item dimension.
| Attribute | Origin | Description |
|---|---|---|
ID |
Common |
The work item's ID, as assigned when the work item was created. |
Previous State |
Common |
The previous state of the work item. |
Reason |
Common |
The reason that the state of the work item changed. |
Rev |
Common |
The revision of the work item. |
State |
Common |
The state of the work item. |
Work Item Type |
Common |
The type of the work item. |
Title |
Common |
The title of the work item. |
Activated By |
Common |
The person who activated the work item. |
Closed By |
Common |
The person who closed the work item. |
CMMI Estimate |
Common |
The estimated number of hours to complete the amount of work. |
Committed |
Common |
Whether the requirement has been committed. |
Discipline |
Common |
The discipline to which the task belongs. |
Exit Criteria |
Common |
The flag to determine whether this scenario should be tracked as an exit criteria for the iteration. |
Issue |
Common |
Issue is used to highlight the work item, for example, to mark a bug as an issue. |
Rough Order of Magnitude (Days) |
Agile |
A rough estimate of the number of person-days to complete the task. |
Meeting Type |
CMMI |
The type of the meeting. |
Priority |
CMMI |
The Priority to the business. |
Probability |
CMMI |
The environment in which the work item was found. |
Proposed By |
CMMI |
The person who proposed the work item. |
Rank |
Common |
The Stack rank for prioritizing work. |
Requirement Type |
CMMI |
The type of the requirement. |
Resolved By |
Common |
The person who resolved the work item. |
Resolved Reason |
Common |
The reason why the bug was resolved. |
Task Hierarchy |
Common |
A string representing Microsoft Project context for the given work item. |
Triage |
CMMI |
Status of triaging the work item. |
UAT |
CMMI |
The user acceptance test (UAT) of the requirement. |
See Also
Concepts
Other Resources
Understanding the Structure of the Data Warehouse Cube
Perspectives
Using Microsoft Excel for Team Foundation Server Reporting