A few years before I left Microsoft UK to join RippleRock, I remember having a conversation with one of my future colleagues about RippleRock’s value proposition and their response was simply this:

“We will make your Agile project successful”

Now, of course they went on to caveat that this requires buy-in from the business among other things but RippleRock’s specialism in both Lean/Agile process and ALM/DevOps tooling give them a unique position among the Microsoft UK Partners.

Since I joined the team, RippleRock have continued to deliver on that statement but there is one thing that comes up project after project and that is the difficulty and inaccuracy teams encounter with gaining usable insight into their development process . On top of that, most organisations insist on reporting on metrics that will only drive the wrong behaviour and cause dissatisfaction in the team.

Some examples include:

• How accurate are the team’s initial estimates?
• How many bugs have the testers raised?
• How many lines of code have been written?
• Comparing velocity across teams
• Cost per team/Story Point

This type of behaviour and the difficulty in authoring reporting (initially in TFS/VSTS but also in Jira etc.) caused RippleRock to develop SenseAdapt.  The name obviously comes from the tool’s aim which is to allow teams to ‘sense’ what is going on in their project and ‘adapt’ their process in order to improve things.

Some examples of what SenseAdapt is visualising for your team:

• When will this project complete?
  • Based on real historical data and using Monte Carlo simulation to predict the likely range of completion dates.
• How healthy is our backlog?
  • Are we working on the right things?  Have we given the team all the information they need to do their jobs?
• How much ‘sludge’ is in our backlog?
  • Typically, there is lots.  This is work that never makes its way to the top of the backlog and will never be addressed, it just sits there, growing and demoralising the team
• Where are the bottlenecks in our process?
  • How can we improve the flow of value to users

^{Figure 1: Currently available charts in SenseAdapt}

Our clients have been using SenseAdapt for five months and the insights that it brings to teams and management is leading to real change in both process and project scope as they rely less on the current subjective reporting and groupthink.

Recently we came up with what I think is the perfect description of what RippleRock are enabling with SenseAdapt:

Process Telemetry

Whilst we use tools such as Application Insights and Dynatrace to monitor a running application or website, SenseAdapt monitors your development process and helps you to see and then take action to improve the system.

If you are interested in a demo or a trial of SenseAdapt or want to discuss what it could offer you then contact us.

If you are UK based then come along to our free event at Microsoft Campus in Reading on 6^th May where we will talk about all this and much more.

More details and signup here:

Engaging the Business with Team Foundation Server: From Idea to Live – Microsoft Campus, Reading (Friday 6th May 2016)

More information on SenseAdapt available on http://www.senseadapt.com

Cheers,

Richard

Tweet.

*Update April 2016 – If you are interested in this area and based in the UK then register for our free event at Microsoft Campus in Reading on 6th May*

About 18 months ago I wrote a blog post on TFS Reporting and it has been one of the most popular on my blog for some time.  I think that is mainly because reporting in TFS is so difficult but many teams realise the importance of accessing the data that it contains. 

Although 90% of that post is still valid in the new world of Team Foundation Server 2015 (Update 1) and Visual Studio Team Services, there have been some fairly significant enhancements and also my approach to reporting have changed too.

Visual Studio Team Services Vs. Team Foundation Server

At the time of my first post on this subject, the cloud-hosted version of TFS had just been renamed to Visual Studio Online (VSO) from Team Foundation Service Preview and reporting was one of the few missing features as it did not have a data warehouse or SQL Server Reporting Services. 

However, at the end of 2015, VSO has been renamed again to Visual Studio Team Services (VSTS) and those missing features have been added (custom Process Templates), become obsolete (SharePoint Integration) or in some ways surpassed the current on-premise version (PowerBI reporting, Release Management).

This is because VSTS is evolving rapidly and is updated monthly whereas we typically see on-premise updates quarterly and it could take IT many more months to plan applying the update.  At RippleRock, we run all our internal projects on VSTS and for me it is fast becoming the obvious choice over on-premise as long as you can get over the idea of your project data and source code being stored in the cloud.

So what has changed in the last 18 months?

Charting

Admittedly, Charting hasn’t changed massively but a historical set of charts was introduced which made the feature much more useful.  Take a look at the Trend Charts in addition to the Snapshot charts

