Module 3: Metadata Repository Understanding Analysis Cube Storage - - PDF document

1

Module 3: Understanding Analysis Services Architecture

Overview

Microsoft Data Warehousing Overview Analysis Services Components Metadata Repository Cube Storage Options Client Architecture Office 2000 OLAP Components

Microsoft Data Warehousing Overview

OLTP Source DTS DW Storage Analysis Services Clients OLE DB for OLAP, ADO MD DTS

Data Transformation Services

• Transforming and Moving Data
• Scheduling DTS Tasks
• Automating OLAP Administrative Tasks

OLTP Source

DTS

DW Storage Analysis Services Clients

DTS

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

OLTP Source DTS

DW Storage

Analysis Services Clients DTS

Analysis Services

OLTP Source DTS DW Storage

Analysis Services

Clients DTS

2

OLTP Source DTS DW Storage Analysis Services Clients DTS

Client Interfaces

Client Interfaces

APIs: Low level: OLE DB for OLAP and OLE DB for Data

Mining

High level: ADO-MD

OLTP Source DTS DW Storage Analysis Services

Clients

DTS Client Interfaces

Client Applications

Office 2000 Third-Party Applications Custom Applications

Analysis Services Components

Analysis Services Architecture Analysis Manager Analysis Server Characteristics OLEDB for OLAP OLEDB for OLAP OLEDB for DM OLEDB for DM

MOLAP MOLAP Store Store

Application Application ADO MD ADO MD PivotTable Service PivotTable Service

Analysis Manager Analysis Manager

DSO DSO

SQL Server SQL Server Data Data Warehouse Warehouse Other Other OLE DB OLE DB Providers Providers

Analysis Services Architecture

Analysis Server

OLAP Engine DM Engine

Note: MOLAP = Multidimensional OLAP; DSO = Decision Support Object Note: ADO = Asynchronous Data Objects

Analysis Manager

Application for Database Administration Snap-In to MMC (Microsoft Management Console) Decision Support Objects

Analysis Server DSO Analysis Manager Custom Administration Interface

Analysis Server Characteristics

OLE DB for OLAP Provider OLE DB Provider Windows 2000 and Windows NT Service

3

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

Cube Storage Options

Storage Modes Partitioning Virtual Cubes Linked Cubes Database Architecture Analysis Server Limits

Storage Modes

History Prior Year

MOLAP 35% agg ROLAP 0% agg

Current Year

MOLAP 10% agg

Partitioning Virtual Cubes

Equivalent of RDBMS View Subset of One Cube Combination of Data from Multiple Cubes Virtually No Storage Consumed

Sales Virtual Cube Sales Virtual Cube Actual Actual Budget Budget

Linked Cubes

Linked Cubes Characteristics Are based on source cubes stored on different servers Reference entire source cubes and not subsets Appear as regular cubes Always have ROLAP storage mode with no aggregations Linked Cubes Limitations Can only be created with SQL Server 2000 Enterprise

Edition

Cannot be used to write back cube data Can only be created on Analysis Server with query

access to source cube

4

Database Architecture

Analysis Server Contains One or More Databases Each Database Contains One or More Cubes Each Cube Contains One or More Partitions Each Partition Can Have a Different Storage Mode, a

Different Aggregation Design, and a Different Storage Location

Analysis Server Limits

Limits Items 64 Levels per dimension Unlimited Partitions per cube 65,535 Calculated members per cube 1,024 Measures per cube 128 Dimensions per cube 64 Cubes per virtual cube Unlimited Cubes per database Unlimited Databases per server

Client Architecture

PivotTable Service PTS Architecture Intelligent Caching Multidimensional Expressions Internet Support on Clients

1) January, February, March 2000 Sales

Query 1) January, February, and March 2000 Sales

Client Client Server Server

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 Client Calculates Only Quarter 1 1999 Needed 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

Internet Support on Clients

Uses IIS (Internet Information Server) to Provide

Authentication Over the Internet

Uses HTTP to Pass Through Firewalls Supports Automatic Setup through ASP Requires SQL Server 2000 Enterprise Edition

5

Office 2000 OLAP Components

Excel PivotTables Local Cubes Web Pivot Control

Review

Microsoft Data Warehousing Overview Analysis Services Components Metadata Repository Cube Storage Options Client Architecture Office 2000 OLAP Components