Transcript Understanding Analysis Services Architecture

Understanding Analysis
Services Architecture
Microsoft Data Warehousing Overview
OLTP
Source
DTS
DTS Analysis OLE DB
DW
Services for OLAP, Clients
Storage
ADO MD
Data Transformation Services
OLTP
Source
DTS
DW
Storage
DTS
Analysis Services

Transforming and Moving Data

Scheduling DTS Tasks

Automating OLAP Administrative Tasks
Clients
Data Warehouse Storage
OLTP
Source

DTS
DW
Storage
DTS Analysis Services
Not Limited to SQL Server 2000


SQL Server 6.5, SQL Server 7.0, Microsoft Access 97,
Microsoft Access 2000, Oracle 7.3, Oracle 8.0
Any ODBC / OLE DB provider
Clients
Analysis Services
OLTP
Source
DTS
DW
Storage
DTS
Analysis
Services
Clients
Client Interfaces
OLTP
Source

DTS
DW
Storage
DTS
Analysis
Services
Client
Interfaces
APIs:


Low level: OLE DB for OLAP and OLE DB for Data
Mining
High level: ADO-MD
Clients
Client Applications
OLTP
Source
DTS
DW
Storage
DTS

Office

Third-Party Applications

Custom Applications
Analysis
Services
Client
Interfaces
Clients
Analysis Services Architecture
Analysis Manager
MOLAP
Store
OLAP
DM
Engine Engine
Application
Analysis Server
ADO MD
Other
OLE DB
Providers
OLEDB for OLAP
OLEDB for DM
DSO
PivotTable Service
SQL Server
Data
Warehouse
Analysis Manager

Application for Database Administration

Snap-In to MMC

Decision Support Objects
Analysis Manager
DSO
Custom Administration Interface
Analysis Server
Analysis Server Characteristics

OLE DB for OLAP Provider

OLE DB Provider

Windows 2000 and Windows NT Service
Metadata Repository

Contains All Metadata for Analysis Server

By Default, an Access Database

msmdrep.mdb

Can Migrate Repository to SQL Server

SQL Server 7.0 OLAP Services format

SQL Server 2000 Meta Data Services repository format
Analysis Server Limits
Items
Limits
Databases per server
Unlimited
Cubes per database
Unlimited
Cubes per virtual cube
64
Dimensions per cube
128
Measures per cube
1,024
Calculated members per cube
65,535
Levels per dimension
64
Partitions per cube
Unlimited
PivotTable Service

Is Required in Order to Query Server

Is the Desktop OLAP Component—Subset of Analysis
Server

Ships with Office

Provides:

Intelligent data and query caching

Multidimensional formula engine

Ability to create local cubes
PTS Architecture
Client
(Excel 2000, Third-Party Applications)
ADO-MD / OLEDB for OLAP
PivotTable Service
Offline
Cube
File
OLAP
Server
Intelligent Caching
Query 1) January, February, and
March 2000 Sales
Query 2) Quarter 1 2000 Sales
Client
Client
Calculates
1) January, February,
March 2000 Sales
2) Quarter 1 2000 Sales
Query 3) Quarter 1 2000 Sales
and Quarter 1 1999 Sales
Only
Quarter 1 1999
Needed From
Server
3) Quarter 1 1999 Sales
Server
1) January, February, March 2000
Sales
3) Quarter 1 1999 Sales
Multidimensional Expressions

Used as the Syntax for Modeling and Querying an OLAP
Database

Supported by PTS

Part of the OLE DB for OLAP API

Used to Create Calculated Members

Key to Advanced Analytical Capabilities of Analysis
Services
Extensible Markup Language for Analysis - XMLA

XML/A is a Data Access Protocol Extending BI to
Microsoft .NET Platform

Supports Exchange of Analytical Data Between Clients
and Servers


Available on Any Device or Platform

Using Any Programming Language
www.xmla.org
Office OLAP Components

Excel PivotTables

Local Cubes

Web Pivot Control
Internet Support on Clients

Uses IIS to Provide Authentication Over the Internet

Uses HTTP to Pass Through Firewalls

Supports Automatic Setup through ASP

Requires SQL Server 2000 Enterprise Edition
Data Mining in Analysis Services

Data Mining Is The Process of Deducing Meaningful
Patterns and Rules from Large Quantities of Data

Searches for Patterns in Data Rather than Answering
Predefined Questions

Is Used To:

Provide historical insights

Predict future values or outcomes

Close the loop for analysis