I am not going to repeat the details of creating a chart as they are well documented on MSDN:TFS2015/VSTS Charts.  One thing I will note is that you can only create a chart from a Flat Query which can be a little limiting.  Pay close attention to the included Columns and Sort Order of your query too, as they will have an impact on what is available to you in the chart options.

 

Dashboards

In TFS 2013 each team had a home page where Team Administrators could pin queries and charts specific to that team.  However, these homepages could quickly become cluttered and had to serve multiple purposes making it difficult to filter the data you wanted to see.  With TFS 2015 Update 1 (and VSTS since October 2015) this has changed to allow users to create multiple dashboards per team.  So you could easily have a bugs dashboard, stakeholder dashboard etc.

Again this is well documented on MSDN: TFS2015U1/VSTS Dashboards.  Just click the green ‘+’ beside the Overview dashboard and start creating the boards you need.

Each board can have a number of configurable widgets to allow you to only show information relevant to the board.  At the time of writing there are about 15 widgets that can be added to a dashboard by clicking the ‘+’ on a green circle at the bottom of the dashboard.

What is exciting for RippleRock is that you will soon be able to write your own widgets and we will make our SenseAdapt (see below) charts available to your dashboards to give realtime actionable metrics.

Power BI

Power BI is probably the biggest development since the last post and typically the feature was first announced shortly after I published the blog making it almost instantly out of date.

To be fair, at that stage there wasn’t a lot in there, really just some nice metrics for Git users in TFS -  No TFVC, No Work Items, No Test Results.  A lot has been added since then and there is more to come!

Once you’ve signed up, it is pretty easy to connect Power BI to your VSTS account (just remember not to add the .visualstudio.com when you enter your account name).

 

When choosing your project(s), be careful to spell it correctly as it doesn’t appear to check if the project exists and you’ll just get blank data.

Importantly data is not live, this is similar to the OLAP cube in TFS which was refreshed every 2 hours by default.  The last time I checked, PowerBI updated data every 24 hours but this is configurable.

Now you can interact with data in a number of ways.  There are the pre-created reports, natural language queries (how many bugs were created this week?) and then you can create your own reports much like you do with Excel and SSRS.

On premise support for PowerBI is not there yet.

However, it can be done by pointing the cloud hosted PowerBI at your on-premise data warehouse.  Obviously this adds some complexity around access to a server behind the firewall and in my eyes doesn’t get beyond the problems that we see with 3rd Party BI Tools.  I think this will become the main reporting option for TFS but for me, it’s not fully baked yet.

SenseAdapt

SenseAdapt is a custom reporting tool for TFS that was born out of both the frustration at the difficulty of visualising data from TFS and the realisation that all too often the data in the system was poor.  We needed something to allow us to understand how teams were using TFS and to surface issues that could be tackled.

My colleague, Bazil is our Product Owner and has a great blog post on SenseAdapt

SenseAdapt: Data-based, Actionable Insight

SenseAdapt can be installed on an on-premise Web Sever (even the TFS box if you wish) or it will be available as a VSTS Extension (see below).  We use the TFS API and so all the data is live and it does not rely on a the data warehouse.  The UI is web based and can be accessed using any device.  All the charts are interactive so you can see much of the data that underpins the chart.

The tool will help teams improve their Agile process but for me, the outstanding feature is the ability to predict project completion dates.

If you have a fixed delivery date, understanding where your project is predicted to finish with the current backlog and completion rate will allow you to make difficult decisions about the scope of the project.  You might not like what you see but it’s better to know ahead of time if there is likely to be a problem with delivery.

Contact me or Bazil if you’re interested in understanding how SenseAdapt can help your team improve.

VSTS Extensions

The TFS Marketplace was in introduced in November 2015 and I expect to see this expand massively over the next 6 months with both free and commercial plug-ins.  We have SenseAdapt almost ready to go (give me a shout if you’re interested in trying it on your account).  At the minute, it is VSTS only but I expect to see extensions on premise in the next few updates. 

VSTS Marketplace

3rd Party BI Tools

We  recently worked with a client who made extensive use of Tableau (and others who use QlikView and Cognos) which seems to be the gold standard in BI tools.

