Transcript A Technical Overview of Microsoft SQL Server 2005

®
Microsoft
™
Server
SQL
Business Intelligence
Barnaby Jeans
IT Pro Advisor
Microsoft Canada
http://blogs.technet.com/Barnaby_Jeans
2005
Prerequisite Knowledge
• Experience administering SQL Server
• Experience administering IIS
• Familiarity with Visual Studio.NET
Level 200
What We Will Cover
• Integration Services
• Analysis Services
• Reporting Services
Improving organizations by
providing business insights
to all employees leading to
better, faster, more
relevant decisions
Advanced Analytics
Self Service Reporting
Business Performance Management
Operational Applications
Embedded Analytics
SQL Server
Business Intelligence
Integrate


Data acquisition from
source systems and
integration
Data transformation
and synthesis
Analyze


Data enrichment,
with business
logic, hierarchical
views
Data discovery via
data mining
Report


Data presentation
and distribution
Data access for
the masses
Customer challenges:
business demands
• Shorter processing windows
– 24:7 businesses have no downtime
– International business has no quiet time
– Online business operates in real-time
• More regulation
– Sarbanes-Oxley, Basel II, impose strict compliance
requirements
• More sophisticated users
– Drive smart decisions at every level from operational to
strategic
Customer challenges:
data demands
• Ever greater volumes of data
– Clickstreams, e-commerce, RFID, call-centres
• More diverse sources of data
– Web services, RSS, unstructured and semi-structured,
many locales
• More diverse destinations
– Mobile devices, personalized reporting, web publishing
SQL Server
Business Intelligence
Integrate


Data acquisition from
source systems and
integration
Data transformation
and synthesis
What is SQL Server
Integration Services?
• A new SQL Server Business
Intelligence application
• The successor to Data
Transformation Services
• The platform for a new
generation of high performance
data integration technologies
Example: before Integration Services
Alerts & escalation
Call centre data: semi structured
Text Mining
Staging
Staging
Legacy data: binary files
Hand
coding
Cleansing
&
ETL
Application database
ETL
Data mining
ETL
Staging
ETL
Warehouse
Reports
Mobile
data
•Integration and warehousing require separate, staged, operations.
•Preparation of data requires different, often incompatible, tools.
•Reporting and escalation is a slow process, delaying smart responses.
•Heavy data volumes make this scenario increasingly unworkable.
Example: with Integration Services
Alerts & escalation
Call centre:
semi-structured data
Text mining
components
Data mining
components
Custom
source
Merges
Standard
sources
Data cleansing
components
Mobile
data
Warehouse
Legacy data: binary files
Application database
SQL Server Integration Services
Reports
•Integration and warehousing are a seamless, manageable, operation.
•Sourced, prepare and load data in a single, auditable process.
•Reporting and escalation can be parallelized with the warehouse load.
•Scales to handle heavy and complex data requirements.
How SSIS Works
•Data sources can be diverse, including custom or scripted adapters
•Transformation components shape and modify data in many ways.
•Data is routed by rules or error conditions for cleansing and conforming.
•Flows can be as complex as your business rules, but highly concurrent.
•And finally data can be loaded in parallel to many varied destinations.
Components in the data flow
• Some components work with data row
by row
–
–
–
–
Calculating new columns
Converting data
Character conversions
Look-up joins to reference tables
• These benefit more from parallelism
than memory
Components in the data flow
• Some components need to work with
the entire data set
– Aggregation
– Sorting
– Fuzzy (best match) Lookups and
Deduplication
• These benefit from increased memory
– 64 bit enables potentially huge data
sets to be worked on in memory
Enabling new architectures …
Traditional warehouse loading
•In this traditional scenario, the integration process simply conforms
data and loads the database server
•The database performs aggregations, sorting and other operations …
•… but has to contend with competing demands for resources from user
queries
•This solution does not scale to very large volumes of data and multiple,
complex aggregations
Enabling new architectures …
Warehouse loading with SQL Server Integration Services
•Here, SQL Server Integration Services conforms the data as before …
•… but also aggregates and sorts, and loads the database
•This frees-up the database server for user queries
•With 64-bit this solution scales well to very large volumes of data and
multiple, complex aggregations
•Even with 32 bit, this architecture can be scaled-out to use a separate
box for the integration process
Customer benefits of SSIS
• Performance
– Data flows process large volumes of data efficiently - even through
complex operations
• Facility
– Many prebuilt adapters and transformations reduce hand coding
– Extensible object model enables specialized custom or scripted
components
– Highly productive visual environment speeds development and debugging
• “Smarts”
– Data cleansing features enable difficult data to be handled during loading
– Data mining brings intelligent handling of data for imputation of incomplete
data, conditional processing of potential problems, or smart escalation of
issues such as fraud detection
demonstration
Integration Services
Build
a package to import files in the database
SQL Server
Business Intelligence
Integrate


Data acquisition from
source systems and
integration
Data transformation
and synthesis
Analyze


