M2074: Microsoft SQL Server 2000 Designing and Implementing OLAP Solutions

Prerequisites:

Before attending this course, students must have:

- A basic understanding of database design, administration, and implementation concepts.
- A satisfactory level of comfort within the Microsoft Windows 2000 environment.

Course Description:

This course provides students with the knowledge and skills necessary to design, implement, and deploy OLAP solutions by using Microsoft SQL Server 2000™ Analysis Services.

This course includes the following modules:

Introduction to Data Warehousing and OLAP

• Introducing Data Warehousing
• Defining OLAP Solutions
• Understanding Data Warehouse Design
• Understanding OLAP Models
• Applying OLAP Cubes

Introducing Analysis Manager Wizards

• Defining Terms
• Previewing Analysis Manager
• Preparing to Create a Cube
• Building the Sales Cube
• Processing the Cube
• Viewing the Results

Understanding Analysis Services Architecture

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

Building Dimensions Using the Dimension Editor

• Understanding Dimension Basics
• Shared vs. Private Dimensions
• Working with Standard Dimensions
• Basic Level Properties
• Working with Parent-Child Dimensions

Using Advanced Dimension Settings

• Working with Levels and Hierarchies
• Working with Time Dimensions
• Creating Custom Rollups
• Introducing Member Properties
• Understanding Virtual Dimensions

Working with Cubes and Measures

• Introduction to Cubes
• Working with Cubes
• Introduction to Measures
• Working with Measures
• Defining Cube Properties
• Using the Disabled Property

Case Study - Creating the Store Expense Cube

• Building the Store Expense Cube
• Updating the Store Expense Cube

Managing Storage and Optimization

• Analysis Server Cube Storage
• The Storage Design Wizard
• Analysis Server Aggregations
• Usage-Based Optimization
• Optimization Tuning

Processing Dimensions and Cubes

• Introducing Dimension and Cube Processing
• Processing Dimensions
• Processing Cubes
• Optimizing Cube Processing
• Troubleshooting Cube Processing

Managing Partitions

• Introducing Partitions
• Creating Partitions
• Using Advanced Settings
• Merging Partitions

Implementing Calculations Using MDX

• Understanding Calculated Members
• Building Calculated Members
• Creating Non-Measure Calculated Members
• Using Functions Within Calculated Members
• Understanding Other Calculation Methods
• Introducing Solve Order

Working with Virtual Cubes

• Understanding Virtual Cubes
• Obtaining Logical Results
• Building a Virtual Cube
• Creating Calculated Members

Using Excel as an OLAP Client

• Office 2000 OLAP Components
• Using Excel PivotTables
• Using PivotCharts
• Working with Local Cubes
• Creating OLAP-Enabled Web Pages

Using Actions, Drillthrough, and Writeback

• Creating Actions
• Performing Drillthrough
• Understanding Writeback

Implementing Security

• Introducing Analysis Services Security
• Understanding Administrator Security
• Securing User Authentication
• Understanding Database Roles
• Implementing Dimension Security
• Managing Cube Roles

Deploying an OLAP Solution

• Introducing DTS
• Executing and Scheduling Packages
• The Analysis Services Processing Task
• Copying and Archiving OLAP Databases

Introduction to Data Mining

• Introducing Data Mining
• Training a Data Mining Model
• Building a Data Mining Model with OLAP Data
• Browsing the Dependency Network

Case Study - Working with the Foodmart Database

• Building the Warehouse Cube
• Building the Sales Cube
• Building the Warehouse and Sales Virtual Cube