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.
I am not really sure where to start. I am a Team Foundation Server Escalation Engineer based in Budapest, Hungary. I will not bore you with details about what an Escalation Engineer does but if you really want to know you can read one of my colleague’s post.
Apart from helping enterprise customers with Team Foundation Server issues, I am part of the team developing the Team Foundation Server Best Practices Analyzer which ships as a part of the Team Foundation Server Power Tools. Please feel free to share any feedback that you have regarding the Best Practices Analyzer or the Power Tools.
In my spare time I am also contributing to the Team Foundation Server Administration Tool. I am very excited about this project as it has gotten some real traction now that it is community driven. In case you are using the tool, and would like to request a new feature, please let me know.
I have started blogging couple of weeks ago and I was posting on the Developer Support Team Foundation Server blog but I have a long list of ideas that I want to blog about so I decided to create my own playground. In this blog I will try to share with you some of the interesting issues that I have seen in Team Foundation Server. I hope you will enjoy the ride.
