Reporting in Team Foundation Server 2010 – Part 1: Introduction

This is the first chapter in the series on Reporting in Team Foundation Server (TFS) 2010. In this series, I’ll cover the following topics:

• Walkthroughs for out-of-the-box experience including customizations
• Custom report authoring tools and walkthroughs
• An overview of the reporting architecture

Out-of-the-box, we have several ways to report on the data in TFS to analyze and track project progress, quality and various other metrics:

• SharePoint Dashboards
  • We have a brand new set of dashboards based on Microsoft Office SharePoint Server (MOSS)/SharePoint Server as well as Windows SharePoint Services (WSS)/SharePoint Foundation.

    If you choose to integrate TFS with SharePoint Server deployment, then we enable a complete set of rich dashboards using Excel Reports that “light up” with Excel Services and Team Web Access web parts. Since the reports are based on Excel, they are very easy to customize.

    If you don’t have SharePoint Server in your deployment or you choose to integrate TFS with WSS/SharePoint Foundation then you still get a set of dashboards with reports and Team Web Access web parts. The reports on these dashboards are SQL Server Reporting Services reports written in Report Definition Language (RDL), so they are not as easy to customize as the Excel reports. The Excel Reports are still available on the SharePoint site, but you don’t get the light up experience since Excel Services isn’t available.

• Rich SQL Reporting Services (SSRS/RS) Reports

  We have a whole set of new and improved RS reports, about 16 RDL reports in the MSF for Agile 5.0 template and 15 RDL reports in the MSF for CMMI 5.0 template, providing visibility into bugs, builds, tests etc.

• Excel Reports from Work Item Queries

  This is one of the features that lower the barrier to entry in building your own reports that I like to highlight; the ability to generate a report in Excel based on a work item query.

I’ll cover each of these topics with detailed walkthroughs in the next few chapters. If you have questions or feedback please leave me a comment or send me an email at sunder.raman at microsoft.com

Team Foundation Server 2010 – Cube Schema Changes in Beta 2

In my last post, I wrote about the relational warehouse and cube schema changes coming your way in TFS 2010. We just released 2010 Beta 2 and I wanted to outline additional schema changes we have made in the cube, especially to polish it for final release and also address a few key usability aspects.

Here’s a comparison between TFS 2008, TFS 2010 Beta 1 and TFS 2010 Beta 2:

TFS 2008	TFS 2010 Beta 1	TFS 2010 Beta 2

Here are the main changes to the Team System cube in Beta 2:

• The following dimensions that are no longer supported have been removed:
  • Dimensions related to load test - Load Test Counters, Load Test Page Summary, Load Test Result, Load Test Scenario, Load Test Transaction
  • Machine
• Corresponding measure groups have also been removed:
  • Load Test Counter, Load Test Page Summary, Load Test Summary, Load Test Details, Load Test Transaction Summary

• Probably one of the biggest changes that we have made in this category is to collapse the ‘Work Item History’ and ‘Current Work Item’ measure groups into one ‘Work Item’ measure group which dynamically shows trend if you include Date and current data otherwise.

   

• The above change to the Work Item measure group also meant:
  • We didn’t need ‘Cumulative Work Item Count’ and ‘Current Work Item Count’ measures, so we just have one ‘Work Item Count’ measure
  • We didn’t need different perspectives either, so we just have one Work Item perspective. Note: Perspective is a SQL Enterprise feature and is not available in SQL Standard.
• Dimension names have been updated to address usability. Here’s a mapping for the name changes:

  Old Dimension Name	New Dimension Name
  File	Version Control File
  Changeset	Version Control Changeset
  Configuration	Test Configuration
  Linked Work Item	Work Item Linked
  Source Project File	Build Source Project File
  Work Item Test Case	Test Case

• ‘Test Result’ measure group has been renamed to ‘Test’ and it includes a new ‘Test Case Count’ measure.

  

• The following measure names have been renamed as well:

  Old Measure Name	New Measure Name
  Cumulative Build Result Count	Build Result Count Trend
  Cumulative Point Count	Point Count Trend
  Cumulative Result Count	Result Count Trend

• Calculated date and numeric measures that are meant for being used as filters gets their own measure group.

  

• And, finally, we updated the names of the display folders within the Work Item dimension to follow a consistent pattern so the custom work item fields that are provisioned still fit into this pattern rather than ending up at the root of this dimension. This is a cosmetic change and will not affect reports. Here are some rules around how attributes are organizes in this dimension:
  • Folders are named based on field reference name, so fields under one namespace are grouped together to help finding them in the cube when building a report
  • System fields are the only ones that show up at the root of this dimension as they are commonly used. E.g. Work Item Type, State, Assigned To etc.

    

