Transcript We have control!

Chris Testa-O’Neill
QA
Who am I
 Chris Testa-O’Neill
 Business Intelligence Specialist at QA
 Technical Author for Microsoft E-Learning

Author of the SQL Server 2008 E-Learning track
 Manchester SQL Server User Group Organiser


www.sqlserverfaq.com
www.learnsqlserver.org
Agenda
 Parameters and Filters
 Caching
 Snapshots
 History
 Subscriptions
 Q and A (if time permits)
Due to time this session cannot
 Report Authoring
 Microsoft SQL Server 2008 R2: What’s new in Reporting
Services – Donald Farmer
 Report Builder 3 (tomorrow) – Andrew Fryer
 Security
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
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
Execution process for cached instances
1.
Retrieves most up-to-date data and processes report
2.
Creates intermediate report and stores intermediate result
in the cache in ReportServerTempDB
3.
Flags intermediate report as a cached instance
4.
Renders report from cached instance
5.
Later requests for the same report are retrieved from the
cached copy
Snapshots
 Snapshot are created in advance of the user browsing
 Stored in the ReportServerDB and are persistent
 Can be used to create historical data
• Execution process for snapshots

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
Demonstration
Creating Parameters and Filters
Creating Cache settings
Creating Report snapshots
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
Demonstration
Creating Historical Reports
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
Demonstration
Creating User Subscriptions
Creating Data Driven Subscriptions
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)
Related Sessions
 Report Builder 3 (tomorrow) – Andrew Fryer
Europe’s Premier
Community
SQL Server Conference
Up and coming events..
November 2009
Tuesday 24th
London – Storage and Query Optimisation - www.sqlpass.org.uk
Thursday 26th
London – SQL Internals and MS BI – SQLServerFAQ.com
Thursday 26th
London – Looking at newsgroups for Info – SQLServerFAQ.com
December 2009
Thursday 3rd
Tuesday 8th
Wednesday 9th
Thursday 10th
Tuesday 15th
Thursday 17th
Online – PowerUp with SQL Server (see next slide)
Cork – Query Optimisation – MTUG.ie
Dublin – Query Optimisation – IrishDev.com
Leeds – Service Broker and Powershell – SQLServerFAQ.com
Cambridge – Christmas Special down the pub – SQLSocial.com
Manchester – SQL Server Internals – SQLServerFAQ.com
Time to Power Up with SQL Server within your organization!
Who Should Attend:
-Database administrators
-Application developers/programmers
-Database developers
- IT Professionals responsible for SQL Server management
Sign Up Today at http://tinyurl.com/PowerUpSQLServer
During the day's presentations by SQL Server experts, Greg Low, Javier
Loria, and Niels Berglund, you'll get detailed information and time to
ask your questions.
Three sessions, directly from your own computer:
- Understanding Query Plan Caching in SQL Server
- SQL Server High Availability
- SQL Server Performance
It’s party time!
Coming up next in the Atrium:
Time to relax and let your hair down.
Rockband
Table football
Air Hockey
and more
Don’t forget speakers and sponsors have drinks
vouchers
We hope you had a great
conference!
See you at the next one in
the Spring
Thank you