SQL Server 2000 Data Warehousing

Outcomes Upon successful completion students will be able to: Analyzing Business Requirements, Defining the Technical Architecture for a Solution, Developing the Logical Design, Derive Physical Design, Create Data Services and Implement a physical data warehouse and OLAP services Assessment You will be able to measures your ability to design and implement data warehouse solutions by using Microsoft SQL Server 2000 with OLAP Services and Data Transformation Services (DTS) installed, by pre and post course assesments in the form of quizzes and tests. Instructor: Michael Lee is a Senior Trainer and Consultant with Saqqara Technology, Inc, a Microsoft Certified Partner and has over 10 years technology training experience and has published numerous books about SQL Server and Client/Server development. He is a popular speaker and presenter, and is in demand at client sites and conferences all over the US and Europe.Course Outline: Lesson 1: Introduction & Planning Introduction

• Topic Overview
• Transactional vs. Analytical Data Systems
• Data Warehouse Defined
• Data Warehouses vs. Data Marts/OLAP

Dimensional Modeling & Planning

• Entity-Relationship Modeling vs. Dimensional Modeling
• Elements of the Dimensional Model
• The Role of User Requirements
• Identifying Source Data
• Implementing the Data Warehouse Bus

Planning Facts

• What is a fact?
• Measures

Planning Dimensions

• Planning Dimension Tables
• Planning a Hierarchy
• Special Dimensions

Lesson 2: Data Marts Building a Data Mart

• Creating the Database
• Creating the Fact Table
• Creating Dimension Tables
• Creating Constraints and Indexes

Loading the Data Mart

• Using Staging Tables
• Using Transact SQL for Data Migration
• Loading Data using Data Transformation Services (DTS)
• Advanced DTS Issues
• Evaluating a Typical Load Scenario

Maintaining the Data Mart

• Backup and Restoration Considerations
• Handling Changing Facts
• Handling Changing Dimensions
• Securing the Data Mart

Lesson 3: Introduction to OLAP, Cube and Dimension Introduction to OLAP

• OLAP Concepts
• Analysis Services Architecture

Cube Basics

• Cube Basics & Measures
• Dimensions & Hierarchies
• Aggregations
• Using the Analysis Manager

Creating Dimensions

• Defining Terms
• Creating Dimensions with the Dimension Wizard
• Creating Time Dimensions
• Using the Dimension Editor
• Basic Dimension Properties
• Using Member Properties
• Virtual Dimensions
• Multiple Hierarchies

Creating Cubes

• Selecting Facts & Measures
• Choosing Dimensions
• Creating Calculated Measures
• Storage Models
• Processing Dimensions
• Processing Cubes
• Using Cube Properties

Lesson 4: Cubes and Special Features Managing Cube Partitions

• Partitions Concepts
• Creating Cube Partitions
• Merging Partitions

Optimizing Cubes

• Optimizing Aggregations with Storage Models
• Usage-Based Optimizations
• Tuning the Analysis Server

Special Features

• Using Drillthrough
• Creating and Using Actions

Connecting Clients Using OLE DB

• Analysis Application Architecture
• Using Excel as an Analysis Server Client
• Overview of Client Interfaces

Introduction to Data Mining

• Data Mining Basics
• Creating Data Mining Models

Lesson 5: MDX MDX Concepts Overview

• What is MDX?
• Role of MDX in Application Architecture
• Creating Calculated Members Within Cubes Using MDX in Client Applications
• MDX Examples

Creating MDX Expressions

• Understanding the Hierarchy
• Using Brackets
• Identifying Members
• Qualifying Sets of Members
• Creating Calculated Members

Interacting with Cubes

• Anatomy of an MDX Query
• Basic MDX Queries
• Tuple Expressions

Performing Aggregations

• Calculating Data in an MDX Query
• Using Aggregate Functions
• Using Time Set Functions

Using MDX with ADOMD/div>

• Connecting to an Analysis Server
• Executing an MDX Query
• Manipulating Results

Contact Hours: 24