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.

1 2 3 4 5

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) =  ' <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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24 25 26 27 28 29 30 31 32

CREATEPROCEDURE[dbo].[CalcTestSetpProg] AS BEGIN SETNOCOUNT ON;   --Delete illegal record   delete[TeamProjectTestStep] whereisnull(system_id,0)=0 DECLARE@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] SET StepCount = Cast(@word as xml).value(‘count(/steps/step)‘,‘int‘)  WHERE[email protected]_id;                ENDTry                BeginCatch                EndCatch                  SETROWCOUNT 0                  DELETEFROM @temp WHERE[email protected]_id;         END END

　　

3, use the report to show

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/28076/201612/28076-20161230220910632-177065943.png "Style=" border:0px; "/>

TFS test Case Step data statistics