Use your VPA
	Live@DL

478

Designing and Implementing OLAP Solutions Using Microsoft SQL Server 2000

Introduction

Return to top

At Course Completion

• Define the term OLAP and its role within data warehousing.
• Design multidimensional data marts by using star and snowflake schemas.
• Recognize the fundamental components of a cube.
• Understand the architecture of Analysis Services.
• Create dimensions from relational dimension tables.
• Understand the many types of dimensions.
• Utilize various dimension properties and settings.
• Design OLAP dimensions based on underlying source data.
• Create cubes by using the Cube Wizard and Cube Editor.
• Create and manipulate measures.
• Develop and understand virtual cubes.
• Design cube storage and aggregations.
• Update dimensions and cubes when source data changes.
• Optimize the processing of dimensions and cubes.
• Create partitions within cubes.
• Implement simple calculations by using multidimensional expressions (MDX) and calculated members.
• Use Microsoft Excel 2000 as an OLAP front-end application.
• Understand how data mining fits within OLAP and the Microsoft data warehousing framework.
• Employ actions, drillthrough, and writeback for data analysis.
• Design and implement cube and dimension security.
• Automate the processing of dimensions and cubes through Data Transformation Services (DTS).
• Create cubes and virtual cubes based on end-user requirements.

Prerequisites

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

Return to top

Microsoft Certified Professional Exams

• There is no MCP exam associated with this course

Student Materials

• Microsoft SQL Server 2000
• Microsoft Excel 2000

Course Outline

Module 1: Introduction to Data Warehousing and OLAP

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

• Describing characteristics, goals, and applications of a data warehouse.
• Understanding the need and use for OLAP solutions.
• Describing data warehouse design.
• Understanding the reasons for implementing OLAP models and describing their components.
• Visualizing a multidimensional database.

Module 2: Introducing Analysis Manager Wizards

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

• Describing Analysis Services components.
• Navigating through the basic interfaces of Analysis Manager.
• Preparing to create a cube by reviewing data sources and initiating the Cube Wizard.
• Creating an OLAP cube by using the Cube and Dimension Wizards.
• Processing a cube.

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

• Describing the components of the Microsoft data warehouse strategy.
• Understanding the Analysis Services components.
• Describing the function of the Microsoft Metadata Repository.
• Explaining the basic differences between the three storage modes for OLAP cubes-multidimensional OLAP (MOLAP), relational OLAP (ROLAP), and hybrid OLAP (HOLAP).
• Understanding client architecture and the role of the Microsoft PivotTable Service.
• Recognizing Microsoft Office 2000 OLAP capabilities.

Module 4: 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

• Understanding dimension fundamentals.
• Knowing when to use shared and private dimensions.
• Describing the characteristics of standard dimensions.
• Adding level properties to dimensions.
• Developing parent-child dimensions.

Module 5: Using Advanced Dimension Settings

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

• Working with dimension levels and hierarchies.
• Understanding and working with time dimensions.
• Creating custom rollup dimensions.
• Defining member properties at dimension levels.
• Creating virtual dimensions from member properties and member levels.

Module 6: Working with Cubes and Measures

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

• Defining the required components of cubes.
• Creating cubes by using the Cube Editor.
• Describing the characteristics of measures.
• Assigning properties to measures.
• Modifying cube properties by using the Cube Editor.
• Disabling levels of shared dimensions.

Module 7: Case Study - Creating the Store Expense Cube

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

• Creating a cube based on user requirements.
• Updating dimensions and adding new dimensions to a cube.

Module 8: Managing Storage and Optimization

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

• Explaining the advantages and disadvantages of the three data storage models.
• Using the Storage Design Wizard to set storage design.
• Describing how aggregations work and designing aggregations for cubes.
• Describing the concepts and mechanics of usage-based optimization.
• Overriding aggregation settings per dimension.

Module 9: Processing Dimensions and Cubes

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

• Understanding the difference between OLAP schema and data.
• Processing dimensions.
• Performing the three types of cube processes.
• Obtimizing cube processing.
• Troubleshooting cube processing.

Module 10: Managing Partitions

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

• Explaining the benefits of partitioning.
• Describing the mechanics of the Partition Wizard
• Explaining when to define slices and when to define filters.
• Describing the purpose and mechanics of merging partitions.

Module 11: 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

• Describing how calculated members work.
• Explaining the mechanics of the Calculated Member Builder and creating calculated members.
• Creating calculated members in non-Measure dimensions.
• Understanding the use of functions in calculated members.
• Understanding other calculation methods in Analysis Services.
• Understanding the importance of Solve Order to generate accurate results.

Module 12: Working with Virtual Cubes

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

• Understanding when to use virtual cubes and knowing their benefits.
• Knowing the rules for constructing meaningful virtual cubes.
• Building virtual cubes by using the Virtual Cube Wizard.
• Defining calculated members in virtual cubes by using the Calculated Member Builder.

Module 13: Using Excel as an OLAP Client

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

• Understanding the various Microsoft Office 2000 OLAP features.
• Creating a PivotTable from an OLAP cube.
• Creating PivotCharts
• Creating local cube files
• Creating a Web page containing Pivot Web components.

Module 14: Using Actions, Drillthrough, and Writeback

• Creating Actions
• Performing Drillthrough
• Understanding Writeback

• Creating and viewing actions.
• Implementing and testing drillthrough.
• Understanding the applications for cube writeback.

Module 15: Implementing Security

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

• Understanding the uses of security in Analysis Services.
• Explaining adminstrator security.
• Describing authentication methods.
• Assigning database roles.
• Applying dimension security.
• Managing cube roles.

Module 16: Deploying an OLAP Solution

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

• Describing the role of Data Transformation Services (DTS).
• Creating a DTS package.
• Defining an Analysis Services processing task.
• Copying, archiving, and restoring OLAP databases.

Module 17: Introduction to Data Mining

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

• Describing data mining characteristics, applications, and modeling techniques.
• Describing the process of training a model.
• Using the OLAP Mining Model Wizard to edit, process, and explore the decision trees.
• Analyzing relational data relationships in the dependency network browser.
• Describing the steps required to build a clustering model by using OLAP data.

Module 18: Case Study - Working with the Foodmart Database

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

• Creating a cube based on user requirements.
• Creating another cube with different dimensions and measures.
• Building a virtual cube.