none
report/sub performance

    Question

  • I am using SSRS 2012, querying a 2012 SSAS cube. I have 3 sub-reports that are almost identical, except that they filter for a different Business Unit, and display a different hierarchy.

    On sub report renders within seconds when run from Report Server. The other two take a minute or more. But the MDX queries for each, when run in SSMS with appropriate parameters, return data within seconds. When rendering the reports in BIDS (VS 2008), all three get rendered quickly, but that is from cached data in the rdl.data file.

    It seems to be the Report Server that is having trouble rendering two of the reports. Is there any way to (easily) differentiate query time vs rendering time for the report server.

    One theory is that if the MDX query yields NULL for all the Measure Cells in the result set, then that column is not returned in the result set, which in turn throws errors in the Report Server Log. Could this be the cause of the long processing times?


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Thursday, June 13, 2013 7:43 PM

Answers

  • I found the answer to this: The "Show Empty Cells" button. Seems the reports were running fine as long as there was data in all the columns. But with no data for one or more of the Measures in the MDX statement, the query does not return that column. So now the report renderer kind of chokes for a while, logs some stuff, then eventually displays the report. It explains why it took LESS time to pull up a report that was looking at MORE data! (With more data, it had a better chance that all the Measure columns had data.)

    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Friday, June 14, 2013 11:50 AM

All replies

  • I found the answer to this: The "Show Empty Cells" button. Seems the reports were running fine as long as there was data in all the columns. But with no data for one or more of the Measures in the MDX statement, the query does not return that column. So now the report renderer kind of chokes for a while, logs some stuff, then eventually displays the report. It explains why it took LESS time to pull up a report that was looking at MORE data! (With more data, it had a better chance that all the Measure columns had data.)

    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Friday, June 14, 2013 11:50 AM
  • Hi Todd,

    Glad to hear that you have resolved the issue by yourself. Thanks for your valuable sharing. I believe more community member can benefit from this thread.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Sunday, June 16, 2013 8:25 AM
    Moderator