I need my reports............. YESTERDAY!!!!!

1. I need my reports.............YESTERDAY!!!!! Chris Testa-O’Neill EMC Consulting

2. Who am I • Chris Testa-O’Neill • Practise Consultant at EMC Consulting • Technical Author for Microsoft E-Learning • Author of the SQL Server 2008 E-Learning track • SQLBits Organiser • www.SQLBits.com • Manchester SQL Server User Group Organiser • www.sqlserverfaq.com

3. Europe’s Premier Community SQL Server Conference

4. Agenda • Parameters and Filters • Caching • Snapshots • History • Subscriptions • Q and A (if time permits)

5. Parameters and Filter • From a users perspective, parameters and filters look the same • Parameters restricts the data at the source • Filters returns all of the data and filters in place • Parameters and filters affected by caching and snapshot settings

6. Caching • Caching is not enable by default • Can be used to improve the performance of retrieving reports by storing a copy in the ReportServerTempDB • Configured to set an expiry time for the cache • Consider “pre heating” the oven using data driven subscriptions

7. Other Report Request First Report Request Published Report Intermediate Report Rendered Report Report Server Cache • Execution process for cached instances • Retrieves most up-to-date data and processes report • Creates intermediate report and stores intermediate result in the cache in ReportServerTempDB • Flags intermediate report as a cached instance • Renders report from cached instance • Later requests for the same report are retrieved from the cached copy

8. Snapshots • Snapshot are created in advance of the user browsing • Stored in the ReportServerDB and are persistent • Can be used to create historical data

9. Creation of snapshot is scheduled for a specific point in time • The intermediate report is created and the result is stored as a snapshot in the report server database • Requests are satisfied by retrieving and rendering the snapshot Rendered Report Report Request Snapshot Creation Published Report Intermediate Report Report Server Snapshot Cache • Execution process for snapshots

10. Demonstration Creating Parameters and Filters Creating Cache settings Creating Report snapshots

11. I need a report from.....yesterday • Historical data provides a point in time copy of reports • Impact on parameters and filters • Control the number of reports stored • BE AWARE – Reducing the number of reports stored will truncate the reports

12. Demonstration Creating Historical Reports

13. Subscriptions • Standard Subscriptions • User and Administrator driven • Data Driven Subscription • Administrator driven • Requires the creation of a subscriber table • Can be used to pre populate a cache

14. Demonstration Creating User Subscriptions Creating Data Driven Subscriptions

15. Available courses • Microsoft Official Curriculum courses • SQL Server 2005 • 2793 – Implementing and Maintaining SQL Server Reporting Server 2005 • SQL Server 2008 • 6236 - Implementing and Maintaining SQL Server Reporting Server 2008 • Microsoft Elearning • Collection 6322: Implementing and Maintaining Business Intelligence in SQL 2008: Integration Services, Reporting Services and Analysis Services (available late 2009)

16. Thank you