Choosing the Source of Data and Authoring Tool for Your Reports for Visual Studio ALM
When you create reports that show data from Visual Studio Application Lifecycle Management (ALM), 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 Microsoft 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 analytical processing (OLAP) data cube (TFS_Analysis), which is optimized for reporting. The OLAP data cube is best suited to reports that provide aggregated information, such as the number of work items that meet a set of criteria. If you want to create reports that show trends over time, such as burn-down 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 do not include trends or historical data.
In this topic
Data Sources and Authoring Tools
Generating Reports Based on Lists of Work Items Using Excel
Creating Reports Based on the OLAP Data Cube
Generating Reports Based on Warehouse Data
Additional Resources
Data Sources and Authoring Tools
As the following table shows, 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 cannot use the warehouse database effectively. If you use Report Builder or Report Designer, you cannot 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 topics in the following table.
Authoring tool |
Source of data |
Related topic |
|---|---|---|
Excel |
Work item query results |
|
Excel |
OLAP data cube |
|
Report Designer |
OLAP data cube |
|
Report Designer |
Warehouse database |
Generating Reports Based on Lists of Work Items by Using Excel
You can create a list in Excel that uses a work item query or a static set of work items. In some senses, this list is already a detailed report. For more information, see Create, Open, and Modify Work Items Using Office Excel. You can also use the work item query as the basis for generating a set of reports that use the analysis services database.
You can also create PivotTable and PivotChart reports from a list of work items. For more information, see the following page on the Microsoft Web site: PivotTable reports 101 (Microsoft Office Online).
To refresh the contents of the list, click Refresh on the Team toolbar, or open the Team menu and click Refresh. To refresh PivotTable and PivotChart reports, click Refresh Data on the Data toolbar, or open the Data menu and click Refresh Data.
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 Creating Reports in Microsoft Excel by Using Work Item Queries.
For more information about this source of data, see Perspectives and Measure Groups Provided in the Analysis Services Cube for Team System.
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 team project collections is gathered and maintained in the warehouse database. If you are 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 Working with Work Item Fields.
For more information about the warehouse database, see Generating Reports Using the Relational Warehouse Database for Visual Studio ALM.
Additional Resources
For more information, see the following pages on the Microsoft Web site:
See Also
Concepts
Finding Bugs, Tasks, and Other Work Items
Creating, Customizing, and Managing Reports for Visual Studio ALM