For a customer I am creating reports related to Test Cases. In order to retrieve the data, I use the Tfs_Warehouse database (relational warehouse). For several situations, Microsoft has conveniently created SQL Server Views on top of the tables available in the database. For Test Cases the View “vDimTestCaseOverlay” is available. In my case however, this View had some “problems”…
First of all, to have test data available, I started by creating a few Test Suites in Microsoft Test Manager and I added some new Test Cases to the Test Suites (in my nearly empty demo environment). Then I went to SQL Server Management Studio to execute my very simple query to check for data;
select top 10 * from vDimTestCaseOverlay
The query returned 2 rows (previously added Test Cases), but not the rows of the Test Cases I just added. I headed over to http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx?op=ProcessWarehouse (> replace “localhost” with the name of your TFS server) to process the Relational Warehouse (this normally runs every 2 minutes) and have up-to-date data. When I got back to SQL Server Management Studio and re-executed my query, there was no difference…
I checked the datasource of the vDimTestCaseOverlay View and found out it reads from the table _DimTestCaseOverlay. This table didn’t contain the Test Cases either.
That’s weird, because in my opinion, processing the Relational Warehouse should process all data from the operational Databases (e.g. Tfs_DefaultCollection) to Tfs_Warehouse… I then started a Full “ProcessAnalysisDatabase” job (http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx?op=ProcessAnalysisDatabase > replace “localhost” with the name of your TFS server) and voila, my SQL query returned all available Test Cases!
Conclusion is, that some data in the Tfs_Warehouse database is only processed/updated when running the Analysis Database sync (which, by default, runs every 2 hours).