The initial learning curve for the TFS Cube is pretty steep. It is quite overwhelming to figure out the relationships between the large number of dimensions and measure groups in the TFS cube at first. In this blog entry I will explain some of the most commonly used perspectives and show how you can easily create Excel reports from them.
*Note* that this blog entry is still relevant to you even if you don't have perspectives on your cube. What I cover here will help you better understand the cube schema. In the demos below I connect Excel to cube perspectives. However you can create the exact same reports by connecting to the Team System Cube.
A perspective defines a viewable subset of a cube that provides focused, business-specific or application-specific viewpoints on the cube. It is a feature only available in the Enterprise Edition of SQL Server. Here are the out of box perspectives, we will explore the first 4 perspectives in this post:
The [Current Work Item] Perspective
Here are the cube dimensions related to the [Current Work Item] measure group:
Use the [Current Work Item] perspective if you would like to analyze the latest Work Item data. For example, you may answer questions such as these:
In short, if you need to analyze the "as of now" work item data in the cube, [Current Work Item] perspective is the right perspective. Beware that the [Work Item History] perspective can answer many of the same questions, but it is much more expensive (slower).
Also use the [Current Work Item] perspective if you would like to analyze Related Work Items. For example, You can answer questions such as these:
There is a corresponding 'Related' dimension for every dimension that slices the [Current Work Item] measure group, e.g. [Area] and [Related Area], [Work Item] and [Related Work Item], etc. This allows you to filter and to show attributes for work items and their related work items. Note that the cube is designed for single hop relationships, i.e. work items and their related work items. For multi-hop relationships, e.g. work items and the related work items relating to their related work items, use SQL queries against the Relational Warehouse
The [Work Item History] Perspective
Here are the cube dimensions related to the [Work Item History] measure group:
Use the [Work Item History] perspective to analyze historical work item data.
Use the [Cumulative…] measures and the [Date] dimension to analyze point in time status or to show historical trend. For example, you can answer questions such as these:
Note that the [Cumulative…] measures are expensive during query time, shorten the date range if your report takes too long to render.
Although you can use the [Work Item History] perspectives to answer some of the questions concerning the latest work item data, use the [Current Work Item] perspective for greater performance.
Use the [State Change Count] measure for rate of change in work item state. For example, you can answer questions such as these:
Note that [State Change Count] returns the number of state transition events, but not the number of work items in state transition. For example, if we have a bug that was active yesterday, and it was resolved, reactivated and re-resolved today. [State Change Count] will return the resolved rate of 2, even though only 1 bug got resolved.
The [Code Churn] Perspective
Here are the cube dimensions related to the [Code Churn] measure group:
Use the [Code Churn] perspective to analyze historical code churn activities on Version Control. For example, you can answer questions such as these:
The [Total Churn] measure is defined as [Lines Added] + [Lines Deleted] + [Lines Modified], and it is the measure you should use to answer questions concerning lines of code churned.
Note that the [Total Lines] measure is expensive during query time. Shorten the changeset, build or date range if your report takes too long to render.
The [Test Result] Perspective
Here are the cube dimensions related to the [Test Result] measure group:
Use the [Test Result] perspective to analyze published test results.
In Team Test, each test can be run and published against a build multiple times. Each build can contain multiple published runs and each run contains at most 1 published result from each test. Let me illustrate the behavior of the different measures using a simple example:
Suppose there are 3 tests in the system and they were run and published to 2 builds in the following manner:
Here's how each measure would behave:
Use the following measures to analyze results across Builds:
Use the following measures to analyze results across Runs:
We will skip the details for the following perspectives. Here are their measure group and dimension relationships for your reference:
The [Build] Perspective
The [Code Coverage] Perspective