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 |
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 = ‘测试用例‘ 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.
1234567891011121314151617181920212223242526272829303132 |
CREATE
PROCEDURE
[dbo].[CalcTestSetpProg]
AS
BEGIN
SET
NOCOUNT
ON
;
--Delete illegal record
delete
[TeamProjectTestStep]
where
isnull
(system_id,0)=0
DECLARE
@
temp
TABLE
(
sys_id
INT
,
word ntext
)
INSERT
INTO
@
temp
(sys_id, word )
select
[System_Id],words
from
[dbo].[TeamProjectTestStep];
DECLARE
@sys_id
AS
INT
,
@word
As
nvarchar(
max
)
WHILE EXISTS(
SELECT
sys_id
FROM
@
temp
)
BEGIN
SET
ROWCOUNT 1
SELECT
@sys_id= sys_id,@word = word
FROM @
temp
;
BEGIN
Try
UPDATE
[TeamProjectTestStep]
SET
StepCount =
Cast
(@word
as
xml).value(
‘count(/steps/step)‘
,
‘int‘
)
WHERE [email protected]_id;
END
Try
Begin
Catch
End
Catch
SET
ROWCOUNT 0
DELETE
FROM
@
temp
WHERE
[email protected]_id;
END
END
|
3, use the report to show
TFS test Case Step data statistics