Grant Holliday

Previously I’ve blogged about how to create a TFS Performance Heat Map using Excel 2007. This is great for a once-off look, but it requires you to update the data source and re-apply the conditional formatting every time a new daily column gets added.

The TFS Performance Heat Map report allows TFS administrators, users and IT Operations staff to monitor the performance of TFS commands and answer questions like “Is CheckIn slower today than yesterday?”

I’ve created a reporting services version of the same data, which allows you to export to PDF and setup a subscription to have it emailed to you daily. I followed the SSW Rules to Better SQL Reporting Services 2005 to give it that professional touch. Thanks to fellow Team System MVP Adam Cogan for the tip!

Here’s an example of what the PDF version looks like:

I’ve made the Visual Studio 2005 solution available which includes two projects. A C# assembly for doing the Heat Map colour calculations and a SQL Reports project. You will need SQL 2005 Business Intelligence Studio installed to be able to open that project. The zip file includes everything necessary to deploy the report manually without these tools though.

Download PerformanceHeatMapReport.zip ~57Kb

Manual Report Deployment

To deploy the report manually, these are the basic steps:

1. Extract the contents of the ZIP file to your server
2. Setup a new or existing service account (TFSREPORTS) with data-reader access to the TfsActivityLogging database
3. Open ‘Performance Heat Map.rdl’ and change the timezone offsets to match your timezone. (see below)
4. Open http://your-server/Reports
5. Select ‘Upload File’ and choose ‘Performance Heat Map.rdl’
6. Select ‘New Data Source’ (see below)
7. Select ‘Properties’ of the report
8. Select ‘Data Sources’
9. Select the data source that you just created
10. Apply the changes
11. Deploy the Utilities DLL (see below)
12. View the report

Changing the Timezone

In the SQL query that I use, I convert all the StartTime dates from their GMT times their local times. I also set all the command times to 12:00 midnight, so that the grouping works correctly.

The report is currently configured for GMT+11 (Australian Eastern Standard Daylight Time).

• Open ‘Performance Heat Map.rdl’ in notepad
• Do a Find/Replace on “DATEADD(hh, 11, StartTime)” and replace +11 with your offset. e.g. “-8″ for PST

In my next revision, I’ll change this to a hidden parameter or something.

Create a new Report Data Source

If you’re not using Visual Studio 2005 to deploy the report, then you will need to manually create an appropriate data source. The settings you’ll need are:

• Connection String: Data Source=SERVER-NAME;Initial Catalog=TfsActivityLogging

This probably shouldn’t be an actual user’s account. You might be better off specifically granting the DOMAIN\TFSREPORTS service account access to the TfsActivityLogging database and using that.

Once you’ve created the data source, you will need to point the report at it.

Deploying the Heat Map calculation assembly

To calculate the different gradients of colours, I’ve used a custom .NET assembly based on the code from Andrew Fryer’s blog post. For Reporting Services to be able to access this assembly, it must be copied to the server.

To use it in the report designer, you need to copy it to:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

To make it available for reports that have been deployed to the report server, you need to copy it to:

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

I hope you find this useful, please leave a comment if you do. I’ve got some ideas for more ‘operational’ reports to create.