Transcript Enterprise Reporting with Reporting Services

Enterprise Reporting with
Reporting Services SQL
Server 2005
Donald Farmer
Group Program Manager
Microsoft Corporation
Agenda
 Reporting Services Overview
 Report Builder Demo
 Architecture
 Report Management Demo
 Managing Reporting Services
 Scheduling
 Logging
 Security
 scalability
 Summary
Reporting Services
Enhanced Reporting Capabilities
Scalable Server
 Rich, Enterprise Reporting Platform
(static and interactive)
 Multiple data sources with
multiple delivery options
 Scalable, manageable and embeddable
Web Services architecture
 Scheduling, Snapshots, Caching, more…
Advanced Authoring Tools
 Visual Studio IDE
 XML specification (RDL)
 3rd party extensibility
End-user, Ad Hoc Reporting
Strong Management Story
 SOAP Web Service APIs
 Report Manager portal
 Extensible security model
 Integration with AS, IS, management tools
Report Builder
 Extending the Reach
of Reporting Services
 Ad Hoc Reporting for
the End-User
 1-Click Install
 With Report Builder:
 Report off a
Business Model
 Modify a Report
 Build a New Report
 Report on Relational
or OLAP data
Reporting Models
 Paradigm: Build Once : Query Many
 Empowers users to create their own reports
 End-user reports can be further edited &
extended in BI Development Studio by
professional report developers
 Provide easy data navigation with drill-
through relations
 Mask relational and OLAP schema
complexity
 Present data in familiar business terms
Report Builder
Architecture
Browser
Builder
Office
Custom App
Web UI
Data Sources
(SQL, OLE DB, XML/A,
ODBC, Oracle, Custom)
Programmatic Interfaces
Report Server
Report Model
Output Formats
(HTML, Excel,
TIFF, Custom)
Security Services
(NT, Passport,
Custom)
Report Processing
Data Processing
Security
Rendering
Delivery
SQL Server Catalog
Delivery Channels
(E-mail, SharePoint,
Custom)
Management Tools
 SQL Server 2005 Management Studio
 Superset of Report Manager functionality
 Report Manager
 Web-based viewing and management application
 Reporting Services Configuration Tool
 Windows-based tool for local or remote configuration of service
 Client Utilities
 Script Host
 Encryption Key Management
 Custom Applications
Reporting Services 2005
Configuration Tool
Configuration Tool Features
 Virtual Directories
 Supports non-default Web sites
 Service Identities
 Database Settings
 Creation and Upgrade
 Scripts can be saved to be applied later
 Key Management
 Scale-out Initialization
 Does not sync settings across machines
 E-mail Delivery Settings
 Execution Account
Role-Based Security Model
 Tasks
 Sets of low-level operations
 Item-level (e.g., create report) or system-level
(e.g., manage jobs)
 Not customizable
Operation
Task
 Roles
 Sets of tasks
 Default roles installed by default
(browser, publisher)
 Default roles can be customized, new ones created
 Roles identified by name, localized
Group or
 Groups/Users
User
 Windows/Active Directory or custom
authentication users
Role
Role
Assignment
 Role Assignments
 Associates groups/users with Roles
 Inherited from parent in namespace
Item
Managing Report Execution
 Configure cache and snapshots via Report
Manager or SQL Management Studio
 Set execution timeouts on a system-wide or per-
report basis
 Long running reports can be stopped manually
 Report Execution Log enables analysis of server
usage
 Optionally, executions are logged to Report Server database
 Includes report, format, user, start, end,
cache hit, size
 Setup includes SSIS package and sample reports
Scheduling
 Management events can be scheduled on
the report server
 Caching, Subscriptions, History
 Schedules are stored in database and
integrated with SQL Agent
 When triggered, Agent adds entry to queue
 Scheduled events are queued in database
and polled by Windows service
Logging and Monitoring
 Performance Monitoring
 Counters for performance
 Report Execution for analysis
 Event Log Integration
 Critical events and errors
 Trace Events
 Can traces all server activities, response
times, security events
 Detail level depends on configuration setting
Scale-Out Deployment
Data Sources
Report Metadata
and Cache
Failover Cluster
Reporting Services
Scale Out Deployment
IIS
Report Server
Oracle
SQL Server
Windows Server
Windows Server
IIS
SQL Server
Report Server
SQL Server
DB2
Windows Server
Windows Server
IIS
Flat Files,
OLE DB,
ODBC
Report Server
Windows Server
NLB
Clients
Scale-Out Setup
 Run setup (files only) to install first report
server instance
 Run setup (files only) to install second
report server instance
 Use configuration tool to create report
server database and configure first report
server instance
 Use configuration tool to configure second
report server instance
 Install and configure load balancing
functionality (NLB, switch)
Data Encryption
 When data source connections and credentials are
stored, they are encrypted in Report Server database
 Stored symmetric key encrypted with instance-based
private key
 In SQL Server 2005, only Windows service has encryption /
decryption logic
 Shared by all machines in scale-out deployment
 Restore key when machine name, installation or Windows
service account changes
 Manage keys with RSKEYMGMT or
Configuration Tool
 Extract a copy of the encryption key
 Apply stored encryption key
 Remove encrypted data on machine
 Always backup your symmetric key!
Summary
 SQL Server 2005 Reporting
Services…
Builds on SQL Server 2000 version with
core product enhancements and crossproduct integration
Improves developer productivity through
Visual Studio integration and report
controls
Enables ad hoc report creation by end
users through new Report Builder client
For More Information
 Reporting Services TechCenter
 http://www.microsoft.com/technet/prodtechnol/sql/2005/technolog
ies/ssrsvcs.mspx
 Developer Center
 http://msdn.microsoft.com/sql/bi/reporting/default.aspx
 SQL Server 2005 Reporting Services Forum:
http://forums.microsoft.com/msdn/ShowForum.aspx
?ForumID=82
 Course 2030: Creating Reporting Solutions:
http://www.microsoft.com/traincert/syllabi/2030AFina
l.asp
 Project Real
 http://www.microsoft.com/sql/solutions/bi/projectreal.mspx
 On-demand Webcasts
 http://www.microsoft.com/events/series/sqlserverbi.mspx