While things started well, the major issue was users understanding the TFS data warehouse.  It could be incredibly difficult to get the data you wanted which kind of defeats the purpose of having this beautiful BI tool. 

So, while you can do it and get good results, it is not an easy option and unless you already have the tools, it can be very expensive.

REST API on Premise

Just a note that TFS 2015 brought the REST API to on-premise TFS. 

“The REST APIs enable a lightweight way to work with Team Foundation Server from virtually any device, platform, or technology stack. You can create and query work items, queue a build, get recent team room messages, access source code, and accomplish almost any team or code management task.”

In fact the TFS API is now available via NuGet in 4 different packages.  Check out Buck Hodge’s blog post on the subject:

NuGet packages for TFS and Visual Studio Online .NET client object model

• Microsoft.TeamFoundationServer.ExtendedClient This package contains the traditional TFS/VSO client object model that uses the SOAP API. You will need it to get full access to the work item tracking object model, version control, test management, and more. If you’ve used the TFS/VSO client object model in applications before, this is the one you’ve used. Because not every API is available in TFS 2015 or VSO currently as a REST API, there are going to be cases where you must use this package. Similarly, there are new features that have been built with only REST APIs for which you will need the Client package.

• Microsoft.TeamFoundationServer.Client Here you’ll find convenient .NET wrappers for our REST APIs for build, team rooms, version control, test case management, and work item tracking. You can of course call the REST APIs directly without using this library. You will encounter cases where an API is not available in this package and have to also use the ExtendedClient package.

• Microsoft.VisualStudio.Services.Client If you need to access account, profile, identity, security, or other core platform services, you’ll need this package.

• Microsoft.VisualStudio.Services.InteractiveClient This package provides the library necessary to show a user an interactive prompt for credentials to sign in. If you are using basic authentication, personal access tokens, or OAuth, you won’t need this.

Richard

Tweet.

If you made it through my last post, you’ll know that I recently updated a TFS 2013 RTM install to Update 2 to resolve a bug in TFS. 

During the update we have to re-apply service accounts and confirm servers etc.  At one stage we were prompted for the SQL Reporting Services Server and the reporting service password.  The issue we needed to resolve was holding up work and unfortunately we couldn’t locate the SSRS password that we needed.  I asked which was more important – getting the team working again or running reports.  The answer was obviously the former and so we took the decision to skip Reporting Services safe in the knowledge that we could deal with it later as a non-critical issue.

The next day the TFS admin had all the details he needed to re-enable reporting and got stuck into the TFS Admin Console.

MSDN: TFS 2013 - Add a report server

However, he added the server details for the data warehouse, clicked Test Connection and received the following error:

The database you specified cannot be used. The database exists, but its schema is not valid for use with Team Foundation Server.

The obvious reason for the error was that the TFS update had changed the data warehouse schema and now our existing warehouse database was out of date.

I wondered if we could update the warehouse database individually but if you understand the reporting architecture in TFS (read my other blog post if you don’t) then you’ll know that we could just blow away the existing warehouse and re-create it.  So that’s what we did, we now have the imaginatively named Tfs_Warehouse2 and Tfs_Analysis2 databases.  We hooked up reporting again, rebuilt the warehouse using the Admin Console and Bob’s your uncle.

We hit a minor snag later that the analysis database didn’t seem to have reflected the changes so we called the ProcessWarehouse web service followed by the ProcessAnalysisDatabase web service, checked everything completed successfully with the GetProcessingStatus web service (details here) and everything was working again in time for tea and biscuits!

I later found an old TFS 2010 related blog post that confirmed what had happened and that our resolution was correct.  Hurrah!

60 minutes left, let’s go for the hat-trick.

Cheers,

Richard

Tweet.

*Check out the 2015 update to this post

I’ve been answering a lot of questions on Team Foundation Server reporting over the last couple of weeks so I thought I’d put some of that information in a blog post.

Team Foundation Server is an amazing tool for storing information about your development projects and all that information can be retrieved if you know what the options are.

This post is targeted at TFS 2013 but most of it is relevant for TFS 2010 & TFS 2012

How does data flow in TFS?

This is probably the most concise reference of TFS reporting architecture and the various options that I can find: Create, customize, and manage reports for Visual Studio ALM

 

