Inside VSTS

TFS 2010 and the SQL Server Analysis Services Cube

Jeff Levinson offers a tour of the new reporting cube functionality in Team Foundation Server 2010.

• By Jeff Levinson
• 06/03/2010

In TFS 2010 the cube has changed significantly at the user interface level and some of the default measures. The usage is the same but this article looks at a tour of the new facets of the cube and how you can use it to make your own custom fields easier to find. This article will focus on the work item measures as they are the most used in reporting. If you have never reported off of the cube through Excel, then this article serves as a brief introduction on how to do this.

Figure 1 shows the Excel pivot table panel which is used for reporting off of the cube.

[Click on image for larger view.]

Figure 1.PivotTable panel.

Looking specifically at the work items measures, there have been some big changes. The first change is that there are no Historical Work Item measures anymore. In fact, the only measures available to you for the work item are the following:

• Completed Work
• Original Estimate
• Remaining Work
• Revision Count
• State Change Count
• Story Points
• Work Item Count

You'll also notice there are no cumulative measures. This is one of the major changes -- the cube handles cumulative and historical values depending on how you do your queries. Simply the fact of using dates as one of the dimensions will pull the historical information. You will also note that on the Show fields related to drop down there are different items related to work items as compared with 2008. These areas are shown in Table 1.

Table 1 -- Work Item Related Fields

Of the Work Item related fields, only the Work Item fields selection allow access to the work item measures, although the measures report back accurately regardless of what items are selected. This is a major change from 2008. In 2008 it was possible to create pivots which could show invalid data -- in 2010 this possibility is greatly reduced because of the re-worked cube.

The structure of items in the pivot table field list has also changed -- and this has implications for you when customizing the process template. Figure 2 shows a view of the new change.

[Click on image for larger view.]

Figure 2. New field structure.

The core work item fields (and a few others) are shown in the root of any dimension, but other items are broken down into folders which match their reference name. For example, in the Microsoft.VSTS.Common "namespace" are items such as Microsoft.VSTS.Common.ActivatedBy or Microsoft.VSTS.Common.ActivatedDate. This means that when you are customizing the process template you can make it easier for users of the cube to find relevant data by using good naming conventions.

PowerPivot

A tool that I have been using recentlym which has been of great help when reporting on data in TFS, is PowerPivot. PowerPivot is a free add-in from Microsoft for Excel which allows for the processing of millions of rows of data quickly and easily. Better yet, it allows you to join data across data sources.

So take for example the fact that you may customize a work item to include a budget number and have the developers fill in hours spent in the work items. If you wanted to determine hours against a budget and see the budgetary information, you could use PowerPivot to take the data from TFS and pull the related budget information in one spreadsheet. You can perform pivots on the data, load it up to SharePoint 2010, add KPI's and other business intelligence functionality. PowerPivot can be downloaded from www.powerpivot.com.