TFS 2010 Warehouse & Reporting trouble shooting basics

  • Article

Recently I dealt with a couple of service requests where customers have not been able to view reports because the warehouse and cube processing failed.

I made some observations and figured out some approaches to get resolve such issues that go beyond what’s documented in MSDN.

 

Background: Physical Architecture of TFS Reporting

Each TFS component maintains its own set of transaction databases. This includes work items, source control, tests, bugs, and Team Build. This data is aggregated into a relational database. The data is then placed in an Online Analytical Processing (OLAP) cube to support trend-based reporting and more advanced data analysis.

The TfsWarehouse relational database is a data warehouse designed to be used for data querying rather than transactions. Data is transferred from the various TFS databases, which are optimized for transaction processing, into this warehouse for reporting purposes. The warehouse is not the primary reporting store, but you can use it to build reports. The TfsReportDS data source points to the relational database. The Team System Data Warehouse OLAP Cube is an OLAP database that is accessed through SQL Server Analysis Services. The cube is useful for reports that provide data analysis of trends such as ‘how many bugs closed this month versus last month?’ The TfsOlapReportDS data source points to the Team System Data Warehouse OLAP cube in the analysis services database.

 

10 Steps to trouble shoot TFS Reporting

1.      On the TFS Application tier server, open an Administrative Command Prompt

2.       Run the following command: Net Stop TFSJobAgent

3.        Once this completes, run the following command to restart the TFSJobAgent: Net Start TFSJobAgent

4.       Open the TFS Administration console, and select the Reporting Node

5.       Click the Start Rebuild link to rebuild the warehouse.  Refresh this page until it displays “Configured and Jobs Enabled”

6.       Open a web browser and navigate to the warehousecontrolservice.asmx page at:

https://<server>:8080/tfs/teamfoundation/administration/v3.0/warehousecontrolservice.asmx

7.       Click ProcessWarehouse, then click Invoke on the subsequent page.  This should return True.

8.       Return to the WarehouseControlService.asmx page, then click ProcessAnalysisDatabase.

9.       Enter Full for the processingType, then click Invoke, this should also return True.

10.    Return to the WarehouseControlService.asmx page and click GetProcessingStatus, this should return the processing status page  

         with the current processing results.  It should indicate Full Analysis processing is occurring.  Refresh this page until the status

         (ResultMessage) of the “Full Analysis Database Sync” indicates “Succeeded”

 

Judgment Day – Rebuild it from scratch:

I’ve seen situations where all this did not help. If you are willing to really get it solved, you can proceed with the following (useful after modification of Project Template):

Pending TFS jobs might conflict with the procedure above…Selecting 'Stop Jobs' in the TFS Admin console will work for the warehouse jobs. (Thanks to grant who pointed it out!)

Now make the TFS Admin Console rebuild the whole warehouse and cube system:

rebuild

Define new DB Names for Warehouse and Analysis Services:

whas

 

After a couple of minutes to hours the rebuild jobs are done and the reports are working as expected.

As all transitions are stored in the Project Collections and Configuration Databases, rebuilding the Warehouse and Cube DB does not result in any loss.

All historical data is included in the reports.

Good Luck!