In a nutshell, when you work as normal in TFS - checking in source code, executing builds, running tests and completing work - all that information is being stored in transactional, operational stores within the Team Project Collection database (there is a separate collection database for each Team Project Collection that you create). 

These stores are designed to provide high transaction speed and data integrity in normalised tables meaning data isn’t duplicated.  As a result, it’s not very easy to work with these tables, it’s difficult to know which tables to use to get the data you want and queries can be slow because of all the joins required. 

A number of data adapters then populate a single, relational, data warehouse with the latest data from the stores.  Note that reportable data from all team project collections is published to that warehouse (Tfs_Warehouse) meaning that you can report across multiple collections (obviously, you should be careful who you give permissions to!)

Tfs_Warehouse has a schema that is much easier to understand because it is optimised for queries and reporting instead of transactions.  Additionally, this database can be on a completely different server so queries won’t slow down TFS.  The data warehouse is organised in a star-like schema into fact tables and dimension tables.

Finally, and every 2 hours by default, an Online Analytical Processing (OLAP) database (Tfs_Analysis) is populated to allow easy reporting on that information and this is where most of the standard reports will pull their data from.

You can read more about the architecture used for TFS reporting in Components of the TFS data warehouse

Types of Report

What types of reporting are available in TFS?

• Work Item Queries
• Work Item Reporting
• Out-of-the-box Excel Reports
• Out-of-the-box SSRS Reports
• Team Web Access Charting
• Team Web Access standard reports
• Custom Excel Reports
• Custom SSRS Reports
• SQL Queries
• TFS API
• REST API

Let’s look at them in a little more detail

Work Item Queries

Work Item queries are probably the simplest way of pulling information from TFS.  The data retrieved is always up to date as queries do not rely on the data warehouse. 

There are 3 types of query

• Flat Queries
• Direct Link Queries
• Tree Queries

For more information on Work Item Queries see Query for work items

TFS 2013 Update 2 adds the option to query on Work Item Tags.  If you have not installed TFS 2013 Update 2 or if you have TFS 2012 then  you cannot include tags in your Work Item Queries

Work Item Reporting

Work Item Reporting is the easiest route into custom reporting with TFS.  You can take a Flat List Query and within Excel click the New Report button on the Team menu

or in Visual Studio, click the Create Report in Microsoft Excel

From there you can choose to generate a report for the current state and/or the trend over time

TFS will generate a workbook with a number of reports on separate worksheets and a table of contents.  You are free to customise any of these reports and save the result for later use.

Here is an example of editing an Excel report Custom Burnup chart with TFS and Excel

For more detail, see Creating Reports in Microsoft Excel by Using Work Item Queries

Out-of-the-box Excel Reports

Depending on your chosen process template, and if you have installed SharePoint as part of your TFS deployment then you may have access to a number of standard Excel reports. 

These are included with both MSF templates (Agile & CMMI) but not with the Visual Studio Scrum template.  That said, with a little tweaking they could work with the Scrum template (eg, swap references to Story Points field used by MSF Agile to the Effort field used by Scrum)

They can be accessed through the SharePoint portal or from the Documents tab in Team Explorer.

Here is the Task Progress report as an example.

See Excel Reports for more detail

Out-of-the-box SSRS Reports

Again, depending on your chosen process template (and assuming you have installed Reporting Services) then you will have access to a number of reports automatically when you create your Team Project.  They can be accessed via Web Access, SharePoint and Team Explorer.

View, upload, and organize reports (Reporting Services Reports)

For example, this is the standard Backlog Overview report available with the Scrum process template

For a full list of available reports see Reports (SQL Server Reporting Services)

To view the reports, you must be assigned or belong to a group that has been assigned the Browser role in SQL Server Reporting Services. For more information, see Grant permissions to view or create reports in TFS.

Team Web Access Charting

Charts are new in TFS 2013 and are a bit like Work Item Reports but a lot simpler to create and not as customisable or detailed.  The big benefit is that you can pin these charts to your Home page in Team Web Access (you’ll need TFS 2013 update 2 for that) so they become very visible to your team

My good buddy, Colin, has an excellent write up on reports here although it pre-dates Update 2 so doesn’t mention pinning to the home page or customising colour on the reports (read about that here)

