Monday, September 27, 2010

Working with Report Snapshots in SQL Server Reporting Services (SSRS)

Problem


We have many SQL Server Reporting Services (SSRS) reports that use our OLTP systems as data sources. We are always adding new reports and the number of users running these reports is also increasing rapidly. We would like to look at leveraging report snapshots as a way to reduce some of the load on our database servers. Can you provide us with an example of how to do this?

Solution

A report snapshot provides the ability to run a report at a scheduled time or on demand and save the report in the ReportServer database. When a user requests the report, they automatically get the snapshot version stored in the ReportServer database rather than executing the SQL queries to retrieve the data from the underlying data source. You can schedule report snapshots to be run during off-peak hours; this can reduce the load on your database servers. You can also create snapshots manually and select which snapshot version of a report that you would like to run. You can save as many snapshots as you like or you can have Reporting Services limit the number of snapshots that are saved. Besides reducing the load on your database servers, you can use report snapshots to archive reports at a particular point in time for later viewing; e.g. month-end reports, year-end reports, etc. Depending on how many report snapshots you keep, you could use a significant amount of space in the ReportServer database. Read more..

1 comment:

  1. This is a good article & good site.Thank you for sharing this article. It is help us following categorize:
    healthcare, e commerce, programming, it consulting, retail, manufacturing, CRM, digital supply chain management, Delivering high-quality service for your business applications,
    Solutions for all Industries,
    Getting your applications talking is the key to better business processes,
    Rapid web services solutions for real business problems,
    Web-based Corporate Document Management System,
    Outsourcing Solution,
    Financial and Operations Business Intelligence Solution,

    prologic-corp

    ReplyDelete