locked
A General Question About Best Approach to Creating a Report W/A Large Number of Records RRS feed

  • Question

  • I'm gonna try to be specific, but I'm afraid this is kind of a generalized questions to begin with.  I'd rate my SSRS proficiency as somewhere around upper-novice to lower-proficient developer.  I have a pretty good grasp on most of the basics and can even manage a trick or two in developing reporting solutions. Currently, I'm stumped however.

    I need to create a report that will summarize student suspensions for each school in my district (approximately 160 schools), on the same page.  Our schools are grouped into 6 areas, so basically I have the the 6 areas as column groups, and the schools as row groups.  The initial creation of the report worked great, as they just wanted to see suspensions per schools.  I was able to group the mdx data coming in so that only 160 rows of data were being processed (1 for each school) and the report came back in under a second.  

    After seeing this, everybody got excited and now they wish to have filters placed on the report so that they can see the suspensions broken down by Race, Gender, and other labels (Is the kid on Free/Reduced lunch? Limited English? etc.).  So I have multiple filters I have to incorporate.  All these labels are (obviously) already attached to each student and are attributes in the Student dimension, so I thought, no big deal, I'll just load in each student and the respective attribute flags, which is about 100,000 records total.  Unfortunately, this has really put a hurtin' on the report -- it's taking about 75 seconds to run this way.  About 30 seconds of this runtime seems to be from pulling in the data from the mdx query itself.  I noticed that each time I added a new attribute field from the student dimension, it would add about an extra 4-5 seconds of processing time.

    Next, I tried to create calculated members in the cube itself for each student attributes: total number of males/females suspended, total number of asians/hispanics/whites suspended, total number of "Free/Reduced Lunch" suspended, etc.  This let me get the number of rows back down to a manageable size and the report speeded back up to about 2-3 seconds.  I thought I was home free, but then I started testing and discovered another issue.  If somebody filters to see, say, Asian Females on Free/Reduced Lunch -- it's possible that a student is going to show up three times because they could be Asian, Female, and on Free/Reduced Lunch - so there's no way to filter that out, I think.

    I really don't see anyway around pulling in each individual kid, which takes me back to about 100,000 rows.  Am I missing something? Is there a better way to do this?  Is there a trick to organizing my cube or the dimension attributes that I don't know about?  Why would adding an attribute from a record that is already "accounted for" add so much extra time onto the processing?  Is it just a known fact that trying to process so many records will not work?

    I've included a screenshot below to give you an idea of what my cube looks like, and the rediculous amount of calculated summary measures i've created trying to fix this...

    Wednesday, December 26, 2012 5:43 PM

Answers

  • A few possibilities:

    1. Pass the parameters to SSAS, and don't use local filters: http://sqlblog.com/blogs/stacia_misner/archive/2010/10/07/29231.aspx (This would put the workload on the Cube instead of SSRS, and result in only applicable records being passed to SSRS, instead of the whole dataset)

    2. Use report snapshots. This would mean running a snapshot nightly (or on another schedule), but that means that when the report is run, the report is rendering data cached in SSRS, and does not have to go back to the cube.


    Mike G.

    • Proposed as answer by Fanny Liu Tuesday, January 8, 2013 1:21 AM
    • Marked as answer by Fanny Liu Tuesday, January 8, 2013 9:40 AM
    Monday, December 31, 2012 5:03 PM

All replies

  • Have you thought about using Power Pivot (free addon to Excel)?

    It can slice and dice the data in real time and can handle millions of rows of data easily, so 100k is not an issue.

    SSRS is really good at just dumping data and not so great at handling complex data structures.


    Dusty

    Thursday, December 27, 2012 3:21 PM
  • Hi Puffster,

    Based on your description, the report data take too long to retrieve as there are larger data in your database. In order to increase the report performance, please refer to the following methods:
      1.Redesign the report queries: Return less data in the report queries; Use a better restriction on the WHERE clause of the report queries.
      2. Simplify the report design: Use less data regions or controls in the report; Use drillthrough report to display details.

    For example, in the main report, you can retrieve the summarize suspensions for each school in the report query. And then create a drillthrough report with parameter which displays the details for each school or each student. In the main, add a drillthrough action and pass the parameter in the drillthrough report.

    For more methods, please refer to the following articleTroubleshooting Reports: Report Performance

    Regards,
    Fanny liu


    Fanny Liu
    TechNet Community Support

    Monday, December 31, 2012 9:08 AM
  • Thanks, Dusty & Fanny, for your suggestions!  Our reports are displayed using PerformancePoint on SharePoint, I just looked and noticed that PerformancePoint does support Excel Services Reporting, but I'd like to look at that as a last-option as all our other reports are doing using Reporting Services and I'd like to stay consistent.

    To give a little more detail, I'm supplying another screen shot that gives a visual of what the report looks like, and two versions of the dataset results.  The first dataset result is what the original query was pulling back, just a simple one record per school that displayed suspension data.  However, after putting in the filtering section at the top of the report, when I added these filter columns into the dataset, you can see that it now takes about 175 rows to return the data for each school because it has to chop the data up to account for each filtering possibility.  There could be any combination of these filters that could be turned on/off, so I'm not sure how I could create a drill through report to accommodate for all the different possibilities.

    Monday, December 31, 2012 3:55 PM
  • A few possibilities:

    1. Pass the parameters to SSAS, and don't use local filters: http://sqlblog.com/blogs/stacia_misner/archive/2010/10/07/29231.aspx (This would put the workload on the Cube instead of SSRS, and result in only applicable records being passed to SSRS, instead of the whole dataset)

    2. Use report snapshots. This would mean running a snapshot nightly (or on another schedule), but that means that when the report is run, the report is rendering data cached in SSRS, and does not have to go back to the cube.


    Mike G.

    • Proposed as answer by Fanny Liu Tuesday, January 8, 2013 1:21 AM
    • Marked as answer by Fanny Liu Tuesday, January 8, 2013 9:40 AM
    Monday, December 31, 2012 5:03 PM
  • Sorry I haven't gotten back to give an update...looks like the biggest issue I had was I had the report set to "show empty cells", when I discovered this and unchecked it, the report went from 75 seconds to under 10 to return data.
    Tuesday, January 8, 2013 1:43 PM