Introduction

Contents

Introduction 1
Course Materials 2
Prerequisites 3
Course Outline 4
Microsoft Certified Professional Program 8
Facilities 10
Information in this document, including URL and other Internet Web site references, is subject to
change without notice. Unless otherwise noted, the example companies, organizations, products,
people and events depicted herein are fictitious and no association with any real company,
organization, product, person or event is intended or should be inferred. Complying with all
applicable copyright laws is the responsibility of the user. Without limiting the rights under
copyright, no part of this document may be reproduced, stored in or introduced into a retrieval
system, or transmitted in any form or by any means (electronic, mechanical, photocopying,
recording, or otherwise), or for any purpose, without the express written permission of Microsoft
Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual
property rights covering subject matter in this document. Except as expressly provided in any
written license agreement from Microsoft, the furnishing of this document does not give you any
license to these patents, trademarks, copyrights, or other intellectual property.

 2001 Microsoft Corporation. All rights reserved.

Microsoft, MS-DOS, Windows, Windows NT, ActiveX, BackOffice, FoxPro, JScript, MS-DOS,
PivotChart, PivotTable, PowerPoint, Visual Basic, Visual C++, and Visual Studio are either
registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries.

© 2001 OLAP Train, LLC. All Rights Reserved. Developed by OLAP Train LLC, for Microsoft
Corporation and distributed under license.

Other product and company names mentioned herein may be the trademarks of their respective
owners.
 Introduction iii

Instructor Notes
Presentation: The Introduction module provides students with an overview of the course
30 Minutes content, materials, and logistics for Course 2074A, Designing and
Implementing OLAP Solutions with Microsoft SQL Server 2000.

Course Materials and Preparation

Required Materials
To teach this course, you need the following materials:

Delivery Guide

Trainer Materials compact disc

Preparation Tasks
To prepare for this course, you must:
• Complete the Course Preparation Checklist that is included with the trainer
course materials.
iv Introduction

Module Strategy
Use the following strategy to present this module:

Course 2074A: Designing and Implementing OLAP Solutions with
Microsoft SQL Server 2000.
Show the slide that displays the course number and course title.

Introduction
Welcome students to the course and introduce yourself. Provide a brief
overview of your background to establish credibility.
Have students introduce themselves and provide their backgrounds, product
experience, and expectations of the course.
Record student expectations on a white board or flip chart that you can
reference later in class.

Prerequisites
Provide the students with the list of prerequisites that they should have met
before taking this course. This is an opportunity for you to identify students
who may not have the appropriate background or experience to attend this
course.

Course Outline
Provide an overview of each module and what students will learn.
Explain how this course will meet students’ expectations by relating the
information covered in individual modules to their expectations.

Microsoft Certified Professional Program
Inform students about the Microsoft Certified Professional (MCP) program
and the various certification options.

Facilities
Explain the facility information for the training site.
 Introduction 1

Introduction
Topic Objective
To introduce yourself,
establish credibility, meet
Name
students, and set student
expectations for the course.
Company Affiliation
Lead-in
Title/Function
Good morning. Welcome to
Designing and Implementing
Job Responsibility
OLAP Solutions Using
Microsoft SQL Server 2000.
Database Design, Administration, and Implementation
Experience
My name is...

Analysis Services or OLAP Experience

Data Warehousing Experience

Expectations for the Course

*****************************ILLEGAL FOR NON-TRAINER USE******************************

Introduce yourself.

Provide a brief overview of

your background to
establish credibility as an
online analytical processing
(OLAP) instructor.

Ask students to introduce

themselves, addressing the
bulleted items on the slide.
Delivery Tip
As students introduce
themselves, use a white
board or flip chart to record
their expectations of the
course.
2 Introduction

Course Materials
Topic Objective
To identify and describe the
course materials.
Lead-in
Name Card
We have provided
Student Workbook
everything you need for this
course. You will find the
Student Materials Compact Disc
following materials at your
desk...
Course Evaluation

*****************************ILLEGAL FOR NON-TRAINER USE******************************

Describe the contents of the The following materials are included with your kit:
student workbook and the
Student Materials compact
Name card. Write your name on both sides of the name card.
disc.
Student workbook. The student workbook contains the material covered in
class, in addition to the hands-on lab exercises.
Have students write their
names on both sides of the
Student Materials compact disc. The Student Materials compact disc
name card. contains the Web page that provides students with links to resources
pertaining to this course, including additional readings, review and lab
Tell students where they answers, lab files, multimedia presentations, and course-related Web sites.
can send comments with
feedback on the course.
Note To open the Web page, insert the Student Materials compact disc into
Delivery Tip the CD-ROM drive and then, in the root directory of the compact disc,
Demonstrate how to open double-click Default.htm.
the Web page provided on
the Student Materials
compact disc. On the
Course evaluation. To provide feedback on the course, training facility, and
Trainer Materials compact instructor, you will have the opportunity to complete an online evaluation
disc, double-click near the end of the course.
Default.htm in the
To provide additional comments or inquire about the Microsoft Certified
StudntCD folder.
Professional program, send e-mail to mcphelp@microsoft.com.
 Introduction 3

