Designing and Implementing OLAP Solutions with Microsoft SQL Server 2000(Microsoft Training Course: 2074) - Microsoft Training Courses London South West UK

Exchange Server

Office (inc Access)

SQL Server

Visual Studio .NET

Windows

Web Development

Visual FoxPro

Programming

Business

Knowledge Management

Certification

Learning Options

Reserve a Place
12 May Bath
19 May London
02 Jun Bath
02 Jun London
07 Jul Bath
14 Jul London
04 Aug Bath
11 Aug London
01 Sep Bath
08 Sep London
06 Oct London
06 Oct Bath
03 Nov London
03 Nov Bath
01 Dec London
01 Dec Bath
05 Jan London
05 Jan Bath
02 Feb London
02 Feb Bath
02 Feb London
02 Feb Bath
02 Feb London
02 Feb Bath

View Other Courses in Same Category

Designing and Implementing OLAP Solutions with Microsoft SQL Server 2000 - (Microsoft Training Course: 2074) - 5 days

Target Audience		Pre-requisites
Purpose		Print 2 Page Flyer
Exams		Price Options

COURSE OUTLINE

Introduction to OLAP and Data Warehousing		Designing Multidimensional Data Marts
Previewing OLAP Using Analysis Services		Understanding Analysis Services Architecture
Setting Up Dimensions		Advanced Dimension Settings
Advanced Data Mart Design Techniques		Cubes and Measures
Creating the Sales Reporting Cube		Virtual Cubes
Storage Optimization		Processing Dimensions and Cubes
Creating Partitions		Implementing Calculations Using MDX
Using Excel as an OLAP Client		Introduction to Data Mining
Analyzing Data with Actions, Drill-Through, and Write-Back		Implementing OLAP Security
Deploying an OLAP Application		Creating the Warehouse Database

Target Audience: This course provides students with the knowledge and skills necessary to design, implement, and deploy OLAP solutions by using Analysis Services Pre-requisites: Before attending this course, students must have the following pre-requisites: Basic understanding of database design, administration, and implementation concepts. Satisfactory level of comfort within the Microsoft Windows 2000 environment Purpose: 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 upon 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 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, drill-through, and write-back 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 upon end-user requirements

Module 1: Introduction to OLAP and Data Warehousing

Why Data Warehousing
Data Marts and Data Warehouses
Intorduction to OLAP
Understanding Multidimensionality
The Microsoft Data Warehouse Solution

Skills

Understand OLAP (online analytical processing) and data warehousing concepts and applications.
Describe characteristics, goals, and applications of a data warehouse.
Explain the relationship between data marts and data warehouses.
Describe reasons for implementing relational and/or multidimensional data marts to meet decision support needs.
Describe tools to manage data warehouse implementations.
Describe components of OLAP databases.

Module 2: Designing Multidimensional Data Marts

Designing a Data Warehouse Strategy
Introducing the Data Warehouse
The Relational Schema Behind the OLAP Database
OLAP and Relational Dimensions
Cubes and Fact Tables

Skills

Design multidimensional data marts by using star and snowflake schemas.
Describe a process for designing data warehouse systems.
Understand how relational dimensions and fact tables relate to OLAP dimensions and cubes.
Determine OLAP dimension elements.
Determine OLAP cube elements.

Module 3: Previewing OLAP Using Analysis Services

Analysis Server Basics
Using OLAP Manager
Understanding the Star Schema Source
Creating the Sales Cube
Building the Sales Cube
Building the Dimensions
Finalizing the Cube
Designing Storage and Processing
Viewing the Results

Skills

Verify that Analysis Server is started.
Create an ODBC data source for the database.
Start Analysis Manager.
Understand the underlying star schema source.
Create a database by using Analysis Manager.
Build dimensions by using the Dimension Wizard.
Design a cube by using the Cube Wizard.
Design storage and process a cube using the Storage Design Wizard.
Browse the cube results.

Module 4: Understanding Analysis Services Architecture

Microsoft Data Warehousing Overview
Analysis Services Architecture
Storage Modes
Partitioning
Dimension Alternatives
Large Dimension Support
Caching and Write-Back
How Databases Are Organized
Other Server Side Elements
Client Architecture
Office 2000 OLAP Components
Data Mining

Skills

