Tfs custom work item value migration

Question

I have a task to create reports about various work items from a Team Foundation Server 2010 instance. They are looking for more information than the query tools seem to expose which is why I am not using the OOB reporting capabilities. The documentation on creating custom reports against TFS identify the Tfs_Analysis cube and the Tfs_Warehouse database as the intended sources for reporting.

They have created a custom work item, "Deployment Requests", to track requests for code migrations. This work item has custom urgency levels (critical, medium, low).

According to Manually Process the Data Warehouse and Analysis Services Cube for Team Foundation Server, every two minutes my ODS (Tfs_DefaultCollection) should sync with the Tfs_Warehouse and every 2 hours it hits the Tfs_Analysis cube. The basic work items correctly show up in my Tfs_Warehouse except not all of the data makes it over, in particular, the urgency isn't getting migrated.

As a concrete example, work item 19301 was a deployment request. This is what they can see using the native query tool from the web front-end.

I can find it in the Tfs_DefaultCollection and the "Urgency" is mapped to Fld10176.

SELECT 
    Fld10176 AS Urgency
, * 
FROM Tfs_DefaultCollection.dbo.WorkItemsAre 
WHERE ID = 19301

trimmed results...

Urgency                       Not A Field   Changed Date 
1 - Critical - (Right Away)   58            2011-09-07 15:52:29.613

If I query the warehouse, I see the deployment request and the "standard" data (people, time, area, etc)

SELECT
    DWI.System_WorkItemType
,   DWI.Microsoft_VSTS_Common_Priority
,   DWI.Microsoft_VSTS_Common_Severity
,   *
FROM
    Tfw_Warehouse.dbo.DimWorkItem DWI
WHERE
    DWI.System_Id = 19301     

Trimmed results

System_WorkItemType   Microsoft_VSTS_Common_Priority   Microsoft_VSTS_Common_Severity
Deployment Request    NULL                             NULL

I am not the TFS admin (first exposure to TFS is at this new gig) and thus far, they've been rather ...unhelpful.

• Is there be a way to map that custom field over to an existing field in the Tfs_Warehouse? (Backfilling legacy values would be great but fixing current/future is all I need)
• Is there a different approach I should be using?

Answer 1

Did you mark the field as reportable? See http://msdn.microsoft.com/en-us/library/ee921481.aspx for more information about this topic.

Answer 2

Based on Ewald Hofman's link, I ran

C:\Program Files\Microsoft Visual Studio 10.0\VC>witadmin listfields /collection:http://SomeServer/tfs > \tmp\witadmin.txt

and discovered a host of things not configured

Reportable As: None

At this point, I punted the ticket to the TFS admins and indicated they needed to fix things. In particular, examine these two fields

  Field: Application.Changes
  Name: ApplicationChanges
  Type: PlainText
  Use: Project1, Project2
  Indexed: False
  Reportable As: None

or

  Field: Microsoft.VSTS.Common.ApplicationChanges
  Name: Application Changes
  Type: Html
  Use: Project1, Project2
  Indexed: False
  Reportable As: None

It will be a while before the TFS Admins do anything but I'm happy to accept Edwald's answer.