Our main report model is built on top of our Analysis Services cube. End users are using Report Builder to query the cube in an ad-hoc fashion. It works great most of the time, but sometimes a user creates a very expensive report which generates a very expensive MDX query. From my testing, running the report then trying to kill it by clicking the stop button in Report Builder does not actually stop the query. Analysis Services continues processing the query until it finishes (which could be 30 minutes and could bring down the server).
Bug 1: The stop button in Report Builder doesn't actually kill the query and close the connection to SSAS.
Bug 2: There is no way I can tell to set a timeout so that if the underlying query does not complete within 2 minutes, the query is then cancelled. Below is a list of what I've tried without success:
A. Changing the data source in Report Manager which the Report Model runs off of. When I edit the properties on an Analysis Services datasource in Report Designer I don't see any properties which would be a timeout listed in the advanced properties. When I guess at the connection string setting name, it doesn't help:
Data Source=(local);Initial Catalog=MyDatabase;Timeout=60
B. Changing the "Limit report execution to the following number of seconds" at /Reports/Pages/Settings.aspx in Report Manager to 60 does not appear to have an effect on Report Builder reports.
If you want to try and reproduce against a report model generated on top of the Adventure Works sample cubes, here's how to build a report that will generate a runaway query:
1. Double click the Product field inside the Product entity
2. Double click the Customer field inside the Contacts folder of the Customer Entity. (You have to get to Customer through the Internet Sales Order Details entity.)
3. Single left click on the Internet Sales Order Details entity.
4. Click the "New Field" button.
5. Change the field name to "Test"
6. Double click Internet Sales Amount in the fields list on the left.
7. Double click Internet Sales Amount in the formula area.
8. Left click "No filter applied" below the formula area and select "Create new filter"
9. Double click the variation of Date with the calendar icon under the Date entity.
10. Click the modifier next to "Date Date (Date)" which will say either "equals" or "in this list" and change it to Relative Dates... Last (n)... Years. Then type 20 in the box that appears.
11. Click OK twice.
12. Double click the new calculated field called Test so it is put in the body of your report.
13. Click the filter button at the top.
14. In the filter dialog, double click the Test calculated field, then change "equals" to "greater than" and type 0 in the box. Click OK.
13. Run the report and watch the runaway query. After 10 seconds, click the stop button in Report Builder. Watch as the query continues to run, and the msmdsrv.exe process continues to use CPU and memory up.
I've attached this RDL file.
I'm running SP1 + the select all hotfix.
(By the way, I haven't spent as much time on it, but I believe a Report Builder report on top of a relational report model shows the same symptoms in terms of the stop button not actually killing the query. I haven't tried setting timeouts on the SQL connection strings.)