Introduction to TFS Data Warehouse and TFS Analysis Services with Excel Reporting

TFS stores all its data in its own SQL Server instance; after installing and configuring TFS (including SQL Server Reporting Services), four databases are created: Tfs_Configuration for configuration information, Tfs_DefaultCollection for project collections, Tfs_Warehouse as a relational data‑warehouse extracted from the collection databases, and ReportServer which holds defined reports.

In addition, an Analysis Services multidimensional data warehouse named Tfs_Analysis is created.

The TFS reporting data warehouse therefore consists of a traditional relational database combined with an Analysis Services database.

There are two ways to define reports: through the Report Server web portal, or by connecting Excel directly to the SQL Server Analysis Services cube, which is the focus of this tutorial.

Preparation : Ensure TFS reporting is configured and job processing is running, verify account permissions (unauthorized accounts cannot see the Analysis Services cube), and install Excel 2013 or later (the demo uses Excel 2016).

Designing a Report with Excel :

1. Open Excel and choose Data → Get External Data → From Other Sources → From Analysis Services.

2. Connect to the TFS Analysis Services server using Windows integrated authentication.

3. Select the Tfs_Analysis database and the Code Churn cube, which contains code‑related metrics.

4. Configure the connection (e.g., file name "代码统计.odc", description "代码统计示例", friendly name "代码统计").

5. Import the data as a PivotTable.

6. In the PivotTable field list, select the desired fields such as Deleted Lines, Added Lines, Modified Lines, and Total Lines under the Code Changes hierarchy.

7. Add the Team Project dimension to the rows area (right‑click the Team Project hierarchy and choose "Add to Row Labels").

8. Complete the setup. Note: this reporting method only supports TFVC source control; Git is not supported in this scenario.

References :

• Managing TFS reports, data warehouses, and Analysis Services cubes: https://msdn.microsoft.com/zh-cn/library/ms244706(v=vs.120).aspx

• Regenerating data warehouses and Analysis Services cubes: https://msdn.microsoft.com/zh-cn/library/cc668753.aspx

• Manually processing TFS data warehouses and Analysis Services cubes: https://msdn.microsoft.com/zh-cn/library/ff400237.aspx