One of the most powerful aspects of TFS is the fact that data from the different disciplines in the team is stored in one central database. As an ALM platform this is very valuable because we can use this data to gather knowledge of work done over the entire process, not just in an isolated practice. This is something few other tools on the market offer and can be extremely powerful for us as software makers. Unfortunately many teams are unaware of these capabilities of TFS which is something that needs to change.
In the previous chapters on Metrics and ALM Assessments we have looked at important KPIs and how the information from work in each phase in the ALM process is stored in the data warehouse in TFS.
In this chapter we will first drill into the data warehouse and reporting architecture in TFS. Having gained an understanding of the way information is stored in TFS we can then move on and look at the different ways of getting to the information and create reports over the metrics that matters to us.
TFS is built for scale and to handle large volumes of data used by many users. Hence, there must be a saleable solution for reporting to support the analysis of the data. To solve this challenge we will not do heavy reporting on the online system, but instead use a separate data warehouse for analysis.
As you can see from Figure 32-1, data is replicated from the various real-time SQL Server tables in TFS to a set of reporting databases.
Figure 32-1. TFS reporting architecture
Warehouse adapters transform the data from the operational store into the relational warehouse database. The adapter does not translate all data into the warehouse, typically only data that makes sense to do trend analysis are transformed (for instance work item history or test case steps are not available in the data warehouse).
Finally, data is aggregated from the relational warehouse into a Microsoft SQL Server Analysis Services OLAP cube. Online Analytical Processing (OLAP) is a business intelligence solution that enables data mining in large data volumes by restructuring the data in a format more suitable for analytics.
Note Work Item data is added to the warehouse based on the reportable attribute on the work item field definition. The reportable attribute can have the following values:
Note Go back to Chapter 7 if you want to learn how to customize a work item definition and set the reporting properties.
As we saw in the previous section reporting data can come from primarily the following three sources:
Data in the warehouse is transformed from the operational store using a warehouse adapter. Because each tool in TFS typically has its own schema there is an adapter for each store that gets transformed into the relational warehouse.
Note The warehouse adapter architecture is extensible. If you would like to get your custom data into the TFS warehouse (for instance code metrics or custom test results), you can create your own warehouse adapter to do so. It is outside the scope of this book to look at the details, instead have a look at http://msdn.microsoft.com/en-us/library/bb286956.aspx if you are interested in learning more.
Relational Data Warehouse Data Models
The relational data warehouse contains the data transformed from the operational store and is available for us as data in different schemas. We have previously gone through the details on the relational data warehouse in each of the Metrics and ALM Assessment chapters:
The OLAP cube contains aggregated data from the TFS data sources, which is great to use for trend analysis. It is easy to use Excel and the PivotTable and PivotChart to create reports that answer questions such as the number of bugs per tester per day in a sprint.
To use the cube for analysis in Excel or SQL Reporting we need to understand the structure of the data in Analysis Services. Figure 32-2 shows the fundamental structure of elements in an OLAP cube.
Figure 32-2. The structure of an OLAP cube
The cube consists of:
The TFS OLAP cube consists of the following measure groups:
Note The data warehouse is updated on a configured schedule. By default data is pulled from the operational store and written to the relational warehouse within two minutes. The SQL Server Analysis Service cube is then updated every two hours. You can change the refresh frequency by using the Warehouse Web Service as described on MSDN: http://msdn.microsoft.com/en-us/library/ms244694.aspx but do so cautiously because shortening the refresh time generates more load on the TFS server.
Setting Up Access to Report Data
To create reports we need to have permission to query TFS and the TFS warehouse. This list shows what is required for the different tools.
Setting Up Access to the Relational Warehouse
To get access to the relational warehouse to create reports we need to be added to the TfsWarehouseDataReader security role in the Tfs_Warehouse database. To setup access follow these steps:
Figure 32-3. Grant access to relational warehouse database
Setting Up Access to the Analysis Services Cube
Setting up access to the Analysis Services cube is similar to setting up access to the relational warehouse, this time we need to be in the TfsWarehouseDataReader role
Figure 32-4. Grant access to the analysis services database
Monitoring the TFS Data Warehouse
The reporting system depends on a number of components working correctly together. The data that the warehouse adapters expect needs to be well-formed to be processed. Occasionally things will happen that make the warehouse stop updating. The most common reason to problems in the warehouse transformation is conflicts in work item fields between projects. When that happens, fields without problems will still be processed but the ones in conflict will not get updated in the warehouse until the problem has been resolved. To help troubleshoot warehouse problems we recommend the following resources:
Creating Reports
Data from the TFS can be extracted in many ways but unfortunately there is no best way to do this, instead we should try to find the best tool for what we want to achieve. The following list is the tools we have available for creating reports;
The next question to ask about reporting is which data source should we use for creating the reports? Table 32-1 shows the combination of tools and data sources.
Table 32-1. TFS Reporting Tools with Relationship to Data Sources
Reporting Tool | Work Item Query | Relational Warehouse | OLAP Cube |
---|---|---|---|
Excel | Yes | Yes | Yes |
Report Builder | No | Yes | Yes |
Report Designer | No | Yes | Yes |
Let’s now take a look at the different tools and see how we use them to create our custom reports.
Work item queries might be simple but they are still very powerful as a quick way of getting to live information about project activities.
Using work item queries in Excel is great for things such as
Here are a couple examples. The first is an example on a basic product backlog report in Excel. Select the work item query.
Figure 32-5. Opening a work item query in Excel
Next, add columns as needed and use Excel to filter data in the report.
Figure 32-6. TFS reporting in Excel
Another example is a report of closed product backlog items without test cases. This report can be created using a link query such as the following:
Figure 32-7. Reporting over links using a work item query in TFS Web Access
But this is about as detailed we get with work item queries. If we need more details or other TFS data, we need to move up in the tool chain.
Excel reports against the OLAP cube are easy to create once you get a grip on the data in the cube. A great thing with Excel is that it is the best tool to play with the cube to get familiar with the data. So what would be better than to show an example on how to do this then? Let’s create a build status report!
Figure 32-8. Connecting Excel to Analysis Services
Figure 32-9. Connecting Excel to Analysis Services, authentication
Figure 32-10. Connecting Excel to Analysis Services, selecting database and cube for analysis
Figure 32-11. Connecting Excel to Analysis Services, selecting how to view the data
Figure 32-12. Working with Excel and Analysis Services, adding fields
Figure 32-13. Working with Excel and Analysis Services, complete PivotChart
The SQL Server Report Builder is a report authoring tool targeted for business users. It is similar to the way we created a report in Excel against the Analysis Services OLAP cube but the end result will be a native SQL Server report that can be accessed by anyone in the team without installing Excel or having personal access to the reporting data.
To show how the Report Builder works, we will again create a Build status report.
Figure 32-14. Working with Report Builds, connecting to a data source
Figure 32-15. Working with Report Builds, adding data fields
Figure 32-16. Working with Report Builds, mapping fields to areas
Figure 32-17. Working with Report Builds, the complete report
The ultimate solution to TFS reporting is to use the SQL Server Report Designer to develop custom reports. With the SQL Server Report Designer we can create advanced logic behind the reports such as creating any number of data sets for the reports or use custom .NET code to transform the data to fit the report. The same principles as for the earlier tools apply but with the Report Designer we need to be familiar with development using Visual Studio as well.
To get you started with custom SQL Server report we will create a build status report from scratch and deploy it to the TFS SQL Server Reporting Services portal.
Figure 32-18. New Business Intelligence project for reporting
By reusing these names, our report will be easy to install later and will not need any custom database, which will make it easier to maintain the reports. Of course, if you need to get to other data you will create a data source specific for that purpose.
In the General tab, name the data source and set up the connection. We will connect to the Analysis Services cube to get the data for this report (see Figure 32-19).
Figure 32-19. Creating a reporting services report data source
Figure 32-20. Creating a new reporting services report, select data source
Figure 32-21. Creating a new reporting services report, query designer
Figure 32-22. Creating a new reporting services report, adding a chart
Deploy a SQL Server Report in Reporting Services
To deploy a report in Reporting Services we simply need to upload the report.rdl to the Reporting Service portal and configure the report parameters as required by the report.
Navigate to the Reporting Services portal and to the project where you want add the report.
Figure 32-23. Uploading a reporting services report to the report portal
Next upload the file. The report now needs to be configured with the appropriate data source mapping and parameter. Open the report properties and finish the configuration, for instance selecting the data source as in Figure 32-24.
Figure 32-24. Configuring a Reporting Services report
After the report has been configured we can run the report and verify that it works as expected (see Figure 32-25).
Figure 32-25. Running a Reporting Services report
Those were the fundamental steps for creating a SQL Server Reporting Service report. Of course we can do many other things with these reports, including creating nested reports, adding parameters, and so on, but from a TFS data warehouse perspective this is what you need to get started.
Summary
Getting information about the project status is critical to most projects. By defining the metrics for analysis early we can make sure in the end we can also get the reports.
In this chapter we looked at the reporting capabilities in TFS and saw that we have a range of options for generating reports. We can use work item queries to quickly extract the current state of activities. That same list can then be loaded into Excel and turned into advanced, appealing reports using the graphing and pivoting capabilities in Excel. We can also use Excel to create trend reports from the TFS data warehouse. For more advanced scenarios we can use SQL Server Reporting Services and create the reports for detailed analysis or reports that collect data from multiple sources. We have additional features in the SQL Server Reporting Services, such as permission control and automatic scheduling of reporting.
18.119.103.204