Team Foundation Server 2010 - Relational Warehouse and Cube Schema Changes
[Update 10/19/2009] The changes detailed in this post are still applicable for TFS 2010. I just published another post with additional cube schema changes that we have made for Beta 2.
Team Foundation Server 2010 is a BIG release for us by all counts. We have made conceptual changes; Brian introduced some of the key concepts in TFS 2010 that includes the concept of Team Project Collections (TPCs). These changes required a lot of infrastructure support that we built from the ground up and changes to all the subsystems including the warehouse adapters, relational warehouse schema and the Analysis Services cube schema.
As our Beta 1 release approaches, I wanted to outline the changes to the relational warehouse and cube schema. These are, unfortunately, breaking changes. While we will provide an updated set of out-of-the-box reports (details below), these schema changes will affect your custom reports. By custom reports, I mean SQL Server Reporting Services (SSRS) or Excel pivot table/chart reports you may have authored against the warehouse or cube.
First, a bit of history
We get a lot of validation about the incredible value that our reporting platform provides. But, we also know that SQL Server Reporting Services (SSRS) reports aren’t easy to build. In our previous releases, we had our Analysis Services cube as the only supported interface for report authoring. However, we recognize that MDX has a huge learning curve for building complex reports using SQL Server Business Intelligence Development Studio (BIDS) and that ad-hoc reporting with Excel pivot table/chart or SQL Server Report Builder didn’t go very far.
While the SSRS team has made great improvements in Report Builder 2.0 when compared to the previous version and they continue to invest in making it easier for end users to author reports, we had work to do as well.
Here are some of the main reasons we invested in improving the schema:
Infrastructure support
- We had to make changes to support infrastructure changes such as the concept of Team Project Collection (TPC). For example, we used to have a flat list of Team Projects in 2008 and the project names were guaranteed to be unique on a server. However with TPCs, multiple collections could have project with same names. This poses a problem as multiple collections feed into the same warehouse and cube. Since TPC is a collection of Team Projects, Team Project dimension had to support hierarchy as well.
- If are you wondering why not have a one warehouse and cube per collection, read the next bullet point.
Cross-Team Project Collection reporting
- In 2008, we had a flat list of Team Projects on a server and because all of these feed into a single warehouse and cube, you could report across projects. We had to make changes to continue feeding data from multiple collections into the warehouse and cube to support cross-TPC reporting.
- This direction lines up with our future goal to support multiple warehouses and continue to support cross-TPC reporting where you choose which collections feed into which warehouse. While, we won’t get there in 2010, this enables us to work towards that goal. In TFS 2010, you do get the flexibility to choose whether you want to use reporting or turn it off.
Enabling relational warehouse as reporting interface
- As I mentioned before, we used to have the cube as the only supported interface for report building. MDX has a huge learning curve so it was a big investment for our customers who wanted to build reports.
- In TFS 2010, we wanted to provide views as the supported reporting interface on top of the relational warehouse as people have T-SQL knowledge. Our old naming conventions didn’t help differentiate fact tables from dimension tables. We had work to do to clean up the schema. So, while we updated the schema to support infrastructure changes like adding TPC information to these tables, we also standardized table names.
Cube usability
- Our old cube schema wasn’t very usable as we had a *lot* of top-level dimensions so it wasn’t easy to understand how they relate.
- We create perspectives and they definitely help, but that feature is limited to SQL Server Enterprise Edition.
- Our work item tracking subsystem has a dynamic schema which is very flexible. But this poses a problem in the cube because custom date and person fields that are added to the system and marked reportable end up as top-level dimensions potentially making the list very long and hard to understand.
- Some of the fields were only related to work item tracking, like Area and Iteration, but were top-level dimensions alongside other dimensions like Build that weren’t related.
Cube Schema Changes
Here’s a comparison between TFS 2008 and TFS 2010 using the default list of dimensions that are created:
TFS 2008 TFS 2010
Here are the main changes to the Team System cube in TFS 2010:
Date and Person fields are now attributes on the Work Item dimension rather than being top-level dimensions.
There is still a top-level Date dimension that you can use for building trend reports that combine measure from multiple measure groups like work items and test results.
Area and Iteration dimensions have been folded into the Work Item dimension as true hierarchies and are 14 levels deep similar to Work Item Tracking (WIT) operational store.
Agent Machine information is now available in the Machine dimension.
Some dimension names have been updated to make them more meaningful and provide context especially when looking at the entire list. Here’s a mapping for the name changes: -
<table> <tbody> <tr class="odd"> <td><strong>Old Name</strong></td> <td><strong>New Name</strong></td> </tr> <tr class="even"> <td>Counter ID</td> <td>Load Test Counters</td> </tr> <tr class="odd"> <td>Page Summary</td> <td>Load Test Page Summary</td> </tr> <tr class="even"> <td>Platform</td> <td>Build Platform</td> </tr> <tr class="odd"> <td>Flavor</td> <td>Build Flavor</td> </tr> <tr class="even"> <td>Run</td> <td>Test Run</td> </tr> <tr class="odd"> <td>Source Project</td> <td>Source Project File</td> </tr> </tbody> </table>Result dimension has been replaced with Test Result dimension because of schema changes in the Team Test operational store.
Outcome dimension has been folded into Test Result dimension.
Run By dimension folded into Test Result dimension.
Owner is no longer a top-level dimension but part of Test Result and Load Test Result.
Dimensions starting with ‘Related…’ are available in the Linked Work Item dimension.
Here are the main additions to the Team System cube in TFS 2010 to support the new functionality that is available:
Work item hierarchy and linking, new in TFS 2010, is also supported in the cube. Related link is no longer the only link type available in WIT and there can be single-hop links or tree link types. Single hop links are available in the Linked Work Item dimension and tree hierarchy is available in the Work Item Tree dimension.
In TFS 2010, work item types can now be grouped into categories. For example, ‘Bug’ category can group ‘Bug’ work item type and ‘Defect’ work item type. This information is available in the cube in the Work Item Category dimension and enables cross-project reporting across different work item types that belong to the same category.
Test cases are stored as work items in 2010, these test cases are available as Work Item Test Case dimension. This dimension filters on just test case work items.
- The name of this dimension is subject to change post-Beta 1.
Test Suite, Test Plan and Configuration are new Team Test concepts represented by these new dimensions.
- Note that Test Suite is not available in the cube in Beta 1.
Area Path and Iteration Path are now available as attributes on the Work Item dimension that provide a flat string to be displayed on your reports.
We also added display folders to the Work Item dimension to make it easier to group fields rather than display one long list
- The names of display folders are subject to change post-Beta 1. If you have feedback, please leave a comment or send me an email at sunder.raman at microsoft.com
Relational Warehouse Schema Changes
Though our relational warehouse database was not a supported reporting interface, we have made some changes that are worthwhile to mention for customers who are going directly against this database to author reports.
Here are the main changes to the relational warehouse database in TFS 2010:
TPC information has been added to tables as appropriate
We have created views on top of the relational warehouse to provide reporting interface.
- These views make it easier to query for data and less likely to break reports when moving to future versions.
- The views available in Beta1 are pending some design decisions. So, please note that these views are subject to change post-Beta1.
Naming conventions have been standardized to help differentiate fact tables and dimension tables
TFS 2008 TFS 2010 
Out-of-the-box reports
In TFS 2010, we will ship a brand new set of out-of-the-box SSRS reports that work against the new schema. We also authored these reports as Excel workbooks that will ship with the product as well. These Excel workbooks will “light-up” with our new dashboards in TFS 2010; for customers who use Microsoft Office SharePoint Server (MOSS). These new Excel workbooks will also be available for customers who choose Windows SharePoint Services (WSS). Look for more information about the dashboards on our team blog by one of my colleagues.
In addition, to make it easier for customers using SSRS reports that shipped with TFS 2008, we are also updating TFS 2008 reports to work against the new schema changes as well. These updated reports will ship as part of TFS 2010.
We are planning to release a whitepaper to help convert your custom reports to work against the new relational warehouse and cube schema. Stay tuned. We always welcome your feedback! Please leave a comment on this post or send me an email at sunder.raman at microsoft.com.
Comments
Anonymous
May 19, 2009
If you’ve upgraded your Team Foundation Server to Team Foundation Server 2010 Beta1 you’re probably wonderingAnonymous
May 19, 2009
After you upgrade your server to Visual Studio Team System 2010 Beta 1, you have a wealth of new featuresAnonymous
June 15, 2009
Dev10 Beta 1 has shipped! We are really excited to have you trying our Beta and giving us feedback. TheAnonymous
July 14, 2009
The new features in TFS are awesome. I am doing most of the visual flows describing merge operations with visio right now. My team will be very excited to upgrade. Thank you, and your team, for the hard work!Anonymous
July 15, 2009
That's great to hear, Todd. Thanks for sharing your thoughts. SunderAnonymous
January 12, 2010
Looking to get clarification... I want to drive multiple geographically-distant project collections. Can I still get a holistic view thru a single cube? I am getting conflicting and incomplete info...Anonymous
January 18, 2010
Jeff, In TFS 2010, we create a single Analysis Services cube that gets data from all the Team Project Collections in a single TFS instance. If you set up a separate TFS instance, it will have it's own cube. I am referring to the logical instance as a TFS instance which could be comprised of several physical servers for application tier, data tier etc. So, to answer your question - yes, you can report across project collections using the cube. HTH, SunderAnonymous
June 12, 2010
Hello. In the last page of this article, it talks about releasing a white paper to allow customer reports to work against the new schema. Is this white paper available? For example, we have reports that utilize the data source "TfsVersionControlDS", but this is no longer available because that data source pointed to the TfsVersionControl database, which is now a project collection database. Any thoughts about how to get those reports to function again? Thanks! James P.S. I am not a reporting expert so bear with me :)Anonymous
June 12, 2010
Sorry, the comment above meant to say "In the last paragraph of this article...." Thanks, JamesAnonymous
June 18, 2010
James, Check out John's blog posts: www.socha.com/.../upgrading-visual-studio-team-foundation.html and www.socha.com/.../upgrading-team-foundation-server-2008.html SunderAnonymous
September 29, 2010
Ive upgraded from 2008 to 2010, I noticed the CurrentWorkItemView in views is now empty.....could this be because of the upgrade?Anonymous
March 14, 2011
Thanks for the info. sunder . I need teh following information : where are types for teh different workitem types stored in TFS database / warehouse. I mean for a workitem type X if it has subtypes a, b,c, d ...where are these a,b,c,d storedAnonymous
March 14, 2011
Thanks for the info. sunder . I need teh following information : where are types for teh different workitem types stored in TFS database / warehouse. I mean for a workitem type X if it has subtypes a, b,c, d ...where are these a,b,c,d storedAnonymous
April 20, 2011
I want to collect project usage information of TFS like when a user be added in a project ,can i find this type of data from the TFS relationship database ? And where can I find the TFS database schema document ? I went through the MSDN but have no finding .Thanks in Advance .Anonymous
September 19, 2011
Thanks for the post. Where i can access this object? How create project to acess this?Anonymous
June 25, 2012
Hello, After upgrade from TFS 2008 to TFS 2010, we noticed few values for the "Build Status" and "Build Flavor" are missing. I have not restored "Tfswarehouse" database for upgrade process as considering new Tfs_Warehouse will created. please guide me any thing i am missing in this procedure. Thanks in advance.Anonymous
October 18, 2012
This article shows why I am having such a hard time creating the reports that I used to be able to report with 2008. In 2010, since the Created Date and Resolved Date are not at the top level of the hiearchy they are not really seen as date fields so when I run reports in excel and with SSRS I can't filter work items created in the current week or work items resolved in the current week. Is there a solution that allows me to create these sliding date ranges?