Troubleshooting TFS 2010 reports & Warehouse

 

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:

http://<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”

 

Refresh TFS Warehouse, Cube and Reports on demand

By default, TFS will process it’s Data Warehouse and Analysis Services Cube (and thus update the data for the reports) every 2 hours. Be careful with changing it to lower values than every hour:

Important

If you reduce the interval to less than the default of two hours (7200 seconds), processing of the data warehouse will consume server resources more frequently. Depending on the volume of data that your deployment has to process, you may want to reduce the interval to one hour (3600 seconds) or increase it to more than two hours. [Source: MSDN]

Alternatively you can use this small command line utility from Neno Loje:

Syntax/Usage:

tfsrefreshwarehouse.exe /server:http://servername:8080/tfs [/full] [/status]

Manually process the TFS Warehouse and Cube

Using just the /status paramter returns useful information about cube processing:

Using /status shows the last and next scheduled sync times

(Note: The user needs to have the ‘Administer Warehouse‘ permission in TFS)

Download the tool from here: TfsRefreshWarehouse.exe (.ZIP, 12,8 KB)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s