TFS and reporting

TFS 2013 has a reporting service that you can use to design reports; you can display them on the dashboard in order to show the team's progress in the project's development life cycle. As this book's main focus is on TFS reporting, it's important to understand how data flows in TFS for the reporting service and its architecture.

The reporting architecture

The reporting service is integrated with a SQL server as it has a separate data warehouse to generate reports. The main component of the TFS reporting architecture are:

• Operational Stores: Every activity in TFS gets stored in relational databases in the SQL server, these are known as operational stores. These mainly contain the TFS configuration and team project collection information.
• Warehouse adapters: These are manageable assemblies that fetch data from operational stores and transform them into a standardized format that is compatible with warehouse databases and writes them to the warehouse relational databases.
• Relational Databases: These have a schema that specifies the fields such as dimensions, measures, and reporting data details. The data warehouse is organized in a star schema, consisting of fact tables and dimension tables. For example, the work item fact table has one row for every work item stored in the work item operational store. Fact tables are a good source of information for the reports and show the latest activity. A dimension table stores the set of values that exists for given dimensions. Measures are the values taken from operational data.
• Analysis service cube: To report about day-to-day work items or test results, the warehouse needs to retain the state of every item for each day; this allows the data cube to aggregate the measures by day. The data cube aggregates the data from the relational databases. Whenever the data cube is processed, the data from the relational databases is pulled into the cube to get aggregated and stored. The cube provides a central place to obtain data for reports without having to know the schema for each operational store and access each store separately.
• Reporting with Report Designer: This is a tool in Visual Studio that is used to design the reports. Report Designer provides tabbed windows for data, layout and preview, query builder, and an expression editor to create a report.
• Excel Reports: TFS is integrated with Excel to manage projects and its reports that use Microsoft Excel. Microsoft Excel provides pivot tables and charts to view and analyze multidimensional data. You can bind these pivot tables directly to the Team Foundation cube, so you can interact with the data in the cube.
• Security: TFS administers have access to the data in the data warehouse by granting or revoking permissions on the user's account. By default, write access to the warehouse is restricted to a service account under which the warehouse service runs. A user who has permission to view the data in the warehouse has full access to all of the data for all team projects in all team project collections.

The following diagram shows the reporting architecture for TFS 2013:

Note

For more information on TFS and Reporting, have a look at the following links:

• http://msdn.microsoft.com/en-us/library/ff647430.aspx
• http://msdn.microsoft.com/en-us/library/bb649552.aspx
• http://msdn.microsoft.com/en-us/library/ms244687.aspx
• http://blogs.msdn.com/b/jasonsingh/archive/2013/03/27/tfs-reports-alm-means-the-everyone-from-your-vp-to-your-customer.aspx

Types of reports

TFS 2013 offers the following types of report, which we will look at in depth in later chapters:

• Work item queries: These are used to review or update work items.
• Work item reporting: Custom work item reports are generated using Microsoft Excel, the project server, and SSRS reporting tools.
• Excel reports: There are a number of standard Excel reports available via the selected process template while creating a team project; these reports can be accessed via the SharePoint portal or Team Explorer's Document tab.
• SSRS reports: There are a number of default reports available via the selected process template with installed SSRS; these can be accessed via Web Access, SharePoint or Team Explorer.
• Team Web Access reports: TFS 2013 has excellent chart and standard reports that show a visual representation of work items. It can be accessed via Team Web Access.
• Light weight reports: TFS 2013 introduced a new feature called light weight reporting that provides the ability to create real-time reports based on query results and does not rely on the warehouse or cube. It offers real-time Burndown charts, velocity, and CFD diagrams directly within Team Web access.
• SQL queries: Major reports in TFS display information from the analysis service OLAP cube. However, running T-SQL queries directly against relational databases provides the facility to create more granular-level custom reports.
• TFS API: This provides binaries to create a custom report using only a few lines of C# code.
• REST API: Currently, this API is only available in Visual Studio Online (VSO) and is an implementation of OData Protocol. The REST API makes it dramatically easier to integrate Visual Studio Online (VSO) with other web-based tools and to access VS Online from any mobile device, irrespective of whether they are third-party commercial tools or custom ones you are building.