One last thing I wanted to call out isn’t related to cube schema, but we have changed the name of the Analysis Services database we create by default. The name of database is ‘Tfs_Analysis’. We want to straighten out the terminology instead of overloading the term warehouse to mean both the relational warehouse database as well as the analysis database. Of course, this is just the default and in 2010 you get the flexibility to name this database however you like using Team Foundation Administration Console.

If you have any questions or feedback please leave me a comment of send me an email at sunder.raman at microsoft.com.

[Update 10/21/2009] John Socha-Leialoha has written detailed blog posts, Part 1 and Part 2, to help upgrade custom reports written against TFS 2008 to work with the new schema changes I outlined in this post.

Team Foundation Server 2010 - Relational Warehouse and Cube Schema Changes

[Update 10/19/2009] The changes detailed in this post are still applicable for TFS 2010. I just published another post with additional cube schema changes that we have made for Beta 2.

Team Foundation Server 2010 is a BIG release for us by all counts. We have made conceptual changes; Brian introduced some of the key concepts in TFS 2010 that includes the concept of Team Project Collections (TPCs). These changes required a lot of infrastructure support that we built from the ground up and changes to all the subsystems including the warehouse adapters, relational warehouse schema and the Analysis Services cube schema.

As our Beta 1 release approaches, I wanted to outline the changes to the relational warehouse and cube schema. These are, unfortunately, breaking changes. While we will provide an updated set of out-of-the-box reports (details below), these schema changes will affect your custom reports. By custom reports, I mean SQL Server Reporting Services (SSRS) or Excel pivot table/chart reports you may have authored against the warehouse or cube.

First, a bit of history

We get a lot of validation about the incredible value that our reporting platform provides. But, we also know that SQL Server Reporting Services (SSRS) reports aren’t easy to build. In our previous releases, we had our Analysis Services cube as the only supported interface for report authoring. However, we recognize that MDX has a huge learning curve for building complex reports using SQL Server Business Intelligence Development Studio (BIDS) and that ad-hoc reporting with Excel pivot table/chart or SQL Server Report Builder didn’t go very far.

While the SSRS team has made great improvements in Report Builder 2.0 when compared to the previous version and they continue to invest in making it easier for end users to author reports, we had work to do as well.

Here are some of the main reasons we invested in improving the schema:

• Infrastructure support
  • We had to make changes to support infrastructure changes such as the concept of Team Project Collection (TPC). For example, we used to have a flat list of Team Projects in 2008 and the project names were guaranteed to be unique on a server. However with TPCs, multiple collections could have project with same names. This poses a problem as multiple collections feed into the same warehouse and cube. Since TPC is a collection of Team Projects, Team Project dimension had to support hierarchy as well.
  • If are you wondering why not have a one warehouse and cube per collection, read the next bullet point.
  
  
• Cross-Team Project Collection reporting
  • In 2008, we had a flat list of Team Projects on a server and because all of these feed into a single warehouse and cube, you could report across projects. We had to make changes to continue feeding data from multiple collections into the warehouse and cube to support cross-TPC reporting.
  • This direction lines up with our future goal to support multiple warehouses and continue to support cross-TPC reporting where you choose which collections feed into which warehouse. While, we won’t get there in 2010, this enables us to work towards that goal. In TFS 2010, you do get the flexibility to choose whether you want to use reporting or turn it off.
  
  
• Enabling relational warehouse as reporting interface
  • As I mentioned before, we used to have the cube as the only supported interface for report building. MDX has a huge learning curve so it was a big investment for our customers who wanted to build reports.
  • In TFS 2010, we wanted to provide views as the supported reporting interface on top of the relational warehouse as people have T-SQL knowledge. Our old naming conventions didn’t help differentiate fact tables from dimension tables. We had work to do to clean up the schema. So, while we updated the schema to support infrastructure changes like adding TPC information to these tables, we also standardized table names.
  
  
• Cube usability
  • Our old cube schema wasn’t very usable as we had a *lot* of top-level dimensions so it wasn’t easy to understand how they relate.
  • We create perspectives and they definitely help, but that feature is limited to SQL Server Enterprise Edition.
  • Our work item tracking subsystem has a dynamic schema which is very flexible. But this poses a problem in the cube because custom date and person fields that are added to the system and marked reportable end up as top-level dimensions potentially making the list very long and hard to understand.
  • Some of the fields were only related to work item tracking, like Area and Iteration, but were top-level dimensions alongside other dimensions like Build that weren’t related.

