Transcript Deliver Rich Analytics with Analysis Services SQL Server

Deliver Rich Analytics
with Analysis Services
SQL Server
Donald Farmer
Group Program Manager
Microsoft Corporation
Agenda
 Analysis Services Overview and What’s
new
 Demo
 Data Mining
 Demo
 Managing and Deploying Analysis Services
 Scalability, Availability, Serviceability,
Manageability
 Summary
Analysis Services
Why OLAP and Data Mining Matter





Powerful business information modeling
Cross platform data integration
Integrated Relational & OLAP views
The best of MOLAP to ROLAP
Data enrichment and advanced
analytics
 Key Performance Indicators and
Perspectives
 Real-time, high performance
 Real-time data in OLAP Cubes
 Very fast and flexible analytics
 XML standards for Data Access and
Web Services integration
 Cost and time savings for customers
integrating with other systems
What Is SQL Server 2005
Analysis Services?
SQL
Server
Analysis
Services
Datamart
 Data
enrichment
and advanced
analytics
Teradata
DW
Spreadsheets
 Real-time
BI Front Ends
Ad Hoc Reports
and high
performance
Oracle
DB2
 Mission
LOB
Rich Reports
critical
Dashboards
Analysis Services
High-level Architecture
Datamart
UDM
Teradata
Spreadsheets
DW
XML/A or ODBO
SQL
Server
Analysis
Services
BI Front Ends
Ad Hoc Reports
Rich Reports
Oracle
DB2
LOB
Cache
Dashboards
SQL Server Analysis Services
New Paradigm for the Analytics Platform
 Unified Dimensional
 Business Intelligence
Model
Enhancements
 Powerful business
 Auto generation of time
information modeling
 Cross platform data
integration
 Integrated Relational &
OLAP views
 KPIs & Perspectives
and other dimensions
based on type
 KPIs, MDX scripts,
translations, currency…
 Proactive caching
 Real-time data in OLAP
Cubes
 Very fast and flexible
analytics
 Data Mining
 10 Mining Algorithms
 Smart applications
 XML standards for Data
Access & Web services
integration
 $$ saving for customers
integrating our solution with
other systems
The Unified Dimensional
Model
Value of Data Mining
Relative New Business Insight
Business Knowledge
SQL Server 2005
Data Mining
Reports (Ad Hoc)
OLAP
Reports (Static)
Easy
Difficult
Usability
 8 new algorithms, 10 in total
 Graphical tools/wizards
 12 embeddable viewers
 SQL Server 2005 makes it easier
 Tightly integrated with AS, DTS,
Reporting
 Integration with Web/Office apps
Complete Set of Algorithms
Decision
Clustering
Trees Introduced in SQL Server 2000
Sequence
Clustering
Association
Linear Regression
Time Series
Neural Net
Text Mining
Naïve
Bayes
Logistic
Regression
Mining for Meaning
BI App lifecycle
Dev/Test
Design
Develop
Debug
Build
Deploy
Version
Dev Server
BI Dev
Studio
Production
Management
Studio
Test Server
Deploy
Inspect
Automate:
• data updates
• permission
updates
Monitor
Version
Prod Server
Prod Server
Prod Server
Scalability
 Fully centralized calculations on the server
 No calculations done on client
 No excess data transported to the client
 Cached calculation
 Ability to cache calculation on disk
 Disk based dimension storage
 Dimension size is not constrained by memory limits
 150 million members already tested
 Role playing dimensions remove need for
duplicating dimension storage
 Attribute-based hierarchies
 Remove need for duplicate info among hierarchies
sharing common attributes
Availability
 Failover Clustering
 Multi-Instances
 Very easy deployment – no registry entries needed
 Server Synching
 Designed for dual machines configurations – number
cruncher machine and end-user facing machine
 Allow:
 Processing the calculations isolated from user interactions
 Isolated verification of the results
 Incremental and transactional synching of the query
machine with the new results
 Enhanced backup and restore
 Unlimited partition sizes
Serviceability
 Trace events (with Profiler Integration)
 Flight recorder (repro-less diagnostics)
 Records server activity and metrics at all times
 Provides diagnostics in the event of system failure
 Allows replay of a failure condition
 ON by default
 Capture and Replay
 Customers use to diagnose performance
 Provides PSS a simple means to repro problems
 Test exploit in Labs
 Dr Watson
Manageability
 Integrated management experience with SQL Server




Single management shell
SQL profiler support
Query analyzer support
Strong integration with IS (DTS) for management tasks automation
 Deployment packages to manage system life cycle
 Dev → Test → Production
 Source control integration for system versioning
 Team development facilities
 Fine grain administration roles
 Database level administration
 Permissions: Creation, R/O, Processing
 XML-based DDL for easy scripting
 Auto referential integrity handling for dealing with dirty
input data
Summary
 Large investment in abilities in SSAS:
 Manageability
 Supportability
 Security
 Availability
 Unified tools with SQL Server
 SQL DBAs skill set can be applied to
administering Analysis Server
 BI platform for 24/7 operations