TechPrufer
Get started
Microsoft
Microsoft logoMicrosoft · Feature trail

Team Foundation Data Warehouse Architecture

17 posts · Jan 2006 – Jun 2022

What this trail covers

This thread tracks the evolution of Team Foundation Server (TFS) and Azure DevOps data warehousing and reporting capabilities. It includes tools and techniques for analyzing database sizes, test attachment sizes, and source control folder sizes to aid administrators in managing their TFS environments. The TFS Administrators Toolkit is a key component, offering features to clean projects, update reports, and manage work item types. This post details the architecture of the data warehouse, including its relational and OLAP components, and the synchronization process for data updates.

All Microsoft trails
  1. Azure DevOps Server 2022 Deprecation of Reporting ServicesThis post announces the deprecation of the existing data warehouse reporting services in Azure DevOps Server 2022, reiterating a previous announcement from Azure DevOps Server 2020. It highlights the introduction and ongoing improvements of the Analytics Service, which began in Azure DevOps Server 2019.1 and offers enhanced reporting capabilities. The post mandates the disabling of the old reporting services during the upgrade to Server 2022. It guides users on how to re-create existing reports using the Analytics Service features like dashboard widgets, Analytics views, and OData queries, emphasizing that existing reports are not automatically migrated.
  2. TF221122: An error occurred running job Work Item Tracking Warehouse Sync for team project collectionThis post details a specific error (TF221122) encountered in Azure DevOps Server and TFS related to the Work Item Tracking Warehouse Sync job, caused by missing historic data. It provides direct links to download and install specific patches (Azure DevOps Server 2019.1.1 Patch 1 and TFS 2018 Update 3.2 Patch 9) that resolve this issue. Verification methods for the patch installations are also included.
  3. August patches for Azure DevOps Server and Team Foundation ServerThis post provides specific patch versions for Azure DevOps Server 2019.0.1, TFS 2018 Update 3.2, and TFS 2017 Update 3.1 to fix the 'TF221122: Cannot create compensating record. Missing historic data' error in the Work Item Tracking Warehouse Sync job. It also details how to verify the installation of these patches by checking specific DLL versions.
  4. Team Foundation Server 2018 Update 2 is now availableThis post announces the release of Team Foundation Server 2018 Update 2. Key contributions include the re-enabling of legacy XAML builds (while noting their deprecated status and providing migration guidance), and the incorporation of features previously released for Visual Studio Team Services, such as pull request labels, mentions, and thread context in notifications; new work item query macros (@MyRecentActivity, @RecentMentions) and the 'Not In' operator; enhancements to multi-phase builds (different agent queues per phase, parallel tests, OAuth token access, conditional phases); release gates for health checks; package feed retention policies and a new filter bar; and improvements to wiki search, work item referencing, rich text pasting, and preview functionality.
  5. Power BI and VSTS - Integration made easyIntroduces 'Analytics Views' as a new feature in VSTS to simplify the extraction of work item tracking data for Power BI. This feature allows users to create custom views with filters for work items, columns, and historical revisions, eliminating the need for direct OData queries. It also provides default views and guidance on creating custom views and using them with the VSTS Power BI Data Connector.
  7. Power BI and VSTS AnalyticsThis post announces the first step in improving the integration between VSTS Analytics and Power BI by updating the VSTS Power BI Desktop Connector. This update provides additional options for historical trend and bug analysis. The underlying architecture for pulling data from VSTS Analytics has been reengineered. Future enhancements will include the ability for users to create their own custom views with filtering capabilities.
  8. VSTS Analytics OData now publicly availableThis post announces the public preview of the Visual Studio Team Services (VSTS) Analytics OData feed, built on the VSTS Analytics reporting platform. It provides the V1.0 endpoint for accessing Work Item data and encourages users to update their code from the private preview endpoint. Documentation for getting started with OData and creating Analytics Widgets is also published. The next step is to provide Power BI connectivity.
  9. Test result storage improvements and impact on upgrading to Team Foundation Server 2017Introduces a new, more compact, and efficient schema for storing test results in TFS 2017, leading to a 5x-8x reduction in storage footprint for test result data. This change necessitates a data migration during the upgrade process, potentially increasing upgrade times. Provides guidelines for administrators to reduce upgrade window duration, including configuring test retention policies and using the TFSConfig.exe tool for pre-upgrade cleanup. For VSTS, the migration is transparent and phased.
  10. A new utility for upgrading reports when you upgrade TFSIntroduces the TFS Reporting Bulk Update tool, a Codeplex utility that automates the process of upgrading deployed reports for existing TFS projects when migrating to newer versions (specifically TFS 2013 CU3 and beyond). This tool generates and executes `tfpt addprojectreport` commands for multiple projects, addressing the manual effort required for upgrading reports across tens or hundreds of projects. It notes a limitation for projects upgraded from TFS 2010 where metadata for determining the correct process template is unavailable, but suggests workarounds.
  11. Querying Lab Environments Using TFS Lab Management APIsThis post provides a C# code example demonstrating how to query TFS Lab Environments using the `Microsoft.TeamFoundation.Lab.Client` API. It shows how to connect to a TFS server, obtain a `LabService` instance, define `LabEnvironmentQuerySpec` to filter environments by project, execute the query, and then iterate through the `LabEnvironment` and `LabSystem` objects to retrieve computer names.
  12. TFS Lab Management with multiple SCVMM serversThis post clarifies that TFS Lab Management can integrate with multiple SCVMM servers, one for each Team Project Collection, using the TfsLabConfig command tool. It emphasizes the critical requirement of configuring unique MAC address ranges for each SCVMM server to prevent conflicts and recommends separate SCVMM host groups and library shares per collection. It also notes that SCVMM is only relevant for Hyper-V environments.
  13. Announcing Team Foundation Server Administration ToolThis post announces the update of the TFS Administration Tool to version 2.3, adding support for Team Foundation Server 2013 and its object model. It also introduces support for SharePoint groups/roles within the tool.
  14. Creating Fence Environments using Team Foundation Server 2013 Preview Lab Management can put Lab Environment in “Not Ready” stateThis post details a specific bug in Team Foundation Server 2013 Preview Lab Management where a 'Not Ready' state occurs when the guest OS is Windows 2012 R2 Preview. The cause is identified as a firewall exception mismatch on the test agent (public profile instead of private/current), preventing the test controller from reaching the agent. A workaround is provided: installing agents for the lab environment once.
  15. Creating Fence Environments using team foundation server 2013 Preview Lab Management can give error “The given key is not present in the dictionary..”This post addresses a known bug in Team Foundation Server 2013 Preview Lab Management and SCVMM 2012 R2 Preview where network-isolated environments fail to create due to an SCVMM error ('The given key is not present in the dictionary..'). A workaround is provided: repairing the Lab Environment after creation, which bypasses the network re-creation step. The cause is identified as SCVMM returning a failure to Lab Management despite successful network creation.
  16. ASP.NET and Web Tools 2012 RC, TFS 2012 Monitoring Management Pack AND SQL Server Data Tools December 2012 release now availableThis post announces the release of the Visual Studio 2012 Team Foundation Server Monitoring Management Pack, which provides proactive and reactive monitoring of TFS 2012 components like application tier server instances, team project collections, build servers, and proxy servers. It also announces the Release Candidate for ASP.NET and Web Tools 2012.2, which includes enhancements to web publishing, new Web API functionality (OData, tracing, help page generation), new MVC templates (Facebook, Single Page Application), SignalR for real-time communication, and extensionless Web Forms via ASP.NET Friendly URLs. Finally, it announces the December 2012 release of SQL Server Data Tools (SSDT), which integrates previous Power Tools functionality, adds support for database unit testing (client-side testing based on MSTest, integration with SSDT SQL Server database projects, command-line execution), and includes enhanced SQL Server Object Explorer features (Projects node, Script As, DAC actions).
  17. Test Attachment, Version Control and Database sizes in Team Foundation serverThis post extends the TFS Administrators Toolkit by adding functionality to query and report on test attachment sizes (including type and extension), source control folder sizes, and to search for large files in source control. It also mentions the existing capabilities of cleaning projects from the UI and updating reports and work item types.
  18. Microsoft Internal Usage of TFS: January - June 2012This post provides detailed internal usage statistics for Team Foundation Server (TFS) from January to June 2012. It quantifies growth in instances, unique users, team project collections, work items, source code files, builds per month, and test cases. It also lists the top 10 TFS instances by various metrics and provides detailed usage data for each instance in June 2012. The data highlights consistent growth in most areas, with a noted decrease in team projects attributed to cleanup efforts.
  19. Team Foundation Server on Windows Azure: A Rangers dog fooding perspectiveThis post details the Visual Studio ALM Rangers' experience 'dog-fooding' Team Foundation Server on Windows Azure. Key highlights include: zero maintenance and administration effort for the Rangers, a productive drag-and-drop backlog maintenance experience in the web client, and real-time burn-down charts and visual status updates for project health. The post also provides adoption statistics (24 team projects, 120 members) and links to related blog posts for further exploration.
  20. Want to better understand relationships between Team Foundation Server work items?Introduced the Work Item Visualizer tool, available on MSDN Code Gallery, for Visual Studio 2010 Ultimate. This tool generates dependency (DGML) graphs to visualize links between work items, aiding in hierarchy analysis, risk/impact assessment, project planning, and understanding the scope of linked work items.
  21. Enabling new Application Lifecycle Management Features for Visual Studio 2010 in Upgraded Team ProjectsThis post details the manual steps required to enable new Application Lifecycle Management (ALM) features in Visual Studio 2010 for team projects that have been upgraded from earlier versions of Visual Studio Team System (VSTS) to Team Foundation Server (TFS) 2010. It covers enabling Test Management features, the Agile Planning Workbooks (iteration backlog and product backlog), Agile or CMMI Dashboards and Reports using `tfpt addprojectportal` and `tfpt addprojectreports`, and updating existing reports to adapt to schema changes in the cube and warehouse database. It also mentions a script provided by Hakan Eskici for enabling these features, especially for MSF for Agile Software Development v4.2.
  22. VS ALM Library Updates on MSDN for October 2010This post details updates to the Visual Studio ALM Library on MSDN for October 2010. It includes new content on "Getting Started With ALM" (software requirements for ALM features), "Installing Team Foundation Server" (tips on upgrading TFS and TFS support for SQL Server clusters), "Team Foundation SDK" (updates to the server object model, reference material, and removal of less valuable content), "Testing the Application" (guidance for build, deploy, and test workflows, and creating hyperlinks to view test results from email), and "Lab Management" (configuring and administering lab management, expanding or changing virtual labs, clarifying classes in lab activities, and creating virtual environments with operating system requirements for test agents).
  23. VSALM Library Updates on MSDN for September 2010This post announces updates to the Visual Studio Application Lifecycle Management (VSALM) documentation for September 2010. Key additions include the complete guide to the Best Practices Analyzer Tool (BPA) for Team Foundation Server, which diagnoses configuration and state issues. Additionally, VB samples have been added to over 150 Code Analysis for Managed Code Warnings reference topics. The post also details updates to modeling, developing, building, testing, and administering applications within the TFS ecosystem, including new walkthroughs for Coded UI tests, guidance on test plans, and integrated lab backup/restore content.
  24. Announcing Team Foundation Power Tools (September 2007)Introduced the Best Practices Analyzer Tool for Team Foundation Server, which scans server and client configurations to verify prerequisites, recommended practices, and system requirements. It gathers information from various sources (WMI, system settings, registry, IIS, SQL Server) and analyzes it against XML-defined rules to detect and report issues with links to resolution guidance. Also introduced Work Item Templates to pre-fill fields in work items, reducing manual effort for tasks like setting area paths, iteration paths, and disciplines.
  25. How to find your Analysis Services serverThis post provides a C# code snippet to programmatically retrieve the Analysis Services server name for Team Foundation Server (TFS) when it's used for custom reporting. It demonstrates how to connect to a TFS instance, retrieve registration entries for the 'vstfs' tool ID, and extract the SQLServerName associated with the 'BISANALYSIS DB'. This is useful for developers needing to connect to the Team System cube for custom reports, especially when Analysis Services is deployed separately from the data tier server.
  26. Announcing Team Foundation Power Tools v1.2Introduced Team Foundation Power Tools v1.2, including the Workspace Command (tfpt.exe) for additional workspace operations, Treeclean Command for managing unversioned files, Process Template Editor for authoring work item types, Check-in Policy Pack with custom policies like Custom Path Policy, and a Build Test Tools Task for running unit tests via MSBuild without .vsmdi files. Also includes bug fixes, Vista support, and Vista support.
  27. New Draft Documentation: Mirroring the Team Foundation Data-Tier ServerThis post introduces four draft documentation topics related to mirroring the Team Foundation data-tier server. The topics cover the general concept of mirroring, configuring SQL Server mirroring, and procedures for failing over to a mirrored data-tier server, including scenarios where the principal server is unavailable.
  28. DRAFT DOCUMENTATION: How to: Fail Over to a Mirrored Data-Tier ServerThis post details the process of failing over to a mirrored data-tier server for Team Foundation Server. It outlines the steps involved in setting up a mirrored data-tier server, configuring SQL Server mirroring, and performing the failover process. The documentation also covers considerations for ensuring data consistency and minimizing downtime during the failover.
  29. DRAFT DOCUMENTATION: How to: Configure SQL Server Mirroring for the Team Foundation Data-Tier ServerThis post details the configuration of SQL Server Mirroring for the Team Foundation Data-Tier Server. It provides documentation on setting up SQL Server Mirroring to ensure high availability and disaster recovery for the TFS data warehouse, including steps for configuring the primary and mirror servers and managing the failover process.
  30. How to: Move the Data Warehouse SQL Server Analysis Services Database to a Separate ServerThis post details the procedure for migrating the SQL Server Analysis Services (SSAS) database of the Team Foundation Server (TFS) data warehouse to a separate server. This involves stopping the TFSServerScheduler service, granting the new SSAS server access to the warehouse relational database, updating the TFSIntegration database with the new SSAS server name, rebuilding the SSAS database using setupwarehouse.exe, restarting the TFS data warehouse application pool, processing the data warehouse cube, updating the SQL Server Reporting Services data source to point to the new SSAS server, and finally restarting the TFSServerScheduler service.
  31. How to: Move the Data Warehouse SQL Server Analysis Services Database to a Separate ServerThis post provides instructions for moving the Team Foundation Server (TFS) cube database (SQL Server Analysis Services) to a separate server. This is a system administration task aimed at improving the performance and manageability of the TFS data warehouse.
  32. Upgrading from Team Foundation Server Release Candidate (RC) to the Release to Manufacturing Version (RTM) of Team Foundation ServerThis post details the specific steps and considerations for upgrading Team Foundation Server Release Candidate (RC) to the Release to Manufacturing (RTM) version. It outlines changes between RC and RTM, including modifications to the Date dimension in the warehouse and the need to re-save customized process templates. It provides detailed upgrade procedures for both single-server and dual-server deployments, including downloading upgrade software, running the TFSUpgradeRTM tool, backing up databases, uninstalling RC components, installing RTM, updating the reporting warehouse, and installing Team Explorer. It also covers updating Team Foundation Build Servers and Team Foundation Server Proxy, and includes verification steps for the upgrade process.
  33. More About the Data WarehouseThis post provides additional information about the Team Foundation Data Warehouse, building on a previous glimpse. It highlights the role of the Team System data warehouse architecture in team project builds and offers insights into troubleshooting common problems encountered during its deployment.
  34. Troubleshooting the Data WarehouseThis post details troubleshooting steps for data flow issues from the operational store to the data warehouse, including identifying failed adapters by enabling debug messages and tracing system events. It also addresses outdated report data by troubleshooting the automatic updating of the data warehouse, confirming the TFSServerScheduler service, and manually processing the warehouse. Procedures include checking data in SQL Server Management Studio, enabling debug messages in Web.Config, and manually running the warehouse controller service.
  35. Understanding the Data Warehouse ArchitectureThis post details the architecture of the Team Foundation data warehouse, explaining its components: operational stores (for daily operations), warehouse adapters (for data extraction, transformation, and loading), the warehouse relational database (organized in a star schema with fact and dimension tables), and the warehouse OLAP cube (for trend analysis and aggregated reporting). It also outlines report design using Report Designer and Excel, and discusses security at the database and team project levels.
  36. More about SQL and Team Foundation ServerThis post addresses Frank's questions about SQL for TFSers, specifically regarding backup, restore, data storage location, and general best practices. It explains that TFS uses SQL Server 2005 for data storage and SQL Server Analysis Services for data aggregation and reporting, with reports delivered via SQL Server 2005 Reporting Services. The post provides links to documentation covering the TFS data warehouse, planning for current needs, backup and restore planning, performing a restore, moving project data, and disaster recovery planning.
  37. Moving a Team Project from One Version to the NextThis post details the manual process of migrating a Team Project from one version to the next in Team Foundation Server. It outlines steps for creating a new team project, manually copying work items, setting user permissions one-by-one, copying documents, and recreating areas, iterations, check-in policies, and alerts. It emphasizes that TFS does not support bulk copying or moving of these items across projects, highlighting the manual effort required for such migrations.
  38. Team Foundation Server Data WarehouseThis post details the architecture of the Team Foundation Server data warehouse, describing its star schema organization, the separation of real-time data from reporting data via OLAP databases, and the structure of facts (details, dimensions, measures). It also outlines the synchronization process, including the default hourly update schedule for the data warehouse and the control over OLAP database updates.