Prerequisites
Topic Objective
To present and describe the
prerequisites for this course.
Lead-in
Understanding of Basic Database Design,
The following prerequisite Administration, and Implementation Concepts
knowledge is needed for this
course.

Experience with Microsoft Windows 2000

*****************************ILLEGAL FOR NON-TRAINER USE******************************

This course requires that you meet the following prerequisites:

Understanding of basic database design, administration, and implementation
concepts.

Experience with Microsoft Windows® 2000.
4 Introduction

Course Outline
Topic Objective
To provide an overview of
each module and what
Module 1: Introduction to Data Warehousing and OLAP
students will learn.
Module 2: Introducing Analysis Manager Wizards
Lead-in
Module 3: Understanding Analysis Services Architecture
In this course, we will
cover...
Module 4: Building Dimensions Using the Dimension Editor

Module 5: Using Advanced Dimension Settings

Module 6: Working with Cubes and Measures

Module 7: Case Study—Creating the Store Expense Cube

Module 8: Managing Storage and Optimization

Module 9: Processing Dimensions and Cubes

*****************************ILLEGAL FOR NON-TRAINER USE******************************

Module 1, “Introduction to Data Warehousing and OLAP,” introduces you to
data warehousing and online analytical processing (OLAP). After completing
this module, you will be able to describe characteristics, goals, and applications
Briefly describe each of a data warehouse, explain the need and use for OLAP solutions, and describe
module. data warehouse design. You also will be able to explain the reasons for
implementing OLAP models, describe their components, and visualize a
As you describe each multidimensional database.
module, acknowledge any
information that will meet Module 2, “Introducing Analysis Manager Wizards,” gives you a
the student expectations comprehensive introduction to cube building fundamentals by using the
that you recorded earlier. Analysis Manager wizards. After completing this module, you will be able to
describe Microsoft SQL Server 2000 Analysis Server components, navigate
through the basic interfaces of Analysis Manager, and prepare to create a cube
by reviewing data sources and initiating the Cube Wizard. You also will be able
to create an OLAP cube by using the Cube Wizard and the Dimension Wizard,
process the cube, and browse the cube data and metadata by using the Analysis
Manager browser.
Module 3, “Understanding Analysis Services Architecture,” provides an
architectural foundation for the various system components. After completing
this module, you will be able to describe the components of the Microsoft data
warehousing strategy, the SQL Server Analysis Server architecture, and the
function of the metadata repository. You also will be able to explain the basic
differences between the three storage modes for OLAP cubes, describe client
architecture and the role of Microsoft PivotTable® Service (PTS), and recognize
Microsoft Office 2000 OLAP capabilities.
Module 4, “Building Dimensions Using the Dimension Editor,” teaches you
about the Dimension Editor and how to use it to create and manage dimensions.
After completing this module, you will be able to describe dimension
fundamentals, explain when to use shared and private dimensions, and describe
the characteristics of standard dimensions. You also will be able to add level
properties to dimensions and develop parent-child dimensions.
 Introduction 5

Module 5, “Using Advanced Dimension Settings,” consists of a solutions-

oriented discussion of various advanced dimension settings and methods used
to develop OLAP dimensions and cubes. After completing this module, you
will be able to work with dimension levels and hierarchies, understand and
work with time dimensions, and create custom rollup dimensions. You also will
be able to define member properties at dimension levels and create virtual
dimensions from member properties and member levels.
Module 6, “Working with Cubes and Measures,” explains how to use the Cube
Editor to create and manipulate cubes, add measures and dimensions, and use
properties to enhance cubes. After completing this module, you will be able to
define the required components of cubes, create cubes by using the Cube
Editor, and describe the characteristics of measures. You also will be able to
assign properties to measures, modify cube properties by using the Cube Editor,
and disable levels of shared dimensions.
Module 7, “Case Study—Creating the Store Expense Cube,” allows you to
create an OLAP cube and several dimensions from start to finish. After
completing this module, you will be able to create a cube based on end-user
requirements, update dimensions, and add new dimensions to a cube.
Module 8, “Managing Storage and Optimization,” explains that designing the
storage mode and aggregations for a cube is one of the most crucial steps in
cube development. After completing this module, you will be able to explain
the advantages and disadvantages of the three data storage modes, use the
Storage Design Wizard to set storage design, and describe how aggregations
work. You also will be able to design aggregations for cubes, describe the
concepts and mechanics of usage-based optimization, and override aggregation
settings per dimension.
Module 9, “Processing Dimensions and Cubes,” describes how to manage
dimension and cube processing with Analysis Services. After completing this
module, you will be able to understand the difference between OLAP schema
and data, process dimensions, and perform the three types of cube processes.
You also will be able to optimize and troubleshoot cube processing.
6 Introduction

