What is the DimDate table, and why would I want to, or need to populate it in TFS 2010? The DimDate table contains 1 row for every day from the date you installed TFS to 4 months into the future. TFS has a job that runs (Common Structures Warehouse Sync) that updates this table for you. However, if you need to get dates into this table that are more then 4 months into the future, you need to take some extra steps.

So when would you need dates more then 4 months into the future? The easiest way to explain it is with an example. If you want to set the end date on the burn down chart to say 12 months out, because you have so much work that you want to see when you will be finished, then you need more dates in the DimDate table. Why didn't Microsoft just put in 12 months? I think it has something to do with the fact that if you are trying to look more then 4 months out, you're probably not using the Agile process correctly. Attempting to estimate more then 4 months (or around there) into the future kind of goes against some of the core beliefs of the agile process. You should be looking to deliver a smaller feature set. However, I have now gotten way off topic, back to the problem at hand.

Populating DimDate

0. Current state

• Open up the TFS_Warehouse.dbo.DimDate table to see what you currently have. This also allows you to quickly check to see if the dates actually get populated by looking at the number of rows. If you are using the default select 1000 rows, and you have more then 1000 rows, you'll need to adjust the query.

• Open up the TFS_Warehouse.dbo._LastUpdatedTime table. This shows you the jobs that can be invoked from the ProcessWarehouse operation. The one we are interested in is the “Common Structures Warehouse Sync”, which should have a collection name of TEAM FOUNDATION. Make a note of the WarehouseLastUpdated date/time. You will want to verify that this value changes after you run the ProcessWarehouse command.

1. Set the end date for the dimDate Table

• Open the Tfs_Warehouse.dbo._PropertyBag table and locate the /Adapter/Config/CommonStructureService/DimDate/EndDenseDate  property keys.
• Modify the Property_Value to be the last date you want in the DimDate table. In my instance, I'm looking to populate thru the end of 2011. NOTE: You must enter the date/time in a very specific format. To populate thru the end of the year, I needed to specify 12/31/2011 as “2011-12-31T00:00:00.000” without the “ “. Failure to add the FULL time part will result in this not working (see my explanation all the way at the bottom of this post).
• When the ProcessWarehouse command is run for the Common Structures, this value will be removed after processing.  

2. Run the ProcessWarehouse Command

• You can manually invoke the ProcessWarehouse command by opening a web browser and accessing the following URL on the TFS machine: http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx?op=ProcessWarehouse.

• Enter Team Foundation for the collection name, and the jobName is Common Structures Warehouse Sync  

• Press invoke, and you should get another browser window that pops up with a SOAP response that indicates the process was started.

3. Verify Results

• The WarehouseLastUpdate should have been updated for the Common Structures Warehouse Sync. You should also see all of your new dates in the DimDate table, and finally, the value(s) you set in the PropertyBag table will have been cleared out.

More Details

As with most stuff I find worth blogging about, the journey is more fun then the destination. I could not get the DimDate table to populate following the above instructions with the exception of one little detail, I was missing the fractional seconds component of my DateTime value. Here are the steps I went thru to figure out what was going on.

Query Analyzer

I started out using Query Analyzer to see what was going on at the database level. The Warehouse Process gets the value from the PropertyBag using the following Sql command:

Declare @p4 nvarchar(max)
set @p4=N'2011-12-01T00:00:00′
exec sp_executesql N'EXEC dbo.prc_PropertyBag_Get @Property_Key=@Property_Key, @Property_Value=@Property_Value OUTPUT
',N'@Property_Key nvarchar(59),@Property_Value nvarchar(max)  output',@Property_Key=N'/Adapter/Config/CommonStructureService/DimDate/EndDenseDate',@Property_Value=@p4 output
select @p4

Then it executes the prc_Dimension_DimDate_AddUpdate stored procedure. As you can see it clears the values in the PropertyBag table first and then invokes the stored procedure itself, passing in an empty XML fragment that “configures” the stored procedure. So I needed to figure out why that XML fragment was empty.

declare @p7 xml
set @p7=convert(xml,N'<ds/>')
exec sp_executesql N'EXEC dbo.prc_PropertyBag_Set @Property_Key=@P0, @Property_Value=@P1
EXEC dbo.prc_PropertyBag_Set @Property_Key=@P2, @Property_Value=@P3
EXEC [dbo].[prc_Dimension_DimDate_AddUpdate] @dataset=@P4, @update=@P5
',N'@P0 nvarchar(61),@P1 nvarchar(4000),@P2 nvarchar(59),@P3 nvarchar(4000),@P4 xml,@P5 bit',@P0=N'/Adapter/Config/CommonStructureService/DimDate/StartDenseDate',@P1=N”,@P2=N'/Adapter/Config/CommonStructureService/DimDate/EndDenseDate',@P3=N”,@P4=@p7,@P5=1

Code Splunking

I decided to enlist the help of reflector in order to take a look at the Process Warehouse code. I found out that the Process Warehouse web method simply queues a job. The TfsJobAgent which runs as the Visual Studio Team Foundation Background Job Agent is the process that executes the job. I had to go splunking around in that folder to finally find the code that is responsible for updating the DimDate Table. It is the UpdateTimeDimension method in Microsoft.TeamFoundation.Adapter.dll which is at C:Program FilesMicrosoft Team Foundation Server 2010Application TierTFSJobAgentplugins.

After looking at the code, I finally discovered my problem was more of a typo then anything. The UpdateTimeDimension method calls TryGetDateProperty which is responsible for getting the value we set in the property bag. If the value is not in the exact format expected by this method (yyyy-MM-ddTHH:mm:ss.fff) it will not work. Initially I was leaving off the fractional seconds!