Understanding Branches in TFS Warehouse

Other Questions Notion
1

I’m looking to improve my SQL query so that it generates a list of changesets related to a particular branch in the TFS warehouse. However, I’m having trouble finding where branch details are kept in the warehouse structure.

Here’s the SQL I have right now, which retrieves all updates made recently:

SELECT [RevisionID]
      ,[RevisionName]
      ,[ModifiedDate]
      ,[CommittedBy__UserName]
FROM [TFS_Warehouse].[dbo].[vFactChangesetDetails]
WHERE [ModifiedDate] > '2012-12-01 00:00:00'

I need to know how to add a condition that filters changesets based on a specific branch, but I’m unsure which table or column holds the branch information. Has anyone had experience with TFS warehouse queries and can help me with this?

2

To locate the branch information, you should explore the DimFile table, specifically the ServerItem column. For your SQL query, you’ll need to join with the relevant file tables to integrate the branch context. Here’s how to adjust it:

SELECT DISTINCT fc.[RevisionID]
      ,fc.[RevisionName]
      ,fc.[ModifiedDate]
      ,fc.[CommittedBy__UserName]
FROM [TFS_Warehouse].[dbo].[vFactChangesetDetails] fc
JOIN [TFS_Warehouse].[dbo].[FactWorkItemChangeset] fwc ON fc.ChangesetSK = fwc.ChangesetSK
JOIN [TFS_Warehouse].[dbo].[DimFile] df ON fwc.FileSK = df.FileSK
WHERE fc.[ModifiedDate] > '2012-12-01 00:00:00'
AND df.ServerItem LIKE '$/YourProject/YourBranch/%'

Make sure to replace YourProject and YourBranch with the actual names you’re using. Also, be cautious with LIKE queries on larger datasets as they may slow down performance, so consider implementing indexes for frequent executions.

3

Been there with TFS warehouse queries. Branch data’s scattered across multiple tables, but here’s what actually works:

SELECT fc.[RevisionID]
      ,fc.[RevisionName]
      ,fc.[ModifiedDate] 
      ,fc.[CommittedBy__UserName]
FROM [TFS_Warehouse].[dbo].[vFactChangesetDetails] fc
JOIN [TFS_Warehouse].[dbo].[FactWorkItemChangeset] fwc ON fc.ChangesetSK = fwc.ChangesetSK
JOIN [TFS_Warehouse].[dbo].[vDimChangeset] vdc ON fc.ChangesetSK = vdc.ChangesetSK
WHERE fc.[ModifiedDate] > '2012-12-01 00:00:00'
AND vdc.BranchName = 'your-branch-name'

Use vDimChangeset instead of the regular dimension table - it exposes BranchName properly.

Honestly though, TFS warehouse schema is a nightmare. Had this exact issue at my last job and ended up automating everything.

Built a Latenode workflow that hits TFS APIs directly, pulls changeset data with branch filtering, and exports to whatever format you need. No more complex joins or warehouse documentation headaches.

Set it once, runs automatically. Way cleaner than maintaining SQL queries every time Microsoft breaks their warehouse structure.

4

I’ve hit this same TFS warehouse headache. Branch info isn’t in vFactChangesetDetails directly - you’ve got to join with the dimension tables.

Join with DimChangeset, then link to file paths through vFactWorkItemChangeset. Branch path’s usually hiding in the ServerItem column of the file dimension tables.

This pattern worked for me:

SELECT DISTINCT fc.[RevisionID]
      ,fc.[RevisionName] 
      ,fc.[ModifiedDate]
      ,fc.[CommittedBy__UserName]
FROM [TFS_Warehouse].[dbo].[vFactChangesetDetails] fc
JOIN [TFS_Warehouse].[dbo].[DimChangeset] dc ON fc.ChangesetSK = dc.ChangesetSK
JOIN [TFS_Warehouse].[dbo].[FactWorkItemChangeset] fwic ON dc.ChangesetSK = fwic.ChangesetSK
WHERE fc.[ModifiedDate] > '2012-12-01 00:00:00'
AND dc.Comment LIKE '%your-branch-name%'

But wrestling with TFS warehouse queries manually sucks. I automated this whole thing using Latenode instead. Built a workflow that pulls data through TFS APIs, filters by branch automatically, and dumps it wherever I need it.

No more digging through warehouse schema docs or complex joins. Just point it at your TFS instance and let it handle the branch filtering.

5

yeah, the warehouse structure is confusing as hell. i skip the joins and go straight through DimChangeset to grab the BranchPath field - much simpler. try JOIN DimChangeset dc ON fc.ChangesetSK = dc.ChangesetSK WHERE dc.BranchPath = '$/yourproject/branches/feature'. fair warning though - the warehouse docs are trash, so you’ll probably need to dig around the schema first.

6

The warehouse schema stores branch relationships through the vFactCodeChurn view, not the changeset details table you’re using. Connecting through the team project collection dimension gives way better branch visibility.

Try this instead:

SELECT c.[RevisionID]
      ,c.[RevisionName] 
      ,c.[ModifiedDate]
      ,c.[CommittedBy__UserName]
FROM [TFS_Warehouse].[dbo].[vFactChangesetDetails] c
INNER JOIN [TFS_Warehouse].[dbo].[vFactCodeChurn] cc ON c.ChangesetSK = cc.ChangesetSK
INNER JOIN [TFS_Warehouse].[dbo].[DimTeamProject] tp ON cc.TeamProjectSK = tp.TeamProjectSK
WHERE c.[ModifiedDate] > '2012-12-01 00:00:00'
AND cc.FileName LIKE '%/branches/YourBranchName/%'

vFactCodeChurn keeps file path context way better than changeset-to-file joins. Branch info comes through the FileName field. Plus it’s faster since CodeChurn is already pre-aggregated for reporting.