Error “The Work Item History cube either does not exist or has not been processed” when opening a custom report

Team Foundation Server process templates come with a collection of reports however sometimes you might choose to develop your own custom reports. Recently I have worked on an issue where rendering a custom report would yield the following error message:

The Work Item History cube either does not exist or has not been processed

Rather than just stating the problem description and the solution to the problem, I have decided to walk you through the troubleshooting process as it might come in handy when troubleshooting problems with Team Foundation Server reports.

In order to narrow down the problem, first we have looked at the built-in reports that come with Team Foundation Server. The built-in reports were rendering without any errors and the data from the reports was up to date which meant that the warehouse synchronization was working as expected.

I have gotten a copy of the report in order to test it in my test environment. After configuring the custom data source that the report was using I was able to get the report to render without any errors. The problem was narrowed down even more to the custom report running in a specific development environment.

I wanted to take a closer look at the queries used by the custom report so I have created a SQL Profiler trace while rendering the report. The simplified MDX query returning the data displayed in the report was:

 SELECT 
    NON EMPTY 
    { [Measures].[Cumulative Count] } ON COLUMNS, 
    NON EMPTY 
    { ([Date].[Year Month Date].[Month].ALLMEMBERS ) } ON ROWS 
FROM [Work Item History] 

I have only basic understanding of MDX queries but the first thing that I have noticed was that the query was written against the [Work Item History] perspective and not against the [Team System] cube. The MSDN documentation of the Work Item History perspective contains the following note:

In order to use perspectives with the Team Foundation cube, you must use Microsoft SQL Server 2005 Enterprise Edition or SQL Server 2005 Enterprise (64) Edition on the data tier. SQL Server 2005 Standard Edition that ships as part of Team Foundation Server, does not support the use of perspectives. When you use SQL Server 2005 Standard Edition, the cube elements from all perspectives reside in the Team System data cube.

As the documentation says, writing queries against perspectives works only with SQL Server Enterprise Edition. Using the information from this knowledge base article we were able to determine that the report was indeed running on SQL Server 2005 Standard Edition.

The documentation also states that the cube elements from all perspectives are included in the Team System cube we have modified the query used by the custom report to:

 SELECT 
    NON EMPTY 
    { [Measures].[Cumulative Count] } ON COLUMNS, 
    NON EMPTY 
    { ([Date].[Year Month Date].[Month].ALLMEMBERS ) } ON ROWS 
FROM [Team System] 

In case you choose to create custom reports please make sure that you write queries against the [Team System] cube. By doing that, the custom reports will work both with SQL Server Standard and Enterprise editions.