Transcript SQL Server

The Value of Business Intelligence with SQL Server 2008
Andrew Fryer
Microsoft
SQL Server 2008
Your Data Any Place, Any Time
Enterprise Data Platform
Services
Reporting
Integration
Analysis
Beyond Relational
Dynamic Development
Pervasive Insight
Query
Mobile and
Desktop
RDBMS
Synch
FILE
XML
Server
OLAP
Search
Cloud
Transparent Data Encryption
External Key Management
Data Auditing
Pluggable CPU
Transparent Failover for
Database Mirroring
Declarative Management
Framework
Server Group Management
Streamlined Installation
Enterprise System
Management
Performance Data Collection
System Analysis
Data Compression
Query Optimization Modes
Resource Governor
Entity Data Model
LINQ
Visual Entity Designer
Entity Aware Adapters
SQL Server Change Tracking
Data Profiling
Synchronized Programming Model
Star Join
Visual Studio Support
Enterprise Reporting
Engine
SQL Server Conflict Detection
FILESTREAM data type
Integrated Full Text Search
Sparse Columns
Large User Defined Types
Date/Time Data Type
Internet Report
Deployment
Block Computations
Scale out Analysis
BI Platform Management
LOCATION data type
Report Builder
Enhancements
SPATIAL data type
TABLIX
Virtual Earth Integration
Rich Formatted Data
Partitioned Table Parallelism
Query Optimizations
Personalized
Perspectives
Persistent Lookups
… and many more
Change Data Capture
Backup Compression
MERGE SQL Statement
Transparent Data Encryption
External Key Management
Data Auditing
Pluggable CPU
Transparent Failover for
Database Mirroring
Declarative Management
Framework
Server Group Management
Streamlined Installation
Enterprise System
Management
Performance Data Collection
System Analysis
Data Compression
Query Optimization Modes
Resource Governor
Entity Data Model
LINQ
Visual Entity Designer
Entity Aware Adapters
SQL Server Change Tracking
Data Profiling
Synchronized Programming Model
Star Join
Visual Studio Support
Enterprise Reporting
Engine
SQL Server Conflict Detection
FILESTREAM data type
Integrated Full Text Search
Sparse Columns
Large User Defined Types
Date/Time Data Type
Internet Report
Deployment
Block Computations
Scale out Analysis
BI Platform Management
LOCATION data type
Report Builder
Enhancements
SPATIAL data type
TABLIX
Virtual Earth Integration
Rich Formatted Data
Partitioned Table Parallelism
Query Optimizations
Personalized
Perspectives
Persistent Lookups
… and many more
Change Data Capture
Backup Compression
MERGE SQL Statement
Store and Consume
Any Type of Data
Beyond Relational
FILESTREAM data type
Integrated Full Text Search
Sparse Columns
Large User Defined Types
Date/Time Data Type
Deliver Location Intelligence
Within your applications
Beyond Relational
GEOMETRY data type
GEOGRAPHY data type
Virtual Earth Integration
SQL Server 2008
Your Data Any Place, Any Time
Enterprise Data Platform
Services
Reporting
Integration
Analysis
Beyond Relational
Dynamic Development
Pervasive Insight
Query
Mobile and
Desktop
RDBMS
Synch
FILE
XML
Server
OLAP
Search
Cloud
Microsoft BI
An Integrated Offering
DELIVERY
SharePoint Server
Reports
Excel
Dashboards Workbooks
Analytic
Views
Scorecards
Plans
END USER TOOLS & PERFORMANCE MANAGEMENT APPS
Excel
PerformancePoint Server
BI PLATFORM
SQL Server
Reporting Services
SQL Server
Analysis Services
SQL Server RDBMS
SQL Server Integration Services
SQL Server 2008
Intelligent Data Platform
Integration
Integrate
Any Data
Services
Reporting
Deliver
Relevant
Services
Reports
Analysis
Drive
actionable
Services
insights
• Consolidate and cleanse
your data from any
source
• Predictable response
across growing volumes
of data
• Simplified management
of your data warehouse
• Author extensive reports
accommodating any
reporting need
• Manage and scale
reporting workloads of
any size
• Deliver intuitive reports
to every user in the
format and location they
prefer
• Build enterprise-scale
analytical solutions
• Extend with
comprehensive and
predictive capabilities
• Reach every user and
drive actionable insight
SQL Server 2008
Design Pillars for SQL Server 2008 BI Technologies
Integration Services
Pipeline Execution
DW Query Optimizations
Data Compression
Resource Governor
Persistent Lookups
Change Data Capture
MERGE SQL Statement
Data Profiling
VSTA Support
Data Warehousing and
Integration Services 2008
Deliver Relevant Reports
Reporting Services 2008
New Report Designer
Enhanced Data Visualization
New Flexible Report Layout
Scalable Report Engine
IIS Agnostic Report Deployment
New Word Rendering
Improved Excel Rendering
New End User Design Experience
2008 Report Engine Changes
 Report Processing
 On-demand processing
 Hierarchical cursor-based object model
 Rendering
 New rendering architecture
 Renderer rewrites
