Fixing Service Manager Data Warehouse Errors

Q: I'm getting errors while running Service Manager reports--the DWDataMart for the report server is not valid. What can I do?

A: I recently saw this error in an environment. It was caused because the reports had been disconnected from the actual correct data source within SQL Reporting Services.

To verify that the data source is the problem, open the SQL Reporting Services website (e.g., http://localhost/Reports--assuming you are locally logged on to the SQL Reporting Services server) and navigate through the menus to one of the Service Manager reports.

From the drop-down menu, select the Manage option (if you don't get a drop-down menu and are using Internet Explorer 10, turn on the compatibility mode).

Select the Data Sources area, which should show that the reference is no longer valid. Click Browse and select a valid data source as the screen shot below shows.

The report might now work-- however, in my experience there are likely other problems as well. The most common cause I see is that a Service Manager Data Warehouse was configured against a SQL Reporting Services instance, then uninstalled, then was installed again using the same instance, which isn't supported and will not work.

If this is your situation and you uninstalled the data warehouse server, you will also need to uninstall SQL Reporting Services completely, then reinstall SQL Reporting Services, then install the data warehouse server again.