Course Outline (continued)

Module 10: Managing Partitions

Module 11: Implementing Calculations Using MDX

Module 12: Working with Virtual Cubes

Module 13: Using Excel as an OLAP Client

Module 14: Using Actions, Drillthrough, and Writeback

Module 15: Implementing Security

Module 16: Deploying an OLAP Solution

Module 17: Introduction to Data Mining

Module 18: Case Study—Working with the Foodmart Database

*****************************ILLEGAL FOR NON-TRAINER USE******************************

Module 10, “Managing Partitions,” explains how to create partitions, how to
define slices and filters, and the benefits of using partitions in cubes to improve
scalability. At the end of this module, you will be able to explain the benefits of
partitioning, describe the mechanics of the Partition Wizard, explain when to
define slices and when to define filters, and describe the purpose and mechanics
of merging partitions.
Module 11, “Implementing Calculations Using MDX,” allows you to learn
about calculated members and how to use them in OLAP cubes to enhance
analysis. After completing this module, you will be able to describe how
calculated members work, explain the mechanics of the Calculated Member
Builder, create calculated members in Measure dimensions, and create
calculated members in non-Measure dimensions. You also will be able to
describe the use of functions in calculated members, explain other calculation
methods in Analysis Services, and describe the importance of Solve Order to
generate accurate results.
Module 12, “Working with Virtual Cubes,” defines virtual cubes and explains
when to use them and the mechanics of how to build them in Analysis Services.
After completing this module, you will be able to describe when to use virtual
cubes, explain the rules for constructing meaningful virtual cubes, build virtual
cubes by using the Virtual Cube Wizard, and define calculated members in
virtual cubes by using the Calculated Member Builder.
Module 13, “Using Excel as an OLAP Client,” provides an overview of
Microsoft Excel 2000 OLAP features and gives you the opportunity to create
and manipulate the various Office 2000 interfaces. After completing this
module, you will be able to describe the various Microsoft Office 2000 OLAP
features, create a PivotTable from an OLAP cube, create Microsoft
PivotCharts®, create local cube files, and create a Web page containing Pivot
Web components.
 Introduction 7

Module 14, “Using Actions, Drillthrough, and Writeback,” teaches you how to
implement three important features: actions, drillthrough, and writeback. At the
end of this module, you will be able to create and view actions, implement and
test drillthrough, and describe the applications for cube writeback.
Module 15, “Implementing Security,” teaches you how to implement security in
Analysis Services. At the end of this module, you will be able to understand the
use of security, explain administrator security, and describe authentication
methods. You also will be able to assign database roles, apply dimension
security, and manage cube roles.
Module 16, “Deploying an OLAP Solution,” explains how to automate cube
processing, copy server objects, and move databases from testing to production
environments. After completing this module, you will be able to describe the
role of Data Transformation Services (DTS), create a DTS package, and define
an Analysis Services processing task. You also will be able to copy, archive,
and restore OLAP databases.
Module 17, “Introduction to Data Mining,” introduces you to data mining
principles and applications while you explore the wizard-driven interface for
creating data mining models. After completing this module, you will be able to
describe data mining characteristics, applications, and modeling techniques,
describe the process of training your model, and use the OLAP Mining Model
Wizard to edit, process, and explore the decision trees. You also will be able to
analyze relational data relationships in the dependency network browser,
describe the steps required to build a clustering model by using OLAP data, and
use the Relational Mining Model Wizard to edit, process, and browse a
clustering model.
Module 18, “Case Study—Working with the Foodmart Database,” is a case
study covering material from the earlier modules. In this module, you use
various cube and dimension techniques to build cubes from the Foodmart 2000
database by using Analysis Services. After completing this module, you will be
able to create a cube based on user requirements. You also will be able to build
a virtual cube.
8 Introduction

Microsoft Certified Professional Program