Why Rewrite Report Engine?
 Scalability
 Reports in SQL 2005 are memory bound
 Memory usage is proportional to data size
 Large datasets can cause out of memory exceptions
 Memory usage in problem renderers (PDF, Excel, CSV)
 Very large reports can starve or fail many smaller
reports
 Much more common scenario than anticipated in
RS 2000 and RS 2005.
Reporting Services 2008
Group
Data
Table
Tablix
Matrix
List
Chart
Chart
Soft Page
Layout
Calcs
HTML
Webforms
Excel
HTML
Winforms
Storage
ROM
Data
Hard Page
Layout
CSV
Image
XML
Image
Print
PDF
Image
Reporting Services 2008
Extensive Report Authoring
Tabl e + Matr ix
Customer
Growth
2001
Retail
Acme
Nadir, Inc.
19%
Acme
Nadir, Inc.
322%
Wholesale ABC Corp.
Wholesale
ABC Corp.
19%
XYZ, Ltd.
322%
Grand Total
Retail
56%
XYZ, Ltd.
Grand Total
2002
Total
1,115
1,331
2,446
152
642
794
11,156 13,312 24,468
1,523
6,421
7,944
13,946 21,706 35,653
Reporting Services 2008
Extensive Report Authoring
Hierarchical rows with dynamic headers
(hierarchical row headers in matrix)
Desired
Current
2005 2006
2005 2006
Washington Total
80
100
50
60
Spokane 30
40
Total
60
80
Portland 40
50
Eugene
30
Seattle
Oregon
20
Washington
80
100
Seattle
50
60
Spokane
30
40
60
80
Portland
40
50
Eugene
20
30
Oregon
Reporting Services 2008
Extensive Report Authoring
Parallel Dynamic Groups
2005 2006
WA Seattle
Current
OR
50
60
Spokane 30
40
Portland 40
50
Eugene
30
20
Table Chair
WA Seattle
OR
Year
Desired
20
30
Spokane 10
20
Portland 10
10
Eugene
5
Product
2005 2006 Table Chair
WA Seattle
OR
50
60
20
30
Spokane 30
40
10
20
Portland 40
50
10
10
Eugene
30
25
5
20
25
Reporting Services 2008
Extensive Report Authoring
Non-detail aggregates in subtotals
(does not make sense as a detail)
Current
Desired
2005 2006
Sue
Count 1
1
Sales
50
Avg
50
NY
Joe
50
60
60
Sue
80
100
60
Total Count 2
2
Count 1
1
Sales
130
160
Sales
80
100
Avg
65
80
Avg
80
100
Total Count 2
2
Sales
130
160
Avg
65
80
...
Joe
...
NY
2005 2006
Demo
Reporting Services 2008 in Action
Extensive Report Authoring
Enriched Visualizations
Charts
Gauges
Maps
Reporting Services 2008
Additional Benefits
Reporting Services 2008
 Layout and pagination consistency
 Distributed client/server computation
 Robust rendering based on client-side
capabilities
 Improved first-page response time
 Lay the foundation for future features
(Bursting, renderer-dependent behavior,
editing in rich clients)
 Opportunity to implement Tablix
SQL Server 2008 Data Mining
Improvements
 Enable better prediction and insight
 Enhanced Time Series Support
 Enhanced Mining Structures





Split data into training and testing partitions
Querying against structure data
Filter data when building models
Build incompatible models in a structure
Cross-validation
Analysis Services 2008
Summary
SQL Server 2008 Business Intelligence
Design
Integrate Any
Data
Process
Experience
Deliver
relevant
Information
Drive
Actionable
Insights
Explore SQL Server 2008
CTP program
• https://connect.microsoft.com/SQLServer/content/content.aspx?Conte
ntID=5395
SQL Server samples
• http://www.codeplex.com/Project/ProjectDirectory.aspx?TagName=SQ
L%20Server
SQL community
Subscribe to blogs
Momentum
• http://www.sqlserverfaq.com/
• http://blogs.msdn.com/keithbu/default.aspx
• http://blogs.technet.com/andrew
• Contact Andrew Fryer
© 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The
information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market
conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT
MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.