Designing and Implementing a Data Warehouse w/ SQL 7.0
Class #1502

Table of Contents

Introduction
At Course Completion
Microsoft Certified Professional Exams
Prerequisites
Course Materials and Software
Course Outline

Introduction

This course provides students with the technical skills required to plan, implement, and maintain a data warehouse using Microsoft® SQL Server™ client/server database management system version 7.0.

At Course Completion

At the end of the course, students will be able to understand:

New technologies included in Microsoft SQL Server 7.0
Online analytical processing (OLAP) server capabilities for multidimensional analysis
Data Transformation Services (DTS) for data marts using SQL Server 7.0
Third-party products for use with SQL Server 7.0
Gathering requirements from the start of a data warehouse project
Maintenance of a data warehouse project

Microsoft Certified Professional Exams

This course will help the student prepare for the following Microsoft Certified Professional exam(s):

Exam 70-019: Designing and Implementing Data Warehouses with Microsoft SQL Server 7.0

Prerequisites

Before beginning this course, students should be able to:

Be familiar with Microsoft SQL Server 7.0
Complete course 832, System Administration for Microsoft SQL Server 7.0, or course 833, Implementing a Database on Microsoft SQL Server 7.0
Have knowledge of Transact-SQL usage in the development of online transaction processing (OLTP) systems
Have basic understanding of programming principles (especially experience with a scripting language like Microsoft Visual Basic® Scripting Edition or JScript® development software)
Understand basic database design, administration, and implementation concepts

The course materials, lectures, and lab exercises are in English. To benefit fully from our instruction, students need an understanding of the English language and completion of the prerequisites.

Course Materials and Software

The course workbook and lab book are yours to keep.

You will be provided with the following software for use in the classroom:

Microsoft Windows NT® Server version 4.0
Microsoft SQL Server version 7.0
Microsoft Internet Explorer version 4.01
Microsoft Office 2000 Beta 2

Day 1

Module 1: Introduction to Data Warehousing

Topics:

What is a data warehouse?
What is a data mart?
Why build a data warehouse
What are some business scenarios that would require a data warehouse?

Lab:

Data warehouse fundamentals

Skills:

Students will be able to:

Explain a data warehouse.
Explain a data mart.
Describe some of the reasons to create a data warehouse.
Describe some of the Microsoft SQL Server 7.0 tools for data warehousing.
Discuss some potential data warehousing scenarios.

Module 2: Gathering User and Customer Requirements

Topics:

System perspectives
Analytical process lifestyle
Requirement gathering process
Opportunity evaluation phase
Management checkpoint
Requirement definition and architecture analysis phase

Lab:

Collecting requirements from interviews and reports

Skills:

Students will be able to:

Compare system architectures.
Describe and detail the steps involved in analyzing and implementing data warehousing.
Identify the appropriate technical requirements for a particular business scenario.

Day 2

Module 3: OLAP Design Techniques

Topics:

Design of an online transaction processing system
Understanding a data warehouse and a data mart
Designing and building a data warehouse and a data mart
Designing and processing aggregations

Lab:

Data warehouse and data mart design

Skills:

Students will be able to:

Describe the design characteristics of transaction processing systems and analytical processing systems.
Discuss methodologies for designing dimensional schemas.
Describe the design characteristics of aggregate schemas.

Module 4: Microsoft Data Warehouse Framework

Topics:

Zero administration server
Managing remote servers from one desktop
Profiler and Index Tuning Wizard
Graphical Showplan
Replication
Backup and Restore enhancements
Query Processor enhancements

Skills:

Students will be able to:

Describe data warehousing frameworks and the approach that Microsoft recommends.
Describe the data store component of the Microsoft data warehousing framework.
Describe Data Transformation Services.
Describe the features of Microsoft SQL Server OLAP Services.
Describe the function of Microsoft ActiveX® Data Objects (ADO) and ADO for Multidimensional Data (ADO MD).
Describe Microsoft English Query.
Describe Microsoft Repository.
Describe the data warehousing features in SQL Server 7.0.

Module 5: Data Transformation Services

Topics:

Using joins to combine data from multiple tables
Manipulating a result set
Using subqueries
Modifying data with joins or subqueries
Querying a remote Microsoft SQL Server-based computer

