TFS test case step data statistics

TFS system integrates a set of BI system, which is realized based on Analysis Service of SQL Server. Through the in-depth use of this data warehouse model in recent years, we can feel how excellent this data warehouse model is, compared with the data warehouse example AdventureWorksDW provided by Microsoft. The whole process of the system itself from the basic data ETL to the data warehouse and then to the Cube is integrated, and it is almost transparent to the users and system administrators. And the customization extension of TFS work items is automatically supported, users can automatically add and statistics data in the final Cub by adding relevant dimensions or measurement metadata attributes of the work.

However, in the process of using it by a large customer, it is proposed to count the data of all test steps of all test cases in TFS. This test step is not designed to be measurable data in TFS system at all. It is obviously impossible to count this data directly from the data warehouse of TFS. Through analysis, the data of the type of HTMLFieldControl in TFS is stored in the WorkItemLongTexts table of the TFS_ Collection library, and the test steps are stored in the form of text organized by the XML structure. Therefore, in order to count this data, we must start from this point. After several attempts, we have finally counted the number of test steps for each project according to the following steps.

1, the cross-database query extracts all the test case related data into a temporary table.

one

two

three

four

five

Select cwv.ProjectNodeName,cwv.System_AssignedTo, cwv.System_CreatedDate, cwv.System_Id,cwv.System_Title, wilt.words

From WorkItemLongTexts wilt

Left join

[Tfs_Warehouse]. [dbo]. [CurrentWorkItemView] cwv on wilt.ID = cwv.System_Id and cwv.System_WorkItemType = 'test case'

Where wilt.fldid = 10181 and wilt.EndDate = '9999-01-01' and SUBSTRING (wilt.Words,1,6) ='