TFS Reporting Architecture Notes
Here are some notes and a list of questions and answers regarding the TFS Reporting architecture. I gave a presentation at this month's Dallas Visual Studio Team System user group. The topic was TFS Reporting but what we really covered was the TFS reporting architecture. We’ll probably follow-up with a second session of “Effective TFS Reporting” to include demos and the meaning of some of the most used TFS reports. I strongly believe that the TFS Reporting architecture includes several areas that should be understood to effectively and efficiently deliver standard and custom reports. So here are some high points for the benefit of others looking for answers.
The TFS Warehouse
The TFS Cube
- Facts
- Dimensions
- Measurements
17 fact tables in the TFSCube
In the TFSWarehouse database, dimension tables have the field "__DimensionMemberActive" and fact tables do not.
Fact tables have measurement fields and foreign keys to dimension tables.
Fact Table Examples:
- Work Item fact table has one row for every work item stored in Work Item operational store.
- Code Churn fact table has one row for every dimension combination.
The 3 WorkItem Field Reportable Attributes (4 Actually)
Fields with the reportable attribute will be exported to the data warehouse and can be included in reports. The reportable attribute takes one of three values.
Note: Once a reportable attribute is set for a field, it can never be changed and is global to all work item types that reference the field.
- Dimension (Integer, Double, String, or DateTime) to the warehouse database and cube as an attribute of the Work Item dimension so that the data can be used to filter reports. Work Item Type and State are good examples of a dimension.
- Detail (Integer, Double, String, or DateTime) warehouse database in “Work Item History” and “Current Work Item” tables.
- Measure (Integer and Double) Each measure will appear in both the Current Work Item and Work Item History measure groups. A formula is allowed for the final measurement aggregation such as Estimated work. Measure type has formula options: sub, count, distinct, count, avg, min, max
- None Leaving a field as “None” for reportable allows it to be changed later and lessens the size of the Warehouse and Cube size.
Ask these questions when specifying the workitem field reportable type.
| Is the field going to appear in a SQL report but not a complex calculation or summary or OLAP report? | Detail |
| Is the field something that will be used in a filter in an OLAP report? | Dimension |
| Is the field a meaningful NUMBER for calculations, a filter, and maybe a formula? | Measure |
| Is the field a string that can be reported in a sub-report instead of bloating the cube? | Detail |
| Is this just a field that is captured but never need on a report? | None |
| It’s uncertain how this field will be used in the future. Can only change later if it is currently “none”. | None |
Resources
- “Creating and Customizing TFS Reports” – A PDF file from the Patterns and Practices Group - Located in the Microsoft Downloads.
http://www.microsoft.com/downloads/details.aspx?familyid=a74486b2-f7db-4a85-97bd-46bf478bda60&displaylang=en - Reports (How Do I in Team Foundation) http://msdn.microsoft.com/en-us/library/ms242890.aspx
- Using Fields for Reporting - http://msdn.microsoft.com/en-us/library/ms194942.aspx
- Team System Rocks: “Creating Custom Reports in VSTS” webcast tutorial.
- SQL Server 2008 Books Online
- TFS Guide (Chapter 15) on CodePlex: http://www.codeplex.com/TFSGuide
- 71 Sample Reports by Buck Hodges: http://blogs.msdn.com/buckh/archive/2006/09/09/tfs_reporting.aspx
- OLAP Definitions: http://altaplana.com/olap/glossary.html
- Understanding the Data Warehouse Architecture: http://msdn.microsoft.com/en-us/library/ms244687.aspx
- Data Warehouse Extensibility: http://msdn.microsoft.com/en-us/library/bb130342.aspx
- BOOK: SQL 2005 Reporting Services Step by Step, MSPress
- BOOK: SQL Server 2005 Analysis Services Step by Step, MSPress
- TFS – Cube Introduction
- http://www.paulstovell.com/blog/tfs-cube-introduction
- http://blogs.msdn.com/teams_wit_tools/archive/2007/04/30/understanding-the-tfs-cube.aspx
- Tutorial Videos
- Demo - Tutorial: Building a TFS report using Report Designer - A couple of report-building demos.
- Demo - Tutorial 2: Building TFS reports using Excel and Excel Services - Building TFS reports in Excel 2007 against the TFSWarehouse.
Random Q & A
Q: Can I use work item queries to perform reporting?
A: Not exactly. Workitem queries perform an optimized query on operational data using a specialized language named Workitem Query Language. Technically you can use the same fields that the workitem query is using but the data will not be in the more optimized TFSWarehouse database. Reports that are run against the operational store will affect normal TFS operation by developers, testers, project managers, automated services, or any other TFS user. A better solution is to find the same fields in the TFSWarehouse and report on them.
Q: How can I improve TFS reporting performance?
A:
- Optimized Reports - Fewer joins and sub-reports when not needed. Use drill-downs sub-reports.
- Move the TFSWarehouse database to another server. http://blogs.msdn.com/vstsue/articles/774113.aspx
- Keep the cube smaller by specifying fields as detail and using SQL to report on them when appropriate such as string fields.
Q: Where can I find out about known issues with TFS?
A: Some issues can be found on the Microsoft site at http://code.msdn.microsoft.com/Project/ProjectDirectory.aspx?TagName=Hotfix,tfs. Others are reported and explained on random blog entries or the very helpful forums at http://forums.microsoft.com/MSDN.
Q: How can I change the TFSWarehouse update period from the default of 60 minutes?
A: This can be changed by running a web service command on the TFS application tier and specifying the number of seconds. Refer to: http://msdn.microsoft.com/en-us/library/ms244694(VS.80).aspx
Q: Can’t use reports in Team Explorer because I have a red X Reports folder icon.
A: This is often because of security settings, proxy servers, firewalls, host files, and security updates. Mickey Gousset wrote a good blog entry regarding this issue. http://teamsystemrocks.com/blogs/mickey_gousset/archive/2007/01/17/1312.aspx#comments
Q: What can I do if I my OLAP cube becomes corrupted or deleted?
A: Run the "SetupWarehouse.exe" utility with the –o option to only rebuild the cube. Then on the application tier, run the web service command, http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx?op=Run. Then on the application tier, run the web service command, http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx?op=GetWarehouseStatus until the status returned is “Idle”.
Q: Where is the project code churn reported?
A: The quality indicators reports to show the overall software quality. Code coverage can reported separately but is best used in a report that also contains related data such as code coverage, test results, and active bugs.
Q: Physically, what is a cube?
A: A set of metadata that uses a relational data store such as TFSWarehouse. When multi-dimensional data is requested, the SQL Server Analysis Server (SSAS) will read the data from the store (the TFSWarehouse database), perform analysis on it, and return it to the requester. The retrieved data is cached and when not used after a period is discarded. The metadata lives on the data tier in the SQL OLAP data directory such as "C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data".
Q: I specified an incorrect reporting type for a field. What can I do to change it?
A: Once the reportable attribute is specified to other than none and it’s used once, that is it. The simplest way is to create and use a new field with the correct report type. An alternative is to create a custom report or data file with the workitem id and the field value. Remove the field using WITField.exe to remove the field. Perform the steps to rebuild the data warehouse and the cube. Add the field using the correct reporting type. The original values can be manually entered in the workitems or a integration tool can be written to set the values.
Q: How can I seamlessly implement my own integration into the data warehouse such as extra business intelligence metrics?
A: The data warehouse is extensible so a managed DLL can be added to the application tier’s adapters to perform ETL operations to the data warehouse at the same intervals wherever it may be located. This provides the data in the data warehouse and the TFS Data Warehouse cube can be extended or another cube created for enhanced reporting . http://msdn.microsoft.com/en-us/library/bb286956(VS.80).aspx
What I didn't mention is the great caching features of Microsoft's SQL Server Analysis Services for the OLAP cube when it reads from the data warehouse. For more understanding on that topic, refer to the Microsoft webcast titled "TechNet Webcast: Real-Time Business Intelligence with SQL Server 2005 Analysis Services" as well as other great webcasts and books.
I hope this helps others appreciate the TFS Reporting architecture.
-Vince