Sunday, June 19, 2011

Processing Tfs_Analysis Database

I was trying to get TFS 2010 to work with SQL 2008 R2 Report Server. Because the order in which these servers were installed, the reporting service in TFS 2010 had to be fixed after it was migrated to a server computer from a test installation on a workstation.

I followed the suggestions at this link:

http://social.msdn.microsoft.com/Forums/en-US/tfsreporting/thread/1d566b21-3082-4aa8-bbc8-d02fb76791de/

I downloaded and uploaded necessary report definitions, I fixed the data sources, and I update security settings. When I tried to open a report, I got errors related to “dsIteration”. I opened a report definition in SQL Report Designer. When I tried to run a query on this dataset, I got a “no cube can be found” error.  

I had to get into “SQL Server Business Intelligence Development Studio” to open the “Tfs_Analysis” database. It is not visible from the “SQL Server Management Studio”. Although the “Tfs_AnalysisDataSource” tested fine, when I tried to process the database from the “Database” menu, I got an error:

“OLE DB error: OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Tfs_AnalysisDataSource', Name of 'Tfs_AnalysisDataSource'. “

I tried to fix the “Tfs_AnalysisDataSource” datasource. Everthing I tried was of no use. I updated the firewall for SQL Analysis Service. It uses port 2383 access. That was not enough. 

I found an article on the internet that was helpful:

http://blog.accentient.com/2010/04/08/ManuallyProcessingTheTeamFoundationServer2010DataWarehouseAndAnalysisServicesDatabase.aspx 

It taught me how to use a web service to build TFS report databases:

http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx?

I went through the steps and they did not solve the problems. I restarted the SQL Analysis Service. It turned out that the “OLE DB error” was caused by the SQL Analysis Service itself. After the restart I got a bunch of SQL errors while processing the database, but none was “network-related”. I went into TFS Management Console and rebuilt the warehouse database, and reprocessed the “Tfs_Analysis” database. The reprocessing worked.

After that, the reports were still not accessible. I restarted the SQL Analysis Service again, and then I went through the web service calls again.

Finally, the reports came up.