TFS Reports – Tfs_Warehouse database

When TFS 2010 was released, the TFS database structure was changed to accommodate the new concept of Collections.  They also changed the warehouse database – the place where data from project collections is gathered (and optimized for reporting).   That database houses the data the will later be processed into the Analysis Services cube. 

If you upgraded from TFS 2008 to 2010, you will now have 2 similar (but very different databases): Tfs_Warehouse and TfsWarehouse (without the underscore).  TfsWarehouse was used by 2008 and is no longer used by 2010.   Any data in that database is stale as of the moment that you upgraded TFS.  If you have any reports that read data from the warehouse, you should point them to Tfs_Warehouse.

Unfortunately, some of the MSDN documentation does not reflect this.  Today as I was refreshing my memory on reports, I ran into this: http://msdn.microsoft.com/en-us/library/bb649551(v=VS.100).aspx.  It shows this query:

SELECT Person.Person, [Work Item].System_State
FROM   [Current Work Item] INNER JOIN
       [Work Item] ON [Current Work Item].[Work Item] =
       [Work Item].__ID INNER JOIN
       Person ON [Current Work Item].[Assigned To] = Person.__ID

The query is not valid in 2010.  This is the equivalent query in TFS 2010:

SELECT Person.Name, [Work Item].System_State
FROM   dbo.DimWorkItem as [Work Item] INNER JOIN
       dbo.DimPerson as [Person] ON [Work Item].[System_AssignedTo__PersonSK] = Person.PersonSK

You should browse through the tables in Tfs_Warehouse to acquaint yourself with the data.  It is really straight forward to join the tables together, but definitely different than it was in 2008.

About esteban

Esteban is the Founder and Chief Technologist at Nebbia Technology, an ALM consulting and Azure-powered technology company. He is a software developer with a passion for ALM, TFS, Azure, and software development best practices. Esteban is a Microsoft Visual Studio ALM MVP and ALM Ranger, Pluralsight author, and the president of ONETUG (Orlando .NET User Group).


Leave a Reply

Your email address will not be published. Required fields are marked *

Are you human? *