Cube Schema Changes

TFS 2008	TFS 2010

Here are the main changes to the Team System cube in TFS 2010:

• Date and Person fields are now attributes on the Work Item dimension rather than being top-level dimensions.
• There is still a top-level Date dimension that you can use for building trend reports that combine measure from multiple measure groups like work items and test results.
• Area and Iteration dimensions have been folded into the Work Item dimension as true hierarchies and are 14 levels deep similar to Work Item Tracking (WIT) operational store.
• Agent Machine information is now available in the Machine dimension.
• Some dimension names have been updated to make them more meaningful and provide context especially when looking at the entire list. Here’s a mapping for the name changes:
  
  

  Old Name	New Name
  Counter ID	Load Test Counters
  Page Summary	Load Test Page Summary
  Platform	Build Platform
  Flavor	Build Flavor
  Run	Test Run
  Source Project	Source Project File

  
  
• Result dimension has been replaced with Test Result dimension because of schema changes in the Team Test operational store.
• Outcome dimension has been folded into Test Result dimension.
• Run By dimension folded into Test Result dimension.
• Owner is no longer a top-level dimension but part of Test Result and Load Test Result.
• Dimensions starting with ‘Related…’ are available in the Linked Work Item dimension.

Here are the main additions to the Team System cube in TFS 2010 to support the new functionality that is available:

• Work item hierarchy and linking, new in TFS 2010, is also supported in the cube. Related link is no longer the only link type available in WIT and there can be single-hop links or tree link types. Single hop links are available in the Linked Work Item dimension and tree hierarchy is available in the Work Item Tree dimension.
• In TFS 2010, work item types can now be grouped into categories. For example, ‘Bug’ category can group ‘Bug’ work item type and ‘Defect’ work item type. This information is available in the cube in the Work Item Category dimension and enables cross-project reporting across different work item types that belong to the same category.
• Test cases are stored as work items in 2010, these test cases are available as Work Item Test Case dimension. This dimension filters on just test case work items.
  • The name of this dimension is subject to change post-Beta 1.
• Test Suite, Test Plan and Configuration are new Team Test concepts represented by these new dimensions.
  • Note that Test Suite is not available in the cube in Beta 1.
• Area Path and Iteration Path are now available as attributes on the Work Item dimension that provide a flat string to be displayed on your reports.
• We also added display folders to the Work Item dimension to make it easier to group fields rather than display one long list
  • The names of display folders are subject to change post-Beta 1. If you have feedback, please leave a comment or send me an email at sunder.raman at microsoft.com 

  

Relational Warehouse Schema Changes

Though our relational warehouse database was not a supported reporting interface, we have made some changes that are worthwhile to mention for customers who are going directly against this database to author reports.

Here are the main changes to the relational warehouse database in TFS 2010:

• TPC information has been added to tables as appropriate
• We have created views on top of the relational warehouse to provide reporting interface.
  • These views make it easier to query for data and less likely to break reports when moving to future versions.
    
  • The views available in Beta1 are pending some design decisions. So, please note that these views are subject to change post-Beta1.
    
• Naming conventions have been standardized to help differentiate fact tables and dimension tables

  TFS 2008	TFS 2010

Out-of-the-box reports

In TFS 2010, we will ship a brand new set of out-of-the-box SSRS reports that work against the new schema. We also authored these reports as Excel workbooks that will ship with the product as well. These Excel workbooks will “light-up” with our new dashboards in TFS 2010; for customers who use Microsoft Office SharePoint Server (MOSS). These new Excel workbooks will also be available for customers who choose Windows SharePoint Services (WSS). Look for more information about the dashboards on our team blog by one of my colleagues.

In addition, to make it easier for customers using SSRS reports that shipped with TFS 2008, we are also updating TFS 2008 reports to work against the new schema changes as well. These updated reports will ship as part of TFS 2010.

We are planning to release a whitepaper to help convert your custom reports to work against the new relational warehouse and cube schema. Stay tuned. We always welcome your feedback! Please leave a comment on this post or send me an email at sunder.raman at microsoft.com.

Work Item rules workaround: Cannot close task unless Remaining Work is zero

In the last post in this series, Gregg blogged about restricting a bug such that only the creator can close it on our team blog. This is the last post in this series of workarounds that we have been blogging about. We saved this as the grand finale for our series.

Customers have asked us: “I don’t want to allow users to close a task unless they have set Remaining Work to zero”

