Choose the source of data and authoring tool
TFS 2017 | TFS 2015 | TFS 2013
When you create reports that show data from Azure DevOps Server, you specify one of three sources of data and use one of three authoring tools. The choice of data source and authoring tool are interrelated.
The simplest reports that you can generate are based on work item lists. You can create reports about work items by exporting a work item query to Office Excel. Work item lists are best suited to tables and charts that handle no more than several hundred work items.
You can create current status and historical trend data by using the Online analytic processing (OLAP) data cube (TFS_Analysis), which is optimized for reporting. The OLAP data cube is best suited to reports that provide aggregated information, like the number of work items that meet a set of criteria. If you want to create reports that show trends over time, like burndown or progress charts, you can most easily create them from the OLAP data cube.
You can use the relational warehouse database (TFS_Warehouse) to create reports that provide line-item details. These include reports that contain titles of work items and more complex reports that don't include trends or historical data.
Data sources and authoring tools
Your choice of data source depends not only on the kind of data that you want to show, but also on the tool that you use to create reports. If you use Excel, you can't use the warehouse database effectively. If you use Report Builder or Report Designer, you can't use lists of work items.
| Authoring tool | Work item query results | OLAP data cube (TFS_Analysis) | Relational warehouse database (TFS_Warehouse) |
|---|---|---|---|
| Excel | Yes | Yes | No |
| Report Builder | No | Yes | Yes |
| Report Designer | No | Yes | Yes |
For more information about how you can create reports that access the three sources of data, see the related articles in the following table.
| Authoring tool | Source of data | Related article |
|---|---|---|
| Excel | Work item query results | Use the query editor to list and manage queries |
| Excel | OLAP data cube | Create Excel reports from a work item query |
| Report Designer | OLAP data cube | Create an aggregate report using Report Designer and the Analysis Services Cube |
| Report Designer | Warehouse database | Create a Detailed Report using Report Designer |
Creating reports that are based on the OLAP data cube
The analysis services database is a multidimensional database that aggregates the data from the warehouse database for more efficient analysis. This data source works especially well with Microsoft Excel.
The analysis services database organizes data in a cube structure. The cube contains measures that are aggregated against many dimensions. This structure provides aggregate values, such as the hours of work for a set of work items. The values are selected directly from the cube instead of calculated in the query.
Note
Some measures, such as Work Item.Work Item Count, are not pre-aggregated. They are calculated when the query is performed.
You can easily build PivotTable and PivotChart reports in Excel by using the analysis services database. For more information, see Create Excel reports from a work item query.
For more information about this source of data, see Perspectives and measure groups provided in the Analysis Services cube for Visual Studio.
Creating reports based on warehouse data
The warehouse database is a relational database that organizes data in a set of related tables and provides views and table-valued functions for accessing that data. Data from the project collections is gathered and maintained in the warehouse database. If you're familiar with writing Transact-SQL queries, you can create reports by using the warehouse database.
Note
The warehouse database might contain detailed data that is not present in the analysis services database, depending on the work items that your project uses. For more information about how work item fields are mapped to the warehouse, see Reportable fields reference.
For more information about the warehouse database, see Table reference for the relational warehouse database.