Course Info

MS2074 - Designing and Implementing OLAP Solutions Using Microsoft SQL Server 2000

Categories SQL Training, MCSE Training, Database Training, MCDBA Training, Microsoft Certification Training, Microsoft SQL Server Training

Duration 5 days (35 hours)

Pre-requisites
Before attending this course, students must have a basic understanding of database design, administration, and implementation concepts and display a satisfactory level of comfort within the Microsoft Windows® 2000 environment.

Courseware MS2074-MS (300 pages)

Price $1975

Notes
This course covers topics students need to know to prepare for the following Microsoft exam:
Exam 70-019: Designing and Implementing Data Warehouses with Microsoft SQL Server™ 7.0 (MCSE and MCDBA elective)

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. At the end of the course, students will be able to 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 change; 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) and create cubes and virtual cubes based on end-user requirements.

Module 1: Introduction to Data Warehousing and OLAP
      Introducing Data Warehousing
      Defining OLAP Solutions
      Understanding Data Warehouse Design
      Understanding OLAP Models
      Applying OLAP Cubes
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
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
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
Module 5: Using Advanced Dimension Settings
      Working with Levels and Hierarchies
      Working with Time Dimensions
      Creating Custom Rollups
      Introducing Member Properties
      Understanding Virtual Dimensions
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
Module 7: Case Study - Creating the Store Expense Cube
      Building the Store Expense Cube
      Updating the Store Expense Cube
Module 8: Managing Storage and Optimization
      Analysis Server Cube Storage
      The Storage Design Wizard
      Analysis Server Aggregations
      Usage-Based Optimization
      Optimization Tuning
Module 9: Processing Dimensions and Cubes
      Introducing Dimension and Cube Processing
      Processing Dimensions
      Processing Cubes
      Optimizing Cube Processing
      Troubleshooting Cube Processing
Module 10: Managing Partitions
      Introducing Partitions
      Creating Partitions
      Using Advanced Settings
      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
Module 12: Working with Virtual Cubes
      Understanding Virtual Cubes
      Obtaining Logical Results
      Building a Virtual Cube
      Creating Calculated Members
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
Module 14: Using Actions, Drillthrough, and Writeback
      Creating Actions
      Performing Drillthrough
      Understanding Writeback
Module 15: Implementing Security
      Introducing Analysis Services Security
      Understanding Administrator Security
      Helping Protect User Authentication
      Understanding Database Roles
      Implementing 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
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
Module 18: Case Study - Working with the Foodmart Database
      Building the Warehouse Cube
      Building the Sales Cube
      Building the Warehouse and Sales Virtual Cube