You can view charts with a Standard TFS Client Access License but to create them you must have a Full license which means Visual Studio Premium, Ultimate or Test Professional.  See more about Access Levels for Team Web Access here.

Team Web Access Charting

Team Web Access standard reports

Team Web Access has some useful reports which can be accessed very easily.  The reports are non-customisable but the good thing about them is that they do not rely on the data warehouse cube so they are never out of date.

Velocity

The Velocity diagram shows the number of Story Points (or whatever unit you are using to estimate User Stories/Backlog Items) scheduled and completed across iterations.  This will help with forecasting and release planning for your project.

Cumulative Flow Diagram

The CFD Diagram shows up to 30 weeks of data displaying the number of Work Items and their state over a date range.  If bulges are showing up on your CFD it could be an indicator of a bottleneck in your development process

TFS 2013 Update 2 adds the ability to specify a start date for your CFD which can be very useful if you have a long running project.

Sprint Burndown

Finally, your Sprint Burndown shows the number of hours (assigned to the Tasks) your team has committed to in the sprint.  As work is completed (or added) then the value for Remaining Work on the Y-axis will change and an ideal trend line will show you when you are likely to complete the work if the current work rate continues

TFS 2013 Update 2 finally added the much requested feature to be able to remove weekends from the TWA Burndown chart.  This means the burndown does not show the “steps” of a flat line when no work is completed at the weekend which could be a little demoralising.  Giles covers it in more detail here

Custom Excel Reports

Now you get into some proper custom reporting.  I still feel that the Work Item Query Reports covered earlier in the article are the easiest way to get started with Excel but you can connect to the Analysis Database manually if you like.

Take a look at Create a Report in Microsoft Excel for Visual Studio ALM which describes how to connect Excel to the TFS Analysis database.  Doing it this way means you are creating a report from scratch which is great if you know what you want but there is definitely a steeper learning curve.  Start with reading Perspectives and measure groups provided in the Analysis Services cube for Visual Studio

Custom SSRS Reports

If you have a SQL Server Reporting Services expert on your team then they may prefer to generate a report that way.  The benefit of using SSRS is that the report can be published to Team Foundation Server and it will be available alongside the OOB Reports in the Reports tab of Team Explorer or the Reports website.  It also allows for much more detailed reports than excel and allows you to plot multiple data points on the same report.

You can use:

• Report Builder
• Report Designer

Both report authoring tools can target Tfs_Analysis and Tfs_Warehouse although you may find Report Designer more complex to begin with.  You can view a comparison of the two tools here.

It may be easier to start with an existing report and customise it.  As well as the OOB reports, there are a number of sample reports available such as the Community TFS Report Extensions

Create a Detailed Report using Report Designer

Table reference for the relational warehouse database for Visual Studio ALM

Perspectives and measure groups provided in the Analysis Services cube for Visual Studio

If you create a custom report then you may want to add it to your Process Template so it is automatically available for any new projects created.

Add reports to the process template

SQL Queries

The majority of the reports we’ve discussed so far pull their data from the Analysis Services OLAP cube but although it’s generally more difficult, there is no reason why you can’t grab data directly from the relational data warehouse

The TFS Reporting Guide is generally a good resource for TFS reporting but it has some good examples of running T-SQL queries directly against the TFS Data Warehouse relational database.  For example, check out the Mean Time Between Failure (MTBF) Report which uses the CurrentWorkItemView to retrieve the current state of each work item from the dimension table.  The sample query fetches all Work Items of type Bug that are in a Closed state in the chosen Team Project and calculates the average time difference in hours between them.

You can also query the TFS operational stores but it’s not advised and you should really use the TFS API instead.  For example How to generate a report of active users who log onto the TFS server

TFS API

One of the great things about TFS has always been it’s customisability and there has always been a reasonably well documented API for pushing and pulling information to/from TFS.  For example you can create a TFS Work Item with about 3 lines of C# code.  You can also use the TFS API to create reports in a supported way.  If you have a look at the WCF Service sample for a Build Duration Report in the TFS Reporting Guide Data Warehouse section.

You’ll need to become familiar with Extending Team Foundation too.

REST API

A little while ago an OData Service was released for TFS to allow clients to be created for smartphones, tablets and non-Windows operating systems as long as they supported http requests. 

