TFS Reporting

If you want to do reporting against TFS there are three different data sources to consider. In this post I’ll go briefly through the possible data sources and there usage, pros and cons.

TFS Relational Warehouse
The TFS relational warehouse is an SQL database dedicated for reporting data inside TFS. The TFS Warehouse has a public schema and is the supported place to do SQL reporting against the TFS Data. The information in TFS Warehouse is an extract from the production data and is updated on interval (by default 2 h)

TFS Cube
The TFS cube is an analysis database storing aggregated information from the TFS warehouse. The TFS Cube is most suited for reporting on trends and aggregated info. Reporting tools are primary Excel for AdHoc reporting and SSRS using MDX as the query language. For those not too comfortable with the MDX language there is a tool on codeplex (http://olappivottableextend.codeplex.com) that allows you to use excel to create the query and then lets you show extract the MDX query.

TFS Production data
If you want access to all details of the TFS implementation, without the time delay you need to go directly against the TFS production data. The only supported way to do so is by using the TFS API. The downside is that you need to develop and deploy a solution wrapping the TFS API and consume it in your report.

Summary
For most cases the TFS Warehouse is your first choice, unless you want to do reporting on trends or aggregated information the TFS Cube is probably your first choice . If you can’t do with the (small) time delay or need access to RitchTextFields or other values not present in the TFS warehouse, you will need to develop and deploy your own data source using the TFS API.

TFS OData Services as data source for SSRS reports

In April Microsoft release a beta of TFS OData Services , enabling a simply way to query and update tfs using http Url’s and atom xml data f, even from non-Windows platforms. This is good news for everyone who wants to extend tfs to mobile devices, but I saw an alternative use for it.

Looking for a SSRS data source
If you want to create reports containing details of work item, a story card report for example, you don’t really have a good data source to base your report on. The TFS Warehouse doesn’t offer vital fields like description, history and many other fields of importance. So far the best option I’ve found is to use Ewald Hofman’s solution in this post http://www.ewaldhofman.nl/post/Work-item-query-as-input-for-a-RS-report.aspx. Although it’s a proven working solution it requires both customizations and deployment of a web services . If TFS OData services will be a part of TFS installation it could be the answer I’ve been looking for.

Installing TFS OData services
To test the new TFS OData services, I started installing it on one of our lab servers. Quite quickly it became obvious that TFS OData install is not install that simply installs the TFS OData Services on a Server. It installs a complete development environment for developers who wants to build mobile TFS applications. To get it installed I switch to a lab devbox and installed all the tools required by the install. Once installed I needed to point the TFS OData services to a tfs server, this was no problem. One thing that caused me some problems was to handle the certificates.

OData as datasource in SSRS
As Microsoft has been pushing OData quite a bit, I was expecting it to be fairly easy to consume OData services in SSRS. Sadly I was surprised to find out that SSRS can generate an OData feed, but have no guidance how to consume OData as a data sources. After quite some time searching and asking around my colleagues I found a solution. SSRS have an XML data source, and you can specify an url as connection string. You can also specify Query parameters and how to translate the returning xml using the Query in SSRS. The following XML translates the OData response to fields in SSRS:

<Query>
    <ElementPath IgnoreNamespaces="true">
        feed{}/entry{}/content{}/properties
    </ElementPath>
</Query>

OData & SSRS - Possible but not practical
So it is possible to consume OData services in SSRS, but it’s not really practical as you specify the URL in the data source. OData uses the URL as the query specification, forcing you to have one data source for each query you intend to use. It might be possible to fix this or write an custom data source adapter for OData, using SSRS extension model.

Beta 1 shortcomings
As I got a data source and a query in SSRS, I started to create a simple story card report. Quite soon I run into trouble. Some parts of the URI’s used for accessing queries is less natural, like /ColletionName/Queries('b3844370-e517-471d-a55c-d45f9bf7716d')/WorkItems This formats with guids as identifiers for queries, instead of query name, might work if coding against it, but for manually doing reporting it’s not a great experience.
The next problem is that the beta 1 of TFS OData returns a predefined subset of a work item, regardless of the work item definition, or the query specification. This is a show stopper, at least as a SSRS data source.

Conclusion
If you want to create reports containing details of work item the best option is still Ewald Hofman’s solution to create an web service to execute Work Item Queries using the TFS API. TFS OData has to develop and grow a bit before it can be used as a data source, on the other hand it’s only a beta 1 yet. The potential is there in the OData format and if its developed and delivered as a part of TFS it could be used as a standardized data source for TFS data.

How to change iteration for Burn Down reports in project portal

Last week I needed to change the default iteration for the burn down chart in the (WSS) team project portal . My first thought was that this should be a simple task done in minutes, as the burn down chart is a SSRS report and I know I can change the default parameters for SSRS reports on the properties tab of the SSRS report.

How to change default parameters for SSRS reports
The simplest way to change the default parameter for a SSRS report is to right click on the Reports folder in team explorer and select Show Report Site. Once the report site opens up in your browser, click on the report you want to change, and then the report shows, click on the Properties tab. In the left border, click on the Parameters tab. Now you can see all parameters in the report and change their default values. Some of them are easy to change, others like the iteration parameter is not expressed in a intuitive or user friendly format.

Finding the right iteration expression
To find the value for an iteration, you need read access to the TFS Cube, and a tool such as SQL Server Management Studio. To get the desired iteration value you have to browse the TFS Cube, find and browse the Iteration hierarchy, find the desired iteration and drag it over to the query area. This procedure is described in detail in a post by John Socha-Leialoha (http://blogs.socha.com/2009/10/customizing-report-parameters-for-team.html)

In my case, and I suspect in many other cases, I didn't have read access to the customers tfs cube, forcing me to find another way to get the iteration value. In other cases, access and knowledge to tools could be a show stopper.

An easier way
As I was forced to find another solution, I came up with the idea to create a custom SSRS report showing the iterations and their corresponding iteration values/expression. Creating the report was easy, and all I had to do once created was to upload it to the server and set the data source. Once done, I simply run the report, find the iteration I need and I can copy the iteration value at the same row. Next time I need to change the iteration, I simply run the report again.
I think it is a really simple and good way to solve the problem, so if you running iterations and want to change the current iteration every 2-4 weeks, grab the report from my skydrive http://cid-5d46cae8c0008cf0.office.live.com/self.aspx/.Public/AreasAndIterations.rdl

TFS Scorecard 2010 Beta 1 released on CodePlex

Today I released a beta of TFS Scorecard for TFS 2010 at CodePlex http://TfsScorecard.codeplex.com. Since the release of TFS2010 I've been working on an adopting the TFS Scorecard to the schema changes of TFS2010 data warehouse and cube. Upgrading to the TFS 2010 schema changes was not a big deal and soon fixed. But the old version was dependent on extensions to the tfs cube.

Old version bad install experience
The TFS2008 version was dependent on extensions made to the schema of the TFS Cube for the adoption queries. This made the install procedure all but smooth and simple. To solve this problem I did a TFS Warehouse adapter making the schema changes, but it still was
an extra step to follow and I would personally think twice myself before adding an external third party data warehouse adapter to a production server.

No more extensions to the cube.
Then I started to look at how to upgrade or adopt the cube extensions, I wanted to improve the install experience, or at least not make it worse. After some thoughts I got the idea to skip the cube extensions and do SQL queries against the TFS Warehouse instead. As the TFS Warehouse contains all data used by the cube schema extensions, I could solve the problems with SQL without requiring any changes by the user. Using SQL against the warehouse instead of MDX agianst the cube could of course hurt performance, but I don't think this is a big concern as the Scorecard report is probably scheduled or viewed once a week in most cases.

Manual Test metrics
With the new test tools for manual testing (Microsoft Test Manage ) I thought it would be nice to get a picture of the adoption of manual testing with MTM. So I made an Manual Test metrics in the adoption page.

Beta release- please provide feedback
I think there is some work left to do on the TFS Scorecard report. I didn't take any time to fix the small details of the report graphics. You could always discuss and improve metrics and other features. Download and try the TFS Scorecard 2010 release from codeplex and if you have any ideas, comments or feedback in general I would be pleased if you let me know.

Back in business

This week was the first real work week for a while. For the last eight month I've been on parent leave taking care of my daughter at home. During my parental leave I've been taking a couple of smaller customer assignments. Somehow they ended up being more than a couple, leaving no time for this blog amongst other things.

But this week I'm back in business, My daughter started at the local daycare center and I went back to work. Hopefully this will mean that I can do some post on the stuff I've done during the parental leave. I also started to work on my backlog, hopefully it will result in some posts and announcements.

About announcements, this week I got an email from Microsoft congratulating me to the Microsoft Community Contributor Award. It's always nice with all kinds of feedback, all comments, emails and even awards :) are greatly appreciated and motivates me to share my thoughts, findings and experience.

TFS2010 Automated team project creation

This is a update on a the code from a previous post TFS Admin, Part II - POC Automated Order process. It has been upgraded to handle tfs2010 and different team project collections .

The feature set includes
• Order form for team project.
• Approval of team project orders
• Automatic team project creation
• Assigning of admin permissions to project admin.
• Reassigning work items to project admin
• Assigning contributors (New)
• Applying standard enterprise access rights
• Applying standard policies
• Sending notification email to project admin

Future plans
I'm in progress of implementing post creation synchronization of contributors. At the moment you can add contributors by editing the contributors field in the SharePoint list. The tool then synchronizes (add) the user as contributor to TFS, RS & WSS. So far the tool doesn't remove contributors or synchronize the list of contributors in SharePoint with the team project. The goal is to make it possible to handle the most common tfs user administrative tasks directly in the SharePoint portal, without installing any client side tools.

Code, Binaries and SharePoint list template
You can find the source, binaries and a SharePoint list template at my skidrive http://cid-5d46cae8c0008cf0.office.live.com/self.aspx/.Public/TFS2010%20Automated%20Team%20Project%20Creation.zip. The process of implementing is described in the TFS Admin, Part II - POC Automated Order process post. The process is most about about creating a custom list in sharepoint and edit the tools config file.

Main TFS server upgrade

During the weekend my colleges and I upgraded our main tfs server with 150+ users to tfs2010. We started to plan this upgrade in April, and overall it has been a smooth and easy upgrade. But of course it always turns up things you didn't expect then you started planning. This post contains our unexpected findings, and how we solved them.

The plan
First a short description of our plan. In order to minimize the risk and minimize disruptions, we choose a migration upgrade. Choosing a migration upgrades allows us to have an good and easy rollback plan but even more important, we can do several real life rehearsals.
Our plan covered three rehearsal upgrades. The first rehearsal is just error pruning, go for a upgrade and see what problems you detected. We also start documenting , creating scripts and time every step.
The second rehearsal main objective is to verify your fixes. It is also a good way to test and make final adjustments to your own documented install process. A third run is a good idea, just to prove that everything works without any surprises. After a run without unexpected surprises you are ready to do the upgrade

SharePoint 2010
Our original plan was to move to SharePoint 2010 during the migration. During the first test run it came clear that the heave use of Conchangos Scrum for Team System template in combination with the lack of support and problem to get even the latest SfTS template to work with SharePoint 2010 was a showstopper.Back to wss3 until SfTS supports SharePoint2010

New reports missing permissions
During the migration a reports are created at /TfsReports/DefaultCollection/TeamProject . It turned out that the permissions is not migrated, leaving it inaccessible for team members. Needing to fix this 100+ folders we made a short program to copy the permissions from the old report folders to the new 2010 report folders. You can find the program and the source at my skydrive http://cid-5d46cae8c0008cf0.office.live.com/browse.aspx/.Public/SyncRsRoles.

Conchangos Scrum for Team System wss templates.
It is a well know fact that the SfTS 2.2 template and TFS2010 upgrades represents a big problem, good news is that EMC has released plans to make a WorkItem Migrator, at least a step in the right direction. Never the less, we didn't anticipate to have so much trouble getting SharePoint template for SfTS project portals in place. Without it all project portals and document libraries for project based on the SfTS template is inaccessible, SharePoint simply returning http error 404.
Our solution was to do a simply filecopy of the folder C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\SiteTemplates\SCRUM, from the older server to the same location on the new server.

The new buildreport not working
The jury is still out on this one. The new build report gives us the following error.

• File system error: The record ID is incorrect. Physical file: \\?\D:\OLAPDATA\Data\Tfs_Analysis.0.db\Dim Build Platform.0.dim\5.BuildPlatformName.(All).ostore. Logical file: .

We have spent several hours rebuilding the cube and warehouse without any success. I will post an update when we have solved this issue.