understanding analysis services architecture. microsoft data warehousing overview oltp source dts dw...
Post on 24-Dec-2015
223 Views
Preview:
TRANSCRIPT
Microsoft Data Warehousing Overview
OLTPSource
DTS DWStorage
Analysis Services Clients
OLE DBfor OLAP,ADO MD
DTS
Data Transformation Services
Transforming and Moving Data
Scheduling DTS Tasks
Automating OLAP Administrative Tasks
OLTPSource
DTS DWStorage
Analysis Services ClientsDTS
Data Warehouse Storage
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
OLTPSource
DTS DWStorage
Analysis Services ClientsDTS
OLTPSource
DTS DWStorage
Analysis Services
ClientsDTS ClientInterfaces
Client Interfaces
APIs:
Low level: OLE DB for OLAP and OLE DB for Data Mining
High level: ADO-MD
OLTPSource
DTS DWStorage
Analysis Services
ClientsDTS ClientInterfaces
Client Applications
Office
Third-Party Applications
Custom Applications
OL
ED
B fo
r OL
AP
OL
ED
B fo
r DM
MOLAPMOLAPStoreStore
Ap
plicatio
nA
pp
lication
AD
O
AD
O
MD
MD
Pivo
tTab
le P
ivotT
able
Service
Service
Analysis ManagerAnalysis Manager
DSODSO
SQL ServerSQL ServerDataData
WarehouseWarehouse
OtherOtherOLE DBOLE DB
ProvidersProviders
Analysis Services Architecture
Analysis ServerOLAP
EngineDM
Engine
Analysis Manager
Application for Database Administration
Snap-In to MMC
Decision Support Objects
Analysis ServerAnalysis ServerDSODSO
Analysis ManagerAnalysis Manager Custom Administration InterfaceCustom Administration Interface
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
OLAPServer
Client(Excel 2000, Third-Party Applications)
OfflineCubeFile
ADO-MD / OLEDB for OLAPADO-MD / OLEDB for OLAP
PivotTable Service
PTS Architecture
1) January, February, March 2000 Sales
Query 1) January, February, and March 2000 Sales
ClientClient
ServerServer
2) Quarter 1 2000 Sales
3) Quarter 1 1999 Sales
1) January, February, March 2000 Sales
3) Quarter 1 1999 Sales
Query 2) Quarter 1 2000 Sales
Query 3) Quarter 1 2000 Sales and Quarter 1 1999 Sales
ClientCalculates
Only Quarter 1 1999Needed From
Server
Intelligent Caching
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
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
top related