Here’s the solution:

1. Create a RemainingWorkValidation field

2. Add it to the form definition under the validation tab like the previous examples

3. Add the following rules to the RemainingWorkValidation field

<FIELD type="String" name="Remaining Work Validation" refname="Demo.RemainingWorkValidation">
  <ALLOWEDVALUES>
    <LISTITEM value="No Errors" />
  </ALLOWEDVALUES>
  <WHEN field="Microsoft.VSTS.Scheduling.RemainingWork" value="0">
    <COPY from="value" value="No Errors" />
  </WHEN>
</FIELD>

4. Add the following rule on the transition to closed state

<Transition from="Active" to="Closed">
  <REASONS>
    <REASON value="Deferred" />
    <REASON value="Obsolete" />
    <REASON value="Cut" />
    <DEFAULTREASON value="Completed" />
  </REASONS>
  <FIELDS>
    <FIELD refname="Demo.RemainingWorkValidation">
      <WHENNOT field="Microsoft.VSTS.Scheduling.RemainingWork" value="0">
        <COPY from="value" value="Remaining work has to be zero before closing the work item" />
      </WHENNOT>
    </FIELD>
  </FIELDS>
</Transition>

5. Add the following rule to active state

<State value="Active">
  <FIELDS>
    <FIELD refname="Demo.RemainingWorkValidation">
      <COPY from="value" value="No Errors" />
    </FIELD>
  </FIELDS>
</State>

6. Add the following rule to closed state

<State value="Closed">
  <FIELDS>
    <FIELD refname="Demo.RemainingWorkValidation">
      <WHENNOT field="Microsoft.VSTS.Scheduling.RemainingWork" value="0">
        <COPY from="value" value="Remaining work has to be zero before closing the work item" />
      </WHENNOT>
    </FIELD>
  </FIELDS>
</State>

Here’s what it looks like:

A word of caution, one of our readers ran into an issue with Team System Web Access with the previous closing an iteration workaround where the UI doesn’t refresh on WHEN rule. Though I haven’t had a chance to try this workaround using Web Access, the WHENNOT rule used here may have a similar issue in Web Access.

Work Item rules workaround: Saving the resolved reason

In our last post in this series, Gregg blogged about securing a work item type on our team blog. Another question along these lines: “How can I save the resolved reason? I want to create metrics based on how bugs were resolved, but the value of the ‘Reason’ field is changed when the bug is closed”

Here’s the solution:

1. Add “Microsoft.VSTS.Common.ResolvedReason” field to the form definition

2. Add the following rules to the workflow transition from “Active” to “Resolved”

<Transition from="Active" to="Resolved">
   <REASONS>
    <REASON value="Fixed">
      <FIELDS>
        <FIELD refname="Microsoft.VSTS.Common.ResolvedReason">
          <COPY from="value" value="Fixed" />
        </FIELD>
      </FIELDS>
    </REASON>
    <REASON value="Deferred">
      <FIELDS>
        <FIELD refname="Microsoft.VSTS.Common.ResolvedReason">
          <COPY from="value" value="Deferred" />
        </FIELD>
       </FIELDS>
    </REASON>
    ...
</Transition>

Here’s the end result; I am displaying the field in the Details tab of the Bug form:

This workaround works by copying the resolved reason when the bug is resolved off into the Resolved Reason field.

Work Item rules workaround: Deactivating a work item type

My colleague, Gregg, and I have been posting a series of workarounds to achieve scenarios in Work Item Tracking that aren’t straightforward using our rule engine. In the last post in this series, Gregg blogged about forcing selection of reason. Let’s take a look at another question that we often get: “How do I deactivate/retire a work item type? I don’t want to destroy the type because I want to report on it, but I don’t want others to create new work items of this type”.

Using a similar approach to the previous workarounds:

1. Create a DeactivatedType field as part of the type you want to deactivate
2. Add the following rules to the field:

<FieldDefinition type="String" name="Deactivated Type" refname="Demo.DeactivatedType">

  <COPY from="value" value="This work item type has been deactivated. Use Bug type instead." />

  <PROHIBITEDVALUES>
    <LISTITEM value="This work item type has been deactivated. Use Bug type instead." />
  </PROHIBITEDVALUES>

</FieldDefinition>

Now, add this field to the form definition. Make it prominent, let’s put it at the top of the form. Here’s how the form would look like when a user tries to create a work item of this type:

This workaround works by copying a value to our new field that violates the prohibited values rule, so the new work item cannot be saved. This achieves the net result that your type hasn’t been deleted but work items of this type cannot be created.

