TFS test Case Step data statistics

The TFS System integrates a suite of BI systems, implemented based on the SQL Server Analysis service. Through these years of deep use, can feel how this data warehouse model is excellent, and Microsoft's official Data Warehouse sample AdventureWorksDW a ratio. The system itself from the basic data ETL to the data warehouse into the cube, the entire process seamless, corresponding users and system administrators, almost transparent. and custom extensions for TFS Work items are automatically supported, and users can automatically join and statistic data by adding the relevant dimensions of the work or the properties of the metric metadata in the final cub.

However, in a large customer use process, the statistics of all the test cases in TFS to complete the test steps of the data, this test step in the TFS system is not designed to be a measurable data, directly from the Data warehouse in TFS statistics, it is obviously impossible. By analysis, data for the type of Htmlfieldcontrol in TFS is stored in the Workitemlongtexts table in the Tfs_collection library, and the test steps are stored as text in the XML structure organization. So to count this data also must start from this point, through a few attempts, follow the steps below to finally count the number of test steps for each project.

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

12345

selectcwv.ProjectNodeName,cwv.System_AssignedTo, cwv.System_CreatedDate, cwv.System_Id,cwv.System_Title,  wilt.words from WorkItemLongTexts wiltleft join[Tfs_Warehouse].[dbo].[CurrentWorkItemView] cwv on wilt.ID = cwv.System_Id and cwv.System_WorkItemType = ‘测试用例‘where wilt.fldid = 10181 and wilt.EndDate = ‘9999-01-01‘ andSUBSTRING(wilt.Words,1,6) = ‘<steps‘

　　

2, for the temporary table extracted above, created a stored procedure, the XML type of the test step data to parse, extract the step data.

1234567891011121314151617181920212223242526272829303132

CREATEPROCEDURE[dbo].[CalcTestSetpProg]ASBEGINSETNOCOUNT ON;  --Delete illegal record  delete[TeamProjectTestStep] whereisnull(system_id,0)=0DECLARE@tempTABLE(    sys_id INT,    word ntext)INSERTINTO@temp(sys_id, word ) select[System_Id],words from[dbo].[TeamProjectTestStep];DECLARE    @sys_id ASINT,    @word Asnvarchar(max)WHILE EXISTS(SELECTsys_id FROM@temp)        BEGIN               SETROWCOUNT 1               SELECT@sys_id= sys_id,@word = word FROM @temp;               BEGINTry                UPDATE[TeamProjectTestStep] SETStepCount = Cast(@word asxml).value(‘count(/steps/step)‘,‘int‘)  WHERE [email protected]_id;               ENDTry               BeginCatch               EndCatch                 SETROWCOUNT 0                 DELETEFROM@tempWHERE[email protected]_id;        ENDEND

　　

3, use the report to show

TFS test Case Step data statistics