SSRS Cached Reports v Snapshots

SSAS and SSRS
1

Hi,

First post, so be gentle!

I am relatively new to SQL and SSRS and have self taught myself so this may be a simple question, asked by a simple person!

What is the difference between Snapshots and Cached Reports? I have a report that contains high volumes of data and takes our management team a few minutes to run each time.

What I would like to do is provide them with a more efficient way of accessing this data and I am hoping either a Cache or Snapshot can help so that when they come to run their report(s) each day, they are only accessing / filtering on the data already extracted.

Whats the best way to handle this? Is it possible to run the cache / snapshot with all parameters selected and then just allow the users to filter out the data they dont want, without having to execute the full report again?

Apologies if I've explained it really badly - as I said, I'm new to SQL and SSRS!

Thanks in advance for reading.

Glenn

2

I found this article: http://www.sqlchick.com/entries/2013/4/16/ssrs-report-execution-snapshot-vs-a-report-history-snapshot.html

It explains the caching of reports and how both types of snapshots work.

In addition - if you know the parameters to be used - you could create multiple linked reports. You can then create a specific linked report for each set of parameters - and generate a snapshot or cache the linked report. Your users then access the specific version of the report which will have the data filtered for them.

I prefer the snapshot approach myself - which allows me to keep a full year of reports that we can go back to if needed.

2 Likes
3

Hi,

Thanks for the advice. if I cache my report with the widest possible parameters, will anyone who subsequently runs the report with narrower parameters benefit from the cache or does this just re-run the whole report using the new parameters?

For example if I were to run the report with all 10 parameters defaulted to "select all", and then my colleague came along after and re-ran the report with some parameter selections that narrowed down the data selection,would that actually help?

Thanks again

Glenn

4

I really cannot answer that - but I would guess that it would not help and the cached report would be the last report executed.

That is why you need to look at snapshots and possibly using linked reports to pre-generate the reports with specific parameters.

5

Last I verified it, reports are cached by user and parameters. That is, the user and all params must match exactly to use a cached report.

If you have some long-running reports that are consistent -- for example, user1 with param2, param3, and a specific param5 list -- you can pre-generate that report and it will be cached.

6

Sorry for the stupid question, but am I able to make multiple caches? For multiple users. So: -

· User 1 with Param 1, Param 2 and 3 selections from Param 3 list

· User 2 with above but only 2 selections from Param 3 list

Thanks

7

Yes, You can pre-run as many different combinations as you like. Typically they're set up to run automatically at a certain time. Just make sure you have enough disk space for the ReportServer* databases; even though the results are stored compressed, naturally the more combinations you store, the more total space it will take.

8

Thanks Scott, Much appreciated. The cache worked perfectly for me – shaved quite a few minutes off the report generation.

One final question– are cache schedules I create on a per user basis? For example, If I am caching a report on a schedule, would another user be able to benefit from this, or would I need to create this for them whilst they are logged in and accessing SSRS?

9

They don't have to be logged in at all, as long as the report is generated based on their user id. I used to pre-generate reports for several different users ahead of time.

10

Thanks again Scott…

My final question!

I just ran a report based on my cached parameters and it ran in a matter of seconds returning results as expected. I then changed some (not all) of those parameters and re-ran the report and it returned the exact same results as previously which I wasn’t expecting. I thought it would re-run the report? For example, my report was based on 6 months financials for our company. I changed this report to 9 months, but the same figure was returned. Was this something to do with the cache being used again rather than it regenerating a fresh report?

11

It really shouldn't be. If the param values as passed to the report are different, SQL should not use a cached result but generate a new one. Something else must be going on, although I can't say what it might be.

12

Thanks Scott,

I'll keep investigating and see if I can find a cause. Could it be related to the setting under processing options that reads "Cache a temporary copy of the report. Expire after a number of minutes".

Should I use this setting, or leave it as "Always run this report with the most recent data"?

Thanks again

13

If you choose the "Always run", you're turning off caching. You probably just want to reset the number of minutes to timeout after. Some reports may really need up-to-the-hour (or up-to-the-minute), but many actually don't and you can save resources by leaving them cached for several hours.