TFS reports blank and tfs_warehouse is empty.
I have been terrible trouble recently trying to get SQL Server Reporting working for TFS (Team Foundation Server). There are a lot of moving parts and if any aren't in sync, then it doesn't work.
My problems were on an on-prem TFS 2018 Update 2 platform, which has been upgraded over the years from goodness knows what version originally.
If you're using SQL Server 2017, then SQL Reporting Services is a standalone download. You will need SQL Analysis Services installed too.
There is official documentation on how to Add Reporting to TFS, which is required reading for the initial setup. If you're lucky it'll work out of the box. It is worth noting that it is perfectly safe to drop the Tfs_Warehouse and Tfs_Analysis databases as they will be recreated by this wizard and repopulated when processing (this can take a long time).
Analysis Services connection error
My first problem came around with being unable to process the SQL Analysis database/cube. The errors are vague, but the main thing you can pull out of Windows Event logs, or SSMS if attempting a manual process is something like this:
The fix I stumbled upon for this was to switch SQL Analysis Services from using "NT Service\MSSQLServerOLAPService" to using "NT AUTHORITY\LocalService" as the service account. This will no doubt be related to a local or domain security policy that is causing problems, I haven't been able to investigate this further, it did though stop the errors.
Adding standard reports
It could be that your reports are missing too. I'm pretty sure I managed to get this to apply collection wide, but the command here only appears to be a project at a time. If I remember what I did, I'll update this.
Missing report data
After getting my reports to show in SSRS web-page, and the errors to disappear from processing I had one more hurdle. All the reports were blank, some gave me useful errors such as:
The 'Warning' parameter is missing a value
Checking the tfs_warehouse database: SELECT count(*) FROM WorkItemSK showed there wasn't any data to report on.
It seems that whilst you can enable the reporting jobs through the TFS Administration Console, this doesn't fully enable the jobs required to get things to process, and to do this you have to call a web-service.
- Using IE on the local TFS computer, navigate to http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx, adjusting your hostname, port and path to your specific installation settings.
- Invoke GetJobProperties to view the EnabledState for all the sync jobs. Mine were "FullyDisabled"
- Invoke SetWarehouseJobEnabledState with state=Enabled to enable them all.
- Invoke ProcessWarehouse to manually kick things off
- Poll GetProcessingStatus with includeOnlineHostsOnly=true until the JobProcessingStatus="Idle" for the job "Common Structures Warehouse Sync".
- Invoke ProcessWarehouse with processingType=full
- Repeat step 5, but watch the job "Full Analysis Database Sync" to complete.
Hopefully, as it did for me, you'll now have data in your tfs_warehouse database and your reports will now load.