Understand the Analysis Server architecture.
Understand the metadata repository.
Know the difference between MOLAP, ROLAP, and HOLAP storage modes.
Understand how Analysis Manager interfaces with the server by using DSO.
Appreciate the benefits of partitioning.
Understand how cubes and databases are organized.
Understand client architecture and the role of PivotTable Services.
Recognize Microsoft Office 2000 OLAP capabilities.

Module 5: Setting Up Dimensions

Understanding Dimension Basics
Private Versus Shared Dimensions
Working with Star Schema Dimensions
Working with Snowflake Dimensions
Working with Time Dimensions
Working with Parent-Child Dimensions
Creating Time Dimensions

Skills

Understand when to use shared and private dimensions.
Open and work with the dimension editor.
Add levels to dimensions.
Create dimensions from star and snowflake schemas.
Define member properties at dimension levels.
Implement time hierarchies and dimensions.
Organize levels within dimensions for drill up and drill down.
Develop parent-child dimensions.

Module 6: Advanced Dimension Settings

Creating Custom Hierarchies
Nuances of Levels
Hierarchies and Dimensions
Understanding Virtual Dimensions
Creating Cube with Financial Accounts
Creating Cube with Large Dimensions
Creating Cube with Forecasting Data
Validating and Optimizing the Cube Structure

Skills

Use the Dimension Editor and Dimension Wizard to build and fine-tune dimensions.
Make use of various dimension properties.
Work with dimension levels and hierarchies.
Create virtual dimensions from member properties.
Create custom member and rollup formulas.
Manage very large, flat dimensions.
Disable levels of a shared dimension.

Module 7: Advanced Data Mart Design Techniques

Sharing Dimensions Among Cubes With Different Granularity
Handling Nulls In the Source Data
Managing Slowly Changing Dimensions
Implementing Summary Fact Tables
Managing Various Dimension Scenarios
Optimization Tuning

Skills

Apply advanced OLAP dimension and cube design techniques.
Share dimensions across cubes with different granularity using relational and multidimensional design techniques.
Handle nulls in the source data using relational and multidimensional design techniques.
Manage slowly changing dimensions using relational and multidimensional design techniques.
Implement summary fact tables.

Module 8: Cubes and Measures

Understanding Cube Basics
Working with Cubes
Working with Measures
Defining Measure Properties
Creating Calculations
Defining Dimension Properties

Skills

Create cubes by using the Cube Editor.
Add and delete measures from a cube.
Add and delete dimensions from a cube.
Set up a measure by using each of the five aggregation functions.
Format measures.
Define an internal measure.
Create simple calculated members.
Administer dimension properties within the Cube Editor.

Module 9: Creating the Sales Reporting Cube

Building the Sales Reporting Cube
Modifying the Sales Reporting Cube

Skills

Create a cube based upon end-user requirements.
Build dimensions given the dimension tables and expected levels.
Use various dimension types.
Use expressions to create dimension member names.
Create measures.
Build simple calculated members.
Design aggregations and process the cube.
Verify cube results by using the Cube Browser.

Module 10: Virtual Cubes

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

Skills

Understand when to use virtual cubes and know their benefits.
Understand the limitations of using virtual cubes.
Know the rules for constructing meaningful virtual cubes.
Build virtual cubes by using the Virtual Cube Wizard.
Define calculated members in virtual cubes by using the Calculated Member Builder.

Module 11: Storage Optimization

Analysis Server Storage
Analysis Server Aggregations
The Storage Design Wizard
Aggregation Details
Usage-Based Optimization
Optimization Tuning

Skills

Explain the pros and cons of the three data storage modes.
Describe how aggregations work.
Use the Storage Design Wizard to set storage design.
Design aggregations for cubes.
Describe the contents of a single aggregation.
Describe the concepts and mechanics of usage-based optimization.
Override aggregation settings per dimension.

Module 12: Processing Dimensions and Cubes

Overview of Schema and Data
Processing Dimensions
Rebuilding Dimensions
Incrementally Updating a Dimension
Processing Cubes
The Full Process
Refreshing a Cube
Incrementally Updating a Cube
Troubleshooting Cube Problems
Optimizing Cube Processing

Skills

Rebuild shared dimensions.
Handle new and deleted members.
Understand the difference between rebuilding and incrementally updating dimensions.
Process a cube using the three methods.
Explain the implications of the three cube processing types.
Perform an incremental data load using a database filter.
See how changes are reflected in OLAP cubes after changing data within the source RDBMS.

Module 13: Creating Partitions

Partitioning Overview
Creating Partitions
Fact Table Considerations
Working with Partitions
Merging Partitions

Skills

Explain the benefits of partitioning.
Describe the pros and cons of portioning source fact tables.
Describe the mechanics of the Partition Wizard.
Explain when to define slices and when to define filters.
Describe the purpose and mechanics of merging partitions.

Module 14: Implementing Calculations Using MDX

Understanding Calculated Members
Defining Calculated Members
Members, Tuples, and Sets
Calculated Members in Non-Measure Dimensions
Using Functions Within Calculated Members
Understanding Solve Order

Skills

Describe how calculated members work.
Describe the impact of calculated members on cube size and performance.
Explain the mechanics of the Calculated Member Builder.
Build simple calculated members.
Understand the importance of calculation solve order.

Module 15: Using Excel as an OLAP Client

Overview of Office 2000 OLAP
Creating an Excel PivotTable
Fine Tuning PivotTables
Working with PivotCharts
Working with Local Cubes
Creating OLAP Enabled Web Pages

Skills

Create a PivotTable from an OLAP cube.
Interact with a PivotTable through pivots, drill-downs, and filters.
Perform PivotTable formatting.
Create PivotCharts.
Create local cube files.
Create Web pages containing Pivot web components.

Module 16: Introduction to Data Mining

Understanding Data Mining
Creating A Decision Tree Model Using OLAP Data
Creating a Decision Tree Model Using Relational Data
Editing an Existing Model
Creating a Clustering Model Using OLAP Data
Creating a Clustering Model Using Relational Data

Skills

Define data mining.
Understand how data mining fits within OLAP and the Microsoft data warehousing framework.
Describe the decision tree and clustering algorithms.
Use data mining to discover data patterns.
Segment data by using data mining.
Create a data mining model using the decision tree algorithm.
Edit an existing model.
Explore the decision tree and look for predictable indicators in the results.

Module 17: Analyzing Data with Actions, Drill-Through, and Write-Back

Understanding Actions
Creating Actions
Drill-Through Fundamentals
Enabling Drill-Through
Cube Write-Back

Skills

Create and manage actions.
Invoke an action that was already created.
Enable cube drill-through.
Understand the mechanics of cube drill-through.
Set up a cube for write-back.

Module 18: Implementing OLAP Security

Analysis Services Security Overview
Using Windows 2000 Security
Managing Roles
Using Virtual Cubes for Security
Defining Dimension Security
Administering Cell Level Security

Skills

Understand how Analysis Services security is linked to Windows 2000 security.
Add a security role to a database via the Analysis Manager.
Assign roles to a cube.
Implement dimension security.
Develop cell-level security by using simple MDX.

Module 19: Deploying an OLAP Application

DTS Overview
Executing and Scheduling Packages
Analysis Services Processing Task
Database Migration and Disaster Recovery

Skills

Describe the role of Data Transformation Services (DTS) within OLAP applications.
Create a DTS Package.
Define an Analysis Services processing task.
Schedule the processing of an OLAP dimension or cube.
Move from testing to production environments.
Perform disaster recovery on OLAP databases.

Module 20: Creating the Warehouse Database

Building the Warehouse Cube
Building the Sales Cube
Building the Warehouse and Sales Virtual Cube
Deploying the Warehouse and Sales Cubes

Skills

Create cubes and virtual cubes based upon end-user requirements.
Build dimensions given the dimension tables and expected levels.
Create partitions by using different fact tables.
Use various dimension types.
Build calculated members.

Microsoft recommends this course in preparation for the following exams:

** Discontinued 30/06/04 ** Designing and Implementing Data Warehouses with Microsoft SQL Server 7.0

Price Options ex VAT:

Classroom Training ?	Distance Learning ?	eLearning Options ?	Book Learning ?
£ 1550 (€2202)	£ 995.00 (€1413)	-	No Books Supported for Course at present
	Call Free on 0800 169 1890 Print 2 Page Flyer Last Modified 01 May 2008