I have a report in SQL Reporting Services (SSRS) that pulls data from an SQL Analysis Services (SSAS) cube. The Cube has two important dimensions - Time and Activity that are related (it's a report on activity over time). The activity dimension has a single unique key, and attributes to indicate who performed the activity. Measures are simple counts and percentages of types of activity and their results.
The report looks something like this:
Report for user: xyx
Report Period: 1/1/2011 - 3/1/2011
Type of Activity | Submitted | Completed | Success Rate
Type 1 | 50 | 20 | 40%
--------------------------------------------------------
Type 2 | 50 | 20 | 40%
--------------------------------------------------------
Type 3 | 50 | 20 | 40%
--------------------------------------------------------
Type 4 | 50 | 20 | 40%
--------------------------------------------------------
Type 5 | 50 | 20 | 40%
--------------------------------------------------------
Total | 250 | 100 | 40%
If I browse the cube is SQL Management studio, I get the results in a fraction of a second. In SSRS it takes upwards of 7 minutes to generate. The Execution Log for SSRS shows time pretty evenly split in retrieval/processing/rendering at:
> TimeDataRetrieval TimeProcessing TimeRendering
> 170866 142324 154689
I suspect it has to do with how the report is filtered, but I don't know how investigate that.
What should I look at next to figure out why SSRS seems to take so long when doing the browse in SSAS is really fast (and the actual reports aren't much bigger than my sample, 3 more rows and a few more columns)?
