Monday, July 21, 2008

Customized VSTS Reporting Part IIIa - The Relational "Build" Schema

In an earlier post I promised to dig deeper into the mystery that is the TFSWarehouse schema. I don't think anyone will question the fact that you must have a firm understanding of the underlying data if you are going to develop customized reports for any data source, and Team Foundation Server is certainly no exception to this rule.

There are many different logical "segments" of data in the TFSWarehouse, so we'll break these down and take each one individually.

One of the more common questions surrounding TFS reporting is how to create useful reports on Builds. Fortunately the Builds "schema" (This is a bad term to use here, but I do it anyway - it represents the logical grouping of entities that are related to Builds) in the TFSWarehouse relational database is pretty easy to understand.

The following picture shows a logical diagram of the tables related to Build information and how they are related:



The major entities represented here are:

  • Build - Contains information collected from MSBuild such as the Build Type, Drop Location and Start Time
  • Build ChangeSet - Contains information on the changesets contained in a specific build
  • Build Details - Contains information on Build Status, such as the date, duration, status and Team Project
  • Build Project - Contains information about a specific instance of a build. (This is probably the most descriptive table for build dashboard type reporting)
  • Build Flavor - Essentially a "snowflake" on the Build Project table that contains build "flavor" information
  • Build Status - Stores the build "states" as the build progresses and is used in the Details table.
  • Build Quality - Contains a quality indicator on each build

There are many ways to use this information in customized reports, but one simple use of this data would be to provide a report that listed the dates of each build, their drop locations and the changesets used by the build. The query would look like this:

   1: SELECT

   2:     BD.[Date],

   3:     B.[Build Type],

   4:     B.[Build],

   5:     BC.[Changeset],

   6:     B.[Drop Location],

   7:     B.[Build Start Time],

   8:     BS.[Build Status]

   9: FROM [TfsWarehouse].[dbo].[Build] B

  10: INNER JOIN [TfsWarehouse].[dbo].[Build Details] BD

  11: ON B.__ID = BD.__ID

  12: INNER JOIN [TfsWarehouse].[dbo].[Build Changeset] BC

  13: ON B.__ID = BC.__ID

  14: INNER JOIN [TfsWarehouse].[dbo].[Build Status] BS

  15: ON B.__ID = BS.__ID


Hopefully this helps make the build info stand out some and makes it a bit easier to develop build-related reports from the TFSWarehouse.

Friday, July 18, 2008

Team Foundation Server - July 2008 Power Tools

The VSTS team has delivered the latest set of Power Tools for Team Foundation Server. I'll post the list of things included below, but one thing I wanted to point out is that we FINALLY have a way to run the TFS SharePoint extensions in a 64-bit environment! WOO HOO. The WssExt64Bit installer that is included with the Power Tools will now allow you to integrate TFS into 64-bit SharePoint/WSS environments. This will be well received news for those trying to eek out the best possible performance and memory utilization from TFS and WSS integration.

Included in the Power Tools release: (From the Downloads Readme Page)

The Visual Studio Team System 2008 Team Foundation Server Power Tools is a set of enhancements, tools and command line utilities that improve the Team Foundation Server user experience.
The following tools are installed with the tfpt.msi package:

  • Command line tool (TFPT.EXE)
  • Team Explorer IDE menu additions
  • Build Notification tool
  • TFS Best Practices Analyzer
  • Process Template Editor
  • Work Item Templates
  • Custom check-in policies
  • TFS Server Manager
  • TFS Users tool
  • Alert Editor

Wednesday, July 9, 2008

Customized VSTS/TFS Reporting Part IIb - Using the OLAP database

In my last article on this subject, I detailed how you could use the Report Wizard to create a simple report that detailed information about WorkItems, such as the number of state changes and revision summarized on a per project basis. The issue with that approach is that the information stored in the Data Warehouse is really designed to support the more efficient OLAP database for this type of reporting. In a future article on this subject I'll dig into the structure of the databases and discuss which one is used for a given purpose, but I wanted to take a moment and illustrate it here. Generally speaking, most of the customized reporting that deals with summarized data will come from the OLAP database as opposed to the data warehouse.

With this in mind, let's go ahead and construct the same report, but using the OLAP cube as a data source. If you still have your project open from the last exercise, you should have a screen that looks something like this:


To add a new report, right-click on the Reports folder and select Add New Report as shown below:


This will start the now familiar Report Wizard as shown here:


Click Next to bring up the Data Source panel. Create a new Shared Data Source as shown below:


Select "New Data Source", give it a name (I used TFSWarehouseOLAP) and select Microsoft SQL Server Analysis Services as the Type. Select Edit to get to the Data Source Connection Properties editor as shown below:


Type the name of your TFS Database server, choose the TfsWarehouse database, and select Test Connection to ensure that it all works. (Remember that this is an OLAP connection, so even though the name of the database is TfsWarehouse, you are not talking to the SQL Server database engine, you're talking to Analysis Services.) then choose OK. Select Next to move to the Query Designer screen as shown below.


On this screen you can either input a valid MDX query, or use the query builder to construct it for you. (Word of caution here, the Query Builder doesn't do the best job of creating MDX, as you might notice in a few screens) Choose the Query Builder button to load the MDX Query Builder as shown below:


This window is fairly straightforward to use if you've done any work with Analysis Services before, and very convoluted if you've never done so. Basically there are 4 main portions to this screen; The upper left details the cube structure as you can work with it. By default, it selects the first perspective (alphabetically) and displays the metadata contained within that perspective. In the case of the screen above, it's the Build Perspective and associated measures and dimensions. Since we are going to work with WorkItems (specifically WorkItem History) we will change the perspective. Click the ellipsis (...) next to the Build perspective in the upper left and then select Work Item History as shown below:


(By the way, a saavy ready might notice that the screen above tells you to select a "Cube" and I'm telling you to select a "Perspective" -- There is only 1 cube in the TfsWarehouse OLAP database, but there are multiple perspectives. You can think of a perspective as a logical view of a cube - and most simplistic tools like this treat them as the same thing)

Click OK to return to the Query Builder, but with the "correct" cube perspective selected this time as shown below:


The left hand side of this screen shows the structure of the Work Item History perspective. There are Measures, which provide the details of our analysis (these are the things that we can calculate and use), Key Performance Indicators (KPIs) which tell us if we're doing good or not, and Dimensions, which are the things that describe our analysis. The upper right-hand side is a filter area that allows us to filter information that will be provided in the result set, and the lower right hand side is essentially a pivot table, this is where we drop our measures and dimensions to build the analysis. To Start, open the Measures tree item and drop both Revision Count and State Change Count measures to the details area, then open the Team Project Dimension and drag the Project member to the pane just to the left of the measures. Then in the upper section, select the Team Project dimension and select all non-deleted projects in the filter expression as shown below:


Click on OK in the Filter expression dialog and OK in the Query builder to return to the Query string dialog as shown below:


(now you see what I mean about the MDX builder)

Click Next and select a Tabular report type (Normally we would use a Matrix report for OLAP data, but this is a simple example so we'll stick with simple report types) as shown below:


Select Next to bring up the table designer as shown below:


Move the Team_Project to the Group section, and the Revision Count and State Change Count fields to the Details section.

Select Next and choose your preferred report layout (I prefer block, but this is purely a personal preference)


Then choose the style that you will use (I prefer Corporate, but again, it's a personal choice)


Select Next to bring up the summary dialog


Name the report, and save it. This will return you to the Visual Studio window, where you can preview the report or make modifications to the layout (which you will need to do in this case if you followed my directions, because the columns will not quite be wide enough to support the fields)

And there we have it, a report that pretty much mimics the report we made in the last article. The point here is that we can use either the relational data warehouse or the OLAP cube to provide very meaningful information in a very short period of time. In the next article, I'll start digging in to the structure of the databases to help understand what data is available and how to use it.

Monday, July 7, 2008

Yet Another Clarification - Customized TFS Reporting

I made a comment in my earlier posting about using Visual Studio 2005 because I was developing reports against a SQL Server 2005 instance. I didn't really make it clear why that mattered. The Business Intelligence Development Studio (BIDS) is really just a slimmed down version of Visual Studio with some added project templates for Business Intelligence projects (SSIS, SSAS and SSRS). If you already have Visual Studio installed when you install the SQL Server client tools, the installer simply adds these project templates to Visual Studio. One issue that arises though, is that the installer doesn't know anything about Visual Studio 2008, so these templates don't always get properly associated. (In my case I've been able to get the SSRS project and Report Model project templates to work, but not the report wizard)

So, to make it very clear, the officially supported toolset from Microsoft looks like this:

  • SQL Server 2005 --- Use Visual Studio 2005
  • SQL Server 2008 --- Use Visual Studio 2008 with SP1

This is of course all subject to change, but for now, since TFS is generally hosted by SQL Server 2005, you'll need Visual Studio 2005 to handle the report development.

Customized VSTS/TFS Reporting Part IIa - Using the Report Wizard

In my previous post on this topic, I discussed everything that you need on the client in order to develop customized reports for TFS. Now we'll walk through creating a very simple report that lists each Team Project that hasn't been deleted, and summarizes the WorkItems, state changes and revisons. This report can be used to get a quick snapshot of how active any given project is on your server. This report will use the relational database named TfsWarehouse on your TFS database server.

To begin the process of creating this simple report, start Visual Studio and create a new "Business Intelligence" project using the "Report Server Project Wizard" template as shown below:


Name the project and click OK. This will start the Report Design Wizard as shown below:



Click Next, and create a new Shared Data Source named "TFSWareHouseRelational" as shown below:


Click Edit to fill in the name of the SQL Server that hosts your TfsWarehouse database and other required connection information as shown below:


Make sure you test the connection and ensure that the server is available and operational. Click OK to close this screen and then click next to get to the query screen. For this screen, we're simply going to paste in the query shown below. (The query will make more sense in a later article where we did into the data warehouse schema for TFS)


tp.[Team Project]

,SUM([Record Count]) AS [Total Work Items]

,SUM([Revision Count]) AS [Revisions]

,SUM([State Change Count]) AS [State Changes]

FROM [Work Item History] wih

JOIN [Team Project] tp

ON wih.[Team Project] = tp.__ID

WHERE tp.[Is Deleted] = 0

GROUP BY tp.[Team Project]



Once you have the query in place, choose next and then choose a tabular report type as shown below:


Click next, and add Team_Project to the grouping, and the remaining fields to the detail as shown below:



Click Next and then choose the specific formatting you want. I personally prefer the Block layout with the Corporate style, but of course this is just a personal preference. These screens are shown below:



Once you have the style and layout selected, click next to take you to the deployment screen. Here you'll put the name of your SSRS Server and the folder that you want to deploy the reports to. In my case, I'm using my development instance of SSRS to deploy and test the reports so I just use Localhost as shown below:


Once this is specified, click Next to name the report as shown below:


Once you have named the report, click Finish to return to the Visual Studio development environment. This will open to the report designer with the design pane open as shown below:


If you want to see what your report will look like, click the Preview tab to see a report that looks something like this:


Obviously you'll want to play around with the layout of the columns and the colors to meet your requirements, but once you have the report looking the way you want it, you can choose DEPLOY from the BUILD menu to publish the report to SSRS. The report will then be available for use by users of TFS. (We'll discuss the deploying of reports to end-users in a future article)

This rather lengthy article demonstrated a very simplistic approach to creating a report to detail statistics for all Team Projects that are deployed to your TFS instance. In the next article I'll discuss using the wizard with the OLAP cube to detail more information about your TFS projects.

Sunday, July 6, 2008

SSWUG Business Intelligence Virtual Conference

As readers of this blog know, I've been actively participating in the SQL Server Worldwide Users Group (SSWUG) virtual conferences on SQL Server. These conferences are proving to be a very nice way for people who wouldn't normally attend technical conferences to be able to get some pretty good education. SSWUG is extending their conferences for the fall, and will be holding a Business Intelligence conference in September (September 24-26). My friend and colleague Matthew Roche will be chairing the BI conference for SSWUG, and has assembled a pretty impressive list of speakers (How I got on this list I'll never know) for this event.

Matthew has also posted some interesting information about the conference here:

Friday, July 4, 2008

Clarification on Components needed for Reporting in TFS

It was brought to my attention that I left one pretty significant detail out of my previous post. You need to make sure that you have the appropriate licenses in place for SQL Server when you install the Management Studio Express Edition tools.

I certainly don't have enough knowledge around SQL Server licensing to be able to tell you what is right or what isn't, so just make sure you speak to whomever in your organization controls your product licenses to ensure that you are legal.

More information on SQL Server licensing can be found here:

Thursday, July 3, 2008

Customized VSTS/TFS Reporting Part I - Configuring the Development Environment

As I mentioned in my previous post, I've decided to spend a bit of time here discussing custom reporting for VSTS and TFS. (Please feel free to leave me a comment here if there's anything in particular that you'd like to see discussed)

This post will primarily cover the basics of what you need and how to set it all up. Keep in mind that there are many different paths to get to where you need to be to develop reports, but I am going to detail what I would call the "Happy Path" (in my opinion, the easiest way to get to where you need to be). There's a ton of information available out there on MSDN and other places that discuss this, but I'm not sure there's any one concise listing of what you really need.

So, let's get started. The first thing we need to do is discuss the components that we'll need. Since we will be working with both relational (SQL Engine) and OLAP (Analysis Services) databases, we'll need to ensure that we have client tools for both. Of course the easiest way to get these is to install a local copy of SQL Server, but we'll stick with the more common client side components. I am also going to assume that you have Visual Studio Team System available to you. In my case, I'll use Visual Studio 2005, since we'll be talking to an instance of SQL Server 2005 for the purposes of this discussion. So, with those disclaimers out of the way, here's a list of the components you need:

  • SQL Server 2005 Client Tools - When you don't have a local copy of SQL Server installed, the easiest way to get these is through the SQL Server 2005 feature Pack, which can be downloaded here: (You'll need a few components from this list)
    • SQL Server Native Client - Allows you to communicate with SQL Server Relational Databases
    • SQL Server 2005 Analysis Services 9.0 OLEDB Provider - Allows you to communicate with SQL Server OLAP cubes
    • Core XML Services (MSXML) 6.0 - Adds interfaces for various XML activities (Behind the scenes everything you do in SSRS will be XML)
  • Business Intelligence Development Studio - The easiest way to get this is through the SQLExpress Toolkit, which can be downloaded here: Even though BIDS is just a slimmed down version of Visual Studio (It's actual SKU is "Visual Studio Premium Partner Edition") you still need to install this, because you need the designers (Project templates) that are included.

One thing to note here, if you do not have a local instance of SQL Server, you may want to go ahead and download/install the Management Studio Express Edition that is packaged with the SQLExpress Toolkit.

Another disclaimer here. The links I'm posting are always subject to change without notice, and more importantly, the components themselves tend to go through revisions on a regular basis. It might be worth your while to search for the items in question before simply relying on my links.

Ok, so once we have the components downloaded and available, you'll want to install things in the following order (I'm assuming that you already have Visual Studio loaded on your system.. By the way, you can accomplish everything I am doing in this series of posts without the full blown Visual Studio, but I imagine that doesn't really apply here since we are talking about Visual Studio Team System customized reporting):

  1. Core XML Services 6.0
  2. SQL Native Client (Install the appropriate version for your platform, x86 or x64)
  3. SQL 2005 Analysis Services 9.0 OLEDB Provider
  4. Business Intelligence Development Studio (BIDS)
  5. SQL Server Management Studio (SSMS)

Once you get everything installed, you'll know that you have success when you start Visual Studio and see the additional "Business Intelligence" project types as shown here:


Another way you can make sure you have the correct connectivity is to start SQL Server Management Studio and connect both the database engine and Analysis Services to the machine containing your TFS Database as shown below:



Once we have connectivity verified, we can get down to the business of creating a simple report.

I'll detail that in the next article in this series. Again, please feel free to post a comment here if there's anything in particular that you'd like to see discussed.

Wednesday, July 2, 2008

VSTS, TFS and Custom Reporting

One topic of conversation that seems to come up on a regular basis both around the office and around the 'net is how to make "better" the reports that ship with Visual Studio Team System and Team Foundation Server. I always take pause when I hear this, because I've never really considered it the job of the software manufacturer to provide reports for every conceivable use-case, and when you're talking about a product as vast and all-encompassing as VSTS/TFS is, I can't even imagine how many reports that would take. Then I stop and wonder if by "better" people really mean, "show the data that I want to see".

Team Foundation Server ships with a very well thought out (at least in my opinion) reporting infrastructure;  a data warehouse and an OLAP cube. These databases are designed with the idea that customers will use the data contained therein to create custom reports that represent the data elements they want to see (Rumor has it that the next VSTS release, "Rosario" will have even more to offer here, but I wouldn't know anything about that). The schema for both is simple enough that you don't need to be a TSQL or MDX genius in order to figure out how to develop compelling reports. For example, here's a subset of the WorkItem history schema in the TFS Warehouse database (this picture stolen from MSDN Patterns and Practices team by the way):


The logical representation above is implemented in a series of dimension tables and a fact table. The fact table schema looks like this:

As you can see, you can look at this schema and pretty easily tell how you can slice and dice data here to generate customized reports.

Given all this good information, I thought it would be a good idea to write a series of mini-articles on how to develop customized reports in VSTS/TFS. Over the next week I'll post the following here on this blog:

  1. Getting started with VSTS/TFS customized reporting - What you need and how to configure the report development environment.
  2. Creating a simple report using Visual Studio - How to use the wizard to create simple reports from both the relational and OLAP databases.
  3. Deep dive into the TFS Warehouse schema - What data is available and what you can do with it
  4. Deep dive into the TFS Warehouse OLAP cube - What data does the cube provide and how the built-in metrics help you.
  5. Putting it together - developing specific reports to answer specific business requirements

If you do read this blog (and I know there's at least 1 of you out there - Hi Julian!) and would like to see something specific on this topic, please don't hesitate to let me know. (comment here so others can see)

Tuesday, July 1, 2008

Microsoft MVP Award

I received the following email this morning:

Dear Ted Malone,

Congratulations! We are pleased to present you with the 2008 Microsoft® MVP Award! The MVP Award is our way to say thank you for promoting the spirit of community and improving people’s lives and the industry’s success every day. We appreciate your extraordinary efforts in Team System technical communities during the past year....

So I guess this means I need to spend a bit more time actually earning the award.. :)

I have been privileged to work with some of the brightest and most interesting people you can imagine over the last 18 years, and many of them have been and are MVPs, so I am truly honored to be counted among their ranks today.

Check out my public MVP profile here, and while you're there, check out the rest of the crew....