TFS test Case Step data statistics

Source: Internet
Author: User
Tags rowcount

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.