????TFS???????????BI????????SQL Server??Analysis Service?????????????????????????????д??????????????????????????????????????????????AdventureWorksDW?е???????????????????ETL???????????Cube?У?????????????壬???????????????????????????????????????TFS?????????????????????????????????????????????????????????????Cub???????????????????????
??????????????????????ù????У?????????TFS????????????????????????????????????????????TFS???и???????????????????????????TFS?????????????????????????????????????????TFS?е?HTMLFieldControl?????????????洢??TFS_Collection???WorkItemLongTexts???У????????????XML??????????????洢????????????????????????????????????????????γ??????????????????????????????????????????????
????1?? ??????????????????????????????????????С?
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?? ???????????????????????????????洢???????????XML?????????????????н???????????????????
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 System_Id=@sys_id;
END Try
Begin Catch
End Catch
SET ROWCOUNT 0
DELETE FROM @temp WHERE sys_id=@sys_id;
END
END
????3?? ???????????