My posts on Team Foundation Server

Buck Hodges, one of our Dev Managers, recently referenced one my posts and I got a couple of emails asking me to update my blog and talk about updates on Reporting in TFS that are coming up in our next release.

I moved from Work Item Tracking and am now Program Manager for Reporting Platform on Team Foundation Server.

Over the last two years, I have been occasionally posting topics on Work Item Tracking on our team blog that we maintain for talking about Project Management, Work Item Tracking and Reporting. I had decided not to cross-post the same topic to both the blogs. Maybe it’s time to re-think that decision :)

Here are some topics that I had posted on our team blog:

• Work Item rules workaround- Closing down an iteration
• New Power Tool for handling display name change in TFS
• New Rosario specifications available
• Rich text in work items
• Can I move work items assigned to one user to another using TFSWitDisplayNames tool?
• Work Item Tracking usability improvements - Part 2
• Work Item Tracking usability improvements in Orcas - Part 1

Our team blog also has great posts from my colleagues on topics in WIT, Excel & Project Integration and Reporting. We will add a lot more content about our Rosario release in the upcoming months.

[Update 3/25/2009] Fixed the broken links to my posts on our team blog. Thanks to Buck for catching this!

TfsAdminUtil and Reporting Services service account

The following is a snapshot of the error that you might see if the Reporting Services data sources are not updated when the account is changed or updated:

Reporting Services Error

• An error has occurred during report processing. (rsProcessingAborted) Get Online Help
  • Cannot impersonate user for data source 'TfsOlapReportDS'. (rsErrorImpersonatingUser) Get Online Help
    • Logon failed. (rsLogonFailed) Get Online Help
      • For more information about this error navigate to the report server on the local server machine, or enable remote errors

SQL Server Reporting Services

Most of us are probably already aware of this, but this question comes up quite often. If your Reporting Services service account has changed because you moved your Team Foundation Server installation or if you are using a different account, you will need to run TfsAdminUtil ChangeAccount.

In addition to this, you will also need to update the password and/or username for the TfsReportDS and the TfsOlapReportDS data sources manually using the Reporting Services web site at: http://<ATMachineName>/Reports/

More documentation on this topic: http://msdn2.microsoft.com/en-us/library/ms252480(VS.80).aspx

Visual Studio Team System Chat tomorrow - Wed, Nov 8th

Join members of the Visual Studio Team System product group to discuss features available in Visual Studio Team Foundation Server, Team Editions for Architects, Developers, Database Pros, and Testers. In addition, discuss what's new in the latest Community Technology Preview (CTP).

Join the chat on Wednesday, November 8^th, 2006 from 10:00am - 11:00am Pacific Time.

Add to Calendar

User Account Control (UAC): Do you want to turn it off?

I have read posts on how to turn User Account Control (UAC, formerly LUA) in Windows Vista off. Others like Jesper Johansson and the folks on the UAC team have tried to convince people to leave UAC running.

Security is not convenient, but I think UAC is definitely a step in the right direction. I understand why some people were annoyed with UAC - it was intrusive in Beta 2. But it's much better in RC1 and later. Personally, I like this feature a lot. I definitely don't want Internet Explorer running unprotected - one wrong click is all it takes when you are running as an admin. Vista makes running as a normal user easy and you definitely don't want to turn off UAC when running as an admin. UAC is useful when running as a normal user as well, 'cause I can enter my admin credentials if an application needs it and I get to decide whether to grant access or not.

Now, if the Secure Desktop mode bugs you, then it's understandable to turn just that off. This is the mode where the desktop goes dark highlighting only the UAC dialog waiting for your input. I don't want the setup that I kicked off from a network share while I was doing something else to block my desktop when it's ready to run. If you want to turn this off, go to Control Panel > System and Maintenance > Administrative Tools > Local Security Policy, click on Local Policies > Security Options and scroll all the way down to UAC policies. Open the policy named "User Account Control: Switch to the secure desktop when prompting for elevation", select Disabled and click Ok. From this point on, the UAC dialog won't block the entire desktop when asking for your permission.

Better be safe than be sorry.

A start...

My name is Sunder Raman and I am currently working on Work Item Tracking feature of Team Foundation Server. Like most, I have been toying around with the idea of blogging for a while, but didn't get around to actually starting one until today. And I created two blogs - one for personal stuff and this one so I don't end up with my rants here. That said; this is just a "customary" introductory post :)

I don't know how much I will be able to keep up with two blogs. But, here's a start...