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:

• Current Work Item
• Work Item History
• Code Churn
• Test Result
• Build
• Code Coverage
• Load Test

 

 

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:

 

• What is the active bug count by Area?
• What is the remaining work by person?
• What is the list of active tasks, and how much work is left in each?
• Is there any active bugs assigned to my team that have not been modified in the past week?

 

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).

 

Video: Demo 1 - Active Bug Count by Area

 

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:

 

• List all Tasks with Active related bugs
• What tasks are blocked by active bugs assigned to my team?

 

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

 

Video: Demo 2- All Tasks with Active Related Bugs

 

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:

 

• Bug count as of a certain point in time
• Bug trend over a period of time
• Trend of Completed Work per person over a period of time
• The average time it takes to close an issue

 

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.

 

Video: Demo 3 - Trend of Completed Work per Person over Time

 

Use the [State Change Count] measure for rate of change in work item state.  For example, you can answer questions such as these:

 

• Incoming bug rate over a period of time
• Activation, Resolve and Close rate for over a period of time

 

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.

 

Video: Demo 4 - Incoming Bug Rate over Time


Video: Demo 5 - Activation, Resolve and Close Rate for Work Items over Time

 

 

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:

 

• How much code churn do we have over a period of time?
• How much code churn do we have over a series of build?
• How much code churn do we have over a series of changeset?
• Who introduced the most churn?
• What changesets go into each build?
• How much code churn was introduced as the result of resolving a certain work item?

 

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.

 

Video: Demo 6 - Trend of Code Churn over Build per Person


Video: Demo 7 - Code Churn by Person per Changeset

 

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:

• Build 1
• Run 1-1
• Test 1 - Failed
• Run 1-2
• Test 1 - Passed
• Test 2 - Passed
• Build 2
• Run 2-1
• Test 2 - Passed
• Test 3 - Failed
• Run 2-2
• Test 3 - Passed

 

Here's how each measure would behave:

• [Result Count] – counts all published results individually
• Returns 1 for Run 1-1
• Returns 2 for Run 1-2
• Returns 3 for Build 1
• [Result Transition Count] – counts the number of times the outcome of tests change between runs within the same build
• Returns 1 for Run 1-1 because Test 1's changed no result to Failed
• Returns 2 for Run 1-2 because Test 1 changed from Failed to Passed and Test 2 changed from no result to Passed
• Returns 2 for Run 2-1 because both Test 2 and Test 3 changed from no result to having a result.  Note that results are not carried forward from build to build
• [Cumulative Result Count] – counts the latest result of each test in a build
• Returns 2 for Build 1, the latest results come from Run 1-2
• Returns 2 for Build 2, Test 2's latest result comes from Run 2-1 and Test 3's comes from Run 2-2
• [Latest Result] – returns the string version of the latest result for a test
• Returns "Passed" for both Test 1 and Test 2 for Build 1
• Returns "Passed" for Test 2 and Test 3 for Build 2

 

Use the following measures to analyze results across Builds:

• [Cumulative Result Count] - Ex: How many tests have passed and failed against each build?
• [Latest Result] - Ex: What was the latest result for each test over a series of builds?

 

Video: Demo 8 - Number of Tests Passed and Failed over Build


Video: Demo 9 - Latest Test Result per Test per Build

 

Use the following measures to analyze results across Runs:

• [Result Count] - Ex: How many tests passed and failed against each run?
• [Result Transition Count] - Ex: Was there any improvement in pass rate over the previous run?  i.e. how many failed tests from the previous run are now passing?

 

 

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