Row Labels are blank in PivotTable from Analysis Services

Question

I have Team Foundation Server 2010 and SQL Server 2008 SP2 installation and am looking at the out-of-the-box Excel Reports for an Agile project. All of the Excel Reports connect to the TFS OLAP cube successfully and show the correct data for our 2+ years of bugs, work items, tests etc.

Unfortunately the row labels in the PivotTable and Chart (x-axis) are always blank for the Date fields (e.g. "Last 4 weeks") making the chart difficult to interpret. The number of rows look correct, and even the drill down seems to work although that's difficult to judge without labels.

I've looked at the dbo.DimDate table in TFS Warehouse and it does indeed have a row for each day, but all the other columns are NULL (apart from DateSK, DateTime and LastUpdatedDateTime) i.e. YearString, MonthString etc. are all NULL. Is that normal?

Answer 1

The first thing I would try is to re-build the cube; You can do this from the Reports tab within the TFS Administration Console on the TFS server desktop. All of the data in the Tfs_analysis cube comes from the data you put into other databases, so you don't need to worry about this rebuild affecting the availability of your TFS 2010 Server. If data in your TFS Reporting databases is corrupted this should correct it. There are different levels of rebuilding the cube, but I would try that easy one first. If you want to get more aggresive about it, then you can use the following blog post for reference: http://blogs.msdn.com/b/dau-blog/archive/2011/11/24/tfs-2010-warehouse-amp-reporting-trouble-shooting-basics.aspx.

Watch out because if you have a lot of data in your server, a rebuild can take hours to finish and reporting will not be available during that time.

Before you rebuild you might also check that your TFS Server has been patched with TFS Service Pack 1; there were also some updates that were released after TFS SP1 that can be installed together names TFS SP1 Cumulative Update 2. These updates are freely available on the Microsoft site.

Answer 2

So the NULL cells in Tfs_Warehouse dbo.DimDate were a big clue. Reading this article it's clear that this table is populated by the Common Structures Warehouse Sync. For some reason, this wasn't running despite doing a full rebuild of the Warehouse and Analysis Services in to new databases.

Looking at http://server:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx and running the GetProcessingStatus method, the Common Structures Warehouse Sync job was showing up as Result=Disabled, even after the full rebuild. Unlike the Warehouse and Analysis Sync, there's not an obvious method to force this to run, so I had to use the "SetWarehouseJobEnabledState" method, entering

collectionName: <leave blank>
jobName: Common Structures Warehouse Sync
state: Enabled

This should return "true" when invoked. Checking back on GetProcessingStatus should show that the job is now queued. When time ticks round to that point it should run, and dbo.DimDate (among others) will be fully populated. Et voila - row labels will appear on reports after you've rebuilt the Analysis cube. (Run the ProcessAnalysisDatabase method, processingType: "Full").

I have no idea why this job would be disabled even after re-creating the reports databases and jobs from scratch using the TFS Administration Console. Maybe there is an error on first run in my environment? It's also a bit confusing that you can force the Warehouse and Analysis builds through their own web method in the WarehouseControlService, but there's no such force for the Common Structures Warehouse Sync.