Lab:

Data Transformation Services

Skills:

Students will be able to:

Describe the overall purpose of DTS.
Describe the DTS components.
Describe data sources and targets available to DTS.
Validate, migrate, and transform data.
Describe the basic architecture of DTS.
Describe the objects used to make a DTS packages.
Build, store, and execute DTS packages.

Module 6: Replication

Topics:

Distributed data overview
Microsoft SQL Server 7.0 replication

Lab:

Replication

Skills:

Students will be able to:

Describe the mechanisms for distributing data.
Describe the replication architecture used in SQL Server.
Determine the best replication topology for a given situation.
Determine which type of replication to use in different data warehousing installations.

Day 3

Module 7: Microsoft SQL Server OLAP Services

Topics:

What are Decision Support Services?
Building dimensions
What is a data cube?
Partitions

Lab:

Microsoft OLAP Services

Skills:

Students will be able to:

Describe SQL Server OLAP Services.
Describe online analytical processing.
Describe the functionality of the OLAP Manager.
Describe and build dimensions.
Describe a data cube.
Build data cubes using Cube Wizard and Cube Editor.
Describe the different cube storage formats.
Describe and calculate aggregations.
Describe partitions and how they affect performance.

Day 4

Module 8: PivotTable Service

Topics:

Microsoft SQL Server OLAP Services architecture
What is PivotTable® Service?
OLAP Services Client/Server Smart Cache
Client/server cache: The basics
Client/server cache: Multiple users
End-user tools
Local data cube persistence

Lab:

Browsing cube data using Microsoft Office 2000

Skills:

Students will be able to:

Define the terminology used to describe multidimensional data structures.
Describe multidimensional schemas.
Use the multidimensional extensions in SQL Server 7.0 to query a multidimensional data store.
Access multidimensional data stores using ActiveX Data Objects.

Module 9: Building Business Analysis Applications

Topics:

OLE DB 2.0 solution with extensions for OLAP
Accessing multidimensional data
ActiveX Data Objects for Multidimensional Data
Creating a local cube file for offline use
Multidimensional Extensions (MDX) for OLE DB for OLAP
How to create a local cube

Labs:

Using MDX
Accessing data using ADO MD
Creating a local cube using ADO

Skills:

Students will be able to:

Review the architecture of the PivotTable Service.
Create and work with in-memory data and query caching.
Create and work with local cube persistence.
Leverage Microsoft SQL Server OLAP Services to avoid PivotTable memory constraints.

Module 10: Microsoft English Query

Topics:

Microsoft English Query
English Query design environment
Creating an English Query application
Deploying an English Query application

Lab:

English Query

Skills:

Students will be able to:

Define the purpose of English Query.
Discuss the components of an English Query application.
Create an English Query application.
Test an English Query application.
Deploy an English Query application in a Web page, Microsoft Visual Basic, or Microsoft Visual C++® project.

Day 5

Module 11: Microsoft Repository

Topics:

Microsoft data warehousing framework
Data warehouse metadata
What is Microsoft Repository?
Why Microsoft Repository is useful
Microsoft Repository architecture
Microsoft Repository objects
Information models
Defining metadata
Information models for data warehousing

Lab:

Showing cube data lineage in Microsoft Repository

Skills:

Students will be able to:

Understand Microsoft Repository.
Identify the parts of Microsoft Repository.
Understand objects and types of models in Microsoft Repository.
Save a data transformation package.

Module 12: Managing Microsoft SQL Server Data Warehouses

Topics:

Resource management
Database management
Resource maintenance
Performance tuning

Lab:

Managing SQL Server data warehouses

Skills:

Students will be able to:

Describe how SQL Server 7.0 dynamically manages memory and space, and the server resources that you can manage.
Describe SQL Server database security features and how they work with the Microsoft Windows NT security environment, locally and in the enterprise.
Manage specific SQL Server database operations using wizards.
Describe how to back up and restore SQL Server databases, how to automate administrative tasks, and the available maintenance wizards.
Describe the factors that affect performance, how to tune a server configuration, and the tools available for monitoring and tuning performance.