Team Foundation Server OData API

OData Service for Team Foundation Server v2

TFS Reporting Guide also has a whole section on this

This has now been enhanced as described in A new API for Visual Studio Online.  However, at this stage this API is only available for Visual Studio Online but it will likely be available for on-premise installations in the next version of TFS.

Some Questions

What about Visual Studio Online?

Visual Studio Online (formerly Team Foundation Service Preview) does not use SQL Reporting Services and so there is no data warehouse to connect the standard reports.  You still have the Team Web Access reports and the option to use the TFS API but in addition it is worth checking out the very cool REST API for Visual Studio Online which should make it easier to pull information from TFS Online

What permissions do I need to view and create reports?

Permissions for Excel Reports That Connect To the Analysis Services Cube

What is the permission needed to view and create a Excel report for TFS Analysis Services cube?

If you prefer to use Report Builder to edit or add Reporting Services Reports then it is slightly different, you’d need to have your account added to the Content Manager role for Reporting Services.

Grant permissions to view or create SSRS reports in TFS

I’m using Microsoft Test Manager with the Visual Studio Scrum template and the OOB reports are lacking.  What can I do?

There are some standard reports for testing such as the Test Plan Progress Report and the Test Case Readiness Report.  If that is not enough then it is worth checking out the (now slightly old but still relevant) blog posts here:

Test Case Management (TCM) Reporting - Frequently Asked Questions - Part 1

Test Case Management (TCM) Reporting - Frequently Asked Questions - Part 2

which gives a lot more information on custom excel reporting.  

Are there metrics associated with Builds and Continuous Integration and can we access historic build success ratios and failures etc.?

There is a full build log with diagnostics available for all builds (you can set a retention policy on them) and also the information is published to the data warehouse.  There are OOB reports (Build Summary, Build Success over Time) and options for custom reporting as discussed above.  See Analyze and report on build details and build coverage using the Build perspective for more detail.

I want to my reports to update more frequently

You can change the process control setting

Managing the data warehouse and analysis services cube

Change a Process Control Setting for the Data Warehouse or Analysis Services Cube

My TFS data warehouse is broken

Check your account permissions, review log files and rebuild the warehouse.

Manually process the data warehouse and analysis services cube for Team Foundation Server

TFS Admin Console - Rebuild the Data Warehouse and Analysis Services Cube

TFSConfig Comman Line - RebuildWarehouse Command

I don’t have Reporting Services or SharePoint installed

No problem, get them installed, start generating your reports or sharing your Excel workbooks with your colleagues.

Install Reporting services

Install SharePoint

Upload reports to a team project

Summary

Hopefully someone will find this post useful and I know I am likely to redistribute a lot of the information within it in the future.  You can see that there are many options for pulling data and generating reports from Team Foundation Server and you just need to figure out which best suits your needs.

Thanks,

Richard

Tweet.

I was doing a little custom reporting using Report Builder today and when I came to preview my report I received an error message.

The permissions granted to user 'TFS2013\user' are insufficient for performing this operation.

System.Web.Services.Protocols.SoapException: The permissions granted to user 'TFS2013\user' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'TFS2013\user' are insufficient for performing this operation.
   at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateReportEditSession(String Report, String Parent, Byte[] Definition, String& EditSessionID, Warning[]& Warnings)
   at Microsoft.ReportingServices.WebServer.ReportingService2010.CreateReportEditSession(String Report, String Parent, Byte[] Definition, String& EditSessionID, Warning[]& Warnings)
----------------------------
The permissions granted to user 'TFS2013\user' are insufficient for performing this operation.

That seemed odd as I hadn’t messed around with security settings and it was working recently.  Still, just to be on the safe side, I reviewed the article:

Grant permissions to view or create reports in TFS

Everything looked fine so I spent some time playing around with security settings and doing a little web searching.  I found an old stackoverflow post which talked about deleting the encryption key and lots of other things that seemed unlikely.

So I grabbed a cup of coffee and went outside for some fresh air and the answer hit me!

Simple as that, and kind of obvious - Run Report Builder as administrator.

Doh!  Hopefully this blog post will save somebody a little time and at least it has broken my blogging drought. 

Cheers,

Richard

Tweet.