200 likes | 348 Views
Understanding Analysis Services Architecture. DTS. DTS. Analysis Services. DW Storage. OLE DB for OLAP, ADO MD. OLTP Source. Clients. Microsoft Data Warehousing Overview. Transforming and Moving Data Scheduling DTS Tasks Automating OLAP Administrative Tasks.
E N D
DTS DTS Analysis Services DW Storage OLE DBfor OLAP,ADO MD OLTP Source Clients Microsoft Data Warehousing Overview
Transforming and Moving Data Scheduling DTS Tasks Automating OLAP Administrative Tasks Data Transformation Services DTS DTS Analysis Services OLTP Source DW Storage Clients
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 Data Warehouse Storage DW Storage DTS OLTP Source Analysis Services DTS Clients
Analysis Services OLTP Source DW Storage Analysis Services DTS DTS Clients
Client Interfaces • APIs: • Low level: OLE DB for OLAP and OLE DB for Data Mining • High level: ADO-MD Client Interfaces OLTP Source DW Storage Analysis Services DTS DTS Clients
Client Applications • Office • Third-Party Applications • Custom Applications Clients OLTP Source DW Storage Analysis Services Client Interfaces DTS DTS
Analysis Manager OLEDB for DM OLEDB for OLAP SQL Server Data Warehouse DSO PivotTable Service Other OLE DB Providers Analysis Server OLAP Engine DM Engine MOLAP Store ADO MD Analysis Services Architecture Application
Analysis Manager • Application for Database Administration • Snap-In to MMC • Decision Support Objects Custom Administration Interface Analysis Manager Analysis Server DSO
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
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
Client(Excel 2000, Third-Party Applications) ADO-MD / OLEDB for OLAP PivotTable Service OfflineCube File OLAPServer PTS Architecture
Query 1) January, February, and March 2000 Sales Query 2) Quarter 1 2000 Sales Query 3) Quarter 1 2000 Sales and Quarter 1 1999 Sales Intelligent Caching Client Calculates Client 1) January, February, March 2000 Sales 2) Quarter 1 2000 Sales 3) Quarter 1 1999 Sales Only Quarter 1 1999 Needed From Server 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