SQL Server Home
Report Builder creates runaway query against SSAS
10/27/2006 3:36:56 PM
User(s) can reproduce this bug
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.)
SQL Server 2005 SP1 - Enterprise Edition
Win2003 Standard Server (SP1)
Operating System Language
Steps to Reproduce
to post a comment.
Please enter a comment.
on 11/14/2011 at 9:47 AM
I can say that this still persists in Report Builder 2.0 latest build.
Any workaround? Can it be some kind of permissions?
on 11/4/2007 at 12:52 AM
I believe that your assumption is correct - stopping a report builder report only stops it once the data is returned for the query. We have a work item to improve this behavior in a future release. If you need more investigation, opening an incident with our CSS team is probably the best way to validate your specific situation.
on 1/3/2007 at 9:04 PM
sorry to reopen this... I'm hoping to get an answer regarding my previous questions I posted on 11/2/2006 at 4:54 PM
I would like very much to help you reproduce the problem of the report execution timeout not working on Report Builder reports.
on 11/2/2006 at 4:54 PM
Are you able to open that RDL file in Report Builder? (You'll have to edit the RDL file in notepad and update it to reflect the correct GUID for your copy of the report model on top of the sample Adventure Works cubes.) What happens on your system when you run it? Does it continue on for a very long time and bring the box to its knees?
Let me know if I can help you reproduce the problem.
on 11/2/2006 at 4:28 PM
The failure of the cancel button to cancel the report is a known issue that is currently being investigated.
We cannot repro the failure of the site-wide report execution timeout to appropriately cancel reports run inside Report Builder. Please note that the report usually is not cancelled precisely at the specified timeout. It may continue for up to a minute before the server actually cancels it.
on 11/1/2006 at 9:58 PM
Bob Meyers on the Report Builder team was able to validate this. The timeout on all Report Builder generated queries was being set to 0 (infinite). And the stop button is not actually killing the query.
to post a workaround.
Please enter a workaround.
on 11/1/2006 at 9:56 PM
Oops... forgot to post the link:
on 11/1/2006 at 9:56 PM
Bob Meyers on the Report Builder team posted a workaround for putting a time limit on a query generated by Report Builder. His code works with a report model on top of a relational database, not a cube. But using a similar concept, you can code one for Analysis Services connections.
© 2013 Microsoft