Data enrichment,
with business
logic, hierarchical
views
Data discovery via
data mining
What Is SQL Server 2005
Analysis Services?
SQL
Server
Datamart
Analysis
Services
Spreadsheets
BI Front Ends
Teradata
DW
UDM
Ad-Hoc Reports
Rich Reports
Oracle
DB2
Cache
LOB
Dashboards
The Role of OLAP
• “Database” OLAP
• “Spreadsheet” OLAP
• The Value Add from Analysis Services
Database Data Access
• SELECT Sum(«Measure»)
FROM «Source»
WHERE «Slicers»
GROUP BY «Dicers»
• Potentially very slow
Spreadsheet Data Access
• Cell Reference =C5
• Retrieves data by position
A
1
2
3
4
5
6
7
B
C
D
E
F
Quarter
Product
Qtr 1
Qtr 2
Qtr 3
Qtr 4
Grand Total
Apples
500
3000
2400
3900
9800
Cherries
700
2200
3800
4600
11300
Grapes
1600
2800
3800
4800
13000
Melons
1000
2900
3800
5900
13600
Grand Total
3800
10900
13800
19200
47700
Spreadsheet
Simple
Formulas
Complex
Users Need Both
Database
Small
Large
Data
Value Add from OLAP
• Pre-stored Aggregations
– Improve speed for summarized queries from additive
measures
• Metadata about dimension attributes and their
relationships
– Enables client to help user navigate
– Facilitates relationship-based calculations
demonstration
Analysis Services
Build
a cube and implement KPIs
Data
Mining
Spreadsheet
Simple
Formulas
Complex
Role of Data Mining
Database
Small
Large
Data
Data Mining Benefits
• Explore your data
– Who are my best customers
– What are my sales likely to be next quarter
– What else are they likely to buy
• Act on patterns and trends
Two Types of Skills for Data
Mining
• Technical
– How to use build efficient model
– How to train/update model
– How to query model
• Statistical
– What questions are meaningful?
– How representative is the data set?
– How should we set model parameters?
•SQL Server 2005 Data Mining
Algorithms
Decision Trees
Sequence
Clustering
Clustering
Association
Time Series
Naïve Bayes
Neural Net
SQL Server
Business Intelligence
Integrate


Data acquisition from
source systems and
integration
Data transformation
and synthesis
Analyze


Data enrichment,
with business
logic, hierarchical
views
Data discovery via
data mining
Report


Data presentation
and distribution
Data access for
the masses
Architecture and Deployment
Introduction to Reporting Services
Browser
Data Sources
(SQL, OLE DB, ODBC,
Oracle, Custom)
Management
URL
Custom App
WMI
Web Service
Report Server
Output Formats
(HTML, Excel,
PDF, Custom)
Report Processing
Data Retrieval
Rendering
Security
Delivery
SQL Server Catalog
Delivery Targets
(E-mail, File share,
Custom)
Architecture and Deployment
Report Processing
Data
On-demand
Access
Report
Definition
Intermediate
Format
Scheduled or
Push Access
Rendered page
in HTML, PDF
or other format
Layout
Snapshot or
Report History
Architecture and Deployment
Reporting Services Client Components
• Report Designer
– Integrated with Visual Studio.NET 2003
• Web Browser
– View and Manage Reports
• Third Party Tools
– Management
– Authoring
– Supported through open interfaces or Web Services API
Architecture and Deployment
Report Authoring
Authoring
Management
Delivery
Online
Access
Report
Definition
Managed
Report
Delivery
Channels
Create Reports
with RDL
Report resources
published/managed as
Web Service
Supports pull and
push delivery of
reports
Architecture and Deployment
Small Deployment
Server
Client
Report Manager
Report Server
Report Server
Database
Report Designer
Architecture and Deployment
Medium Deployment
Server A
Report Manager
Report Server
Server B
Report Server
Database
Client
Report Designer
Architecture and Deployment
Large Deployment
Report Server
Web Farm
Report Server
Clients
Report Server
Report Server
Data
Sources
SQL Server
Cluster
Report Server
Database
Report Server
Database
Creating Reports
Visual Studio .NET Integration
Creating Reports
Report Definition Language (RDL)
• Report Definition Language – XML schema contains
– Data source information
– Layout
– Report Properties
• Definitions stored in Report Server Database
• Can create tools that use RDL
Managing Reports
Report Manager
• View, Search and Subscribe to Reports
• Create and manage:
–
–
–
–
–
–
Folders
Linked reports
Report history
Schedules
Data source connections
Subscriptions
• Set properties and report parameters
• Manage role definitions and assignments
Managing Reports
Report Manager
Managing Reports
Viewing Reports
• Reports can be rendered in:
– HTML
• HTML with Office Web Components
– XML
– CSV
– Excel
– Image – such as TIFF
– Acrobat PDF file
Managing Reports
Report Subscriptions
Managing Reports
Report Execution Processing
• On Demand
• On Demand From Cache
– Removed from cache at defined intervals
• From Snapshots
– Query is separate from render
Managing Reports
Securing the Report Server and Reports
• Reporting Server Security
– Uses IIS security
– Windows security
• User Permissions
– Windows security
– Role-based authorization
demonstration
Creating, Customizing and
Importing Reports
Creating a Report
Customizing a Report
Reporting Services Summary
• Supports the full reporting life cycle
• Highly scalable
• You do not need to know RDL to create a report
definition
• Supports several data sources
• Reports can be rendered in a variety of formats
• Reports can be executed on demand or cached
for quicker access
SQL Server
Business Intelligence
Integrate


Data acquisition from
source systems and
integration
Data transformation
and synthesis
Analyze


Data enrichment,
with business
logic, hierarchical
views
Data discovery via
data mining
Report


Data presentation
and distribution
Data access for
the masses
For More Information
• Visit TechNet at www.microsoft.com/technet
• For additional information on books, courses, and
other community resources that support this session,
visit
blogs.technet.com/Barnaby_Jeans