Topic Objective
To provide students with
information about the
Microsoft Certified Systems Engineer + Internet
(MCSE + Internet)
Microsoft Certified
Professional Program.
Microsoft Certified Systems Engineer (MCSE)
Lead-in
Microsoft Certified Database Administrator (MCDBA)
The Microsoft Certified
Microsoft Certified Solution Developer (MCSD)
Professional Program
includes these
Microsoft Certified Professional + Site Building
certifications… (MCP + Site Building)

Microsoft Certified Professional + Internet
(MCP + Internet)

Microsoft Certified Professional (MCP)

Microsoft Certified Trainer (MCT)

*****************************ILLEGAL FOR NON-TRAINER USE******************************

The Microsoft Certified Professional program is a leading certification program
that validates your experience and skills to keep you competitive in today's
changing business environment. The following table describes each certification
in more detail.
Certification Description

MCSA on Microsoft The Microsoft Certified Systems Administrator (MCSA) certification is designed for
Windows 2000 professionals who implement, manage, and troubleshoot existing network and system
environments based on Microsoft Windows 2000 platforms, including the Windows
.NET Server family. Implementation responsibilities include installing and configuring
parts of the systems. Management responsibilities include administering and supporting
the systems.
MCSE on Microsoft The Microsoft Certified Systems Engineer (MCSE) credential is the premier
Windows 2000 certification for professionals who analyze the business requirements and design and
implement the infrastructure for business solutions based on the Microsoft Windows
2000 platform and Microsoft server software, including the Windows .NET Server
family. Implementation responsibilities include installing, configuring, and
troubleshooting network systems.
MCSD The Microsoft Certified Solution Developer (MCSD) credential is the premier
certification for professionals who design and develop leading-edge business solutions
with Microsoft development tools, technologies, platforms, and the Microsoft Windows
DNA architecture. The types of applications MCSDs can develop include desktop
applications and multi-user, Web-based, N-tier, and transaction-based applications. The
credential covers job tasks ranging from analyzing business requirements to maintaining
solutions.
MCDBA on Microsoft The Microsoft Certified Database Administrator (MCDBA) credential is the premier
SQL Server 2000 certification for professionals who implement and administer Microsoft SQL Server
databases. The certification is appropriate for individuals who derive physical database
designs, develop logical data models, create physical databases, create data services by
using Transact-SQL, manage and maintain databases, configure and manage security,
monitor and optimize databases, and install and configure SQL Server.
 Introduction 9

(continued)
Certification Description

MCP The Microsoft Certified Professional (MCP) credential is for individuals who have the
skills to successfully implement a Microsoft product or technology as part of a business
solution in an organization. Hands-on experience with the product is necessary to
successfully achieve certification.
MCT Microsoft Certified Trainers (MCTs) demonstrate the instructional and technical skills
that qualify them to deliver Microsoft Official Curriculum through Microsoft Certified
Technical Education Centers (Microsoft CTECs).

Certification Requirements
The certification requirements differ for each certification category and are
specific to the products and job functions addressed by the certification. To
become a Microsoft Certified Professional, you must pass rigorous certification
exams that provide a valid and reliable measure of technical proficiency and
expertise.

For More Information See the Microsoft Training and Certification Web site at
http://www.microsoft.com/traincert/.
You can also send e-mail to mcphelp@microsoft.com if you have specific
certification questions.

Acquiring the Skills Tested by an MCP Exam

Microsoft Official Curriculum (MOC) and MSDN® Training Curriculum can
help you develop the skills that you need to do your job. They also complement
the experience that you gain while working with Microsoft products and
technologies. However, no one-to-one correlation exists between MOC and
MSDN Training courses and MCP exams. Microsoft does not expect or intend
for the courses to be the sole preparation method for passing MCP exams.
Practical product knowledge and experience is also necessary to pass the MCP
exams.
To help prepare for the MCP exams, use the preparation guides that are
available for each exam. Each Exam Preparation Guide contains exam-specific
information, such as a list of the topics on which you will be tested. These
guides are available on the Microsoft Training and Certification Web site at
http://www.microsoft.com/traincert/.
10 Introduction

Facilities
Topic Objective
To inform students of class Class Hours
logistics and rules for the
training site.
Lead-in Building Hours Phones
Before we start, let’s go over
the class logistics.
Parking Messages

Rest Rooms Smoking

Meals Recycling

*****************************ILLEGAL FOR NON-TRAINER USE******************************

Explain the class hours,

extended building hours for
labs, parking, rest room
location, meals, phones,
message posting, and
where smoking is or isn’t
allowed.

Also make sure that the

students are aware of the
recycling program if one is
available.