Useful information that isn't in the warehouse.
I'm trying to create a graph for a report that shows the inventory of a type of work item over time, among other statistics. The problem is that the TfsWarehouse database doesn't have System.DateCreated in it. When I tried to change the template so that System.DateCreated had an attribute of reportable="dimension", uploading the template resulted in the following error: "Actual reporting settings for the field System.CreatedDate are different from those specified in the XML. Changing these settings it prohibited."
Is there any way to get the TfsWarehouse database to include this and other system variables that it currently doesn't hold? If not, is there any easy way to do a true inventory of when items where created by date?
Each work item revision is in Work Item History. You can use the Date associated with the history items and select only the first revision (Revision=1).
We'll look into the issue with CreatedDate, though.
The created date will be present in the RTM version of the data warehouse. For many scenarios, the "Date" dimension in the OLAP cube will suffice. In particular, if you are using the "Cumulative Count" measure and the Date dimension, it will show you the total number of work items as they existed on each day.
For "System" fields, the reportable settings are fixed at deployment time, and cannot be changed.
One alternative you might consider is authoring a Work Item query that extracts the items you are looking to analyze, including their created date, and export these to excel using the excel integration feature. From there, you can build charts or graphs that show the counts of work items created in a particular time range.
The trade-offs between this approach, and using the data warehouse is whether you want to look at things "as they were at a point in time", or "as they are now". If the latter is your goal, because Created Date does not change over time, you can use a Work Item query.
In the RTM version, "Created Date" will be available for use with the "Current Work Items" measure group in the cube, and the information will be in the relational database for all versions of work items.
Hope that helps,
tom
