SQL Server 2005 for Business Intelligence

Overview:

In this instructor-led course, you'll learn to implement an Analysis Services solution in an organization. Learn to use the Analysis Services development tools to create an Analysis Services database and an OLAP cube, and learn to use the Analysis Services management and administrative tools to manage an Analysis Services solution. Supplemental content and our enhanced labs add additional hands-on experience.

This course incorporates material from the Official Microsoft Learning Product (OMLP):
Course 2791: Implementing and Maintaining Microsoft SQL Server 2005 Analysis Services

Description:

1. Introduction to Microsoft SQL Server 2005 Analysis Services

Get an introduction to common analysis scenarios and discover how Analysis
Services provides a powerful platform for multidimensional OLAP solutions and
data mining solutions. Discover the main considerations for installing Analysis
Services.

• 
  Overview of Data Warehousing
• 
  Overview of Data Analysis Solutions
• 
  Overview of SQL Server 2005 Analysis Services
• 
  Installing SQL Server 2005 Analysis Services

2. Creating Multidimensional Analysis Solutions

Examine the development tools you can use to create an Analysis Services
multidimensional analysis solution, and learn to create data sources, data
source views, and cubes.

• 
  Developing Analysis Services Solutions
• 
  Data Sources and Data Source Views
• 
  Creating a Cube

3. Working with Dimensions

Learn to edit dimensions and configure dimensions, attributes, and hierarchies.

• 
  Configuring Dimensions
• 
  Defining Hierarchies
• 
  Sorting and Grouping Attributes

4. Working with Measures and Measure Groups

Learn to edit and configure measures and measure groups.

• 
  Working with Measures
• 
  Working with Measure Groups

5. Querying Multidimensional Analysis Solutions

Discover multidimensional expressions (MDX) and learn to implement calculated
members and named sets in an Analysis Services cube.

• 
  MDX Fundamentals
• 
  Adding Calculations to a Cube

6. Customizing Cube Functionality

Learn to customize a cube by implementing KPIs, actions, perspectives, and
translations.

• 
  Understanding KPIs through Application
• 
  Implementing KPIs
• 
  Implementing Actions
• 
  Implementing Perspectives
• 
  Implementing Translations

7. Deploying and Securing an Analysis Services Database

Learn to deploy an Analysis Services database to a production server and
implement security in an Analysis Services multidimensional solution.

• 
  Deploying an Analysis Services Database
• 
  Securing an Analysis Services Database

8. Maintaining a Multidimensional Solution

Explore the maintenance tasks associated with an Analysis Services solution, and
learn how administrators can use the Analysis Services management tools to
perform them.

• 
  Configuring Processing Settings
• 
  Logging, Monitoring, and Optimizing an Analysis Services Solution
• 
  Backing Up and Restoring an Analysis Services Database

9. Introduction to Data Mining

Discover data mining, and learn to implement data mining structures and models
and validate data model accuracy.

• 
  Overview of Data Mining
• 
  Creating a Data Mining Solution
• 
  Validating Data Mining Models

10. Implementing Proactive Caching

Examine the use and importance of indexes in an OLAP environment, and learn to
implement proactive caching.

• 
  Index Creation, Maintenance, and Tuning
• 
  Proactive Caching Overview
• 
  Benefits of Proactive Caching
• 
  Implementing Proactive Caching

11. Analysis Services with Microsoft Office System

Learn to integrate Analysis Services with Microsoft Office.

• 
  Integration with Microsoft Office
• 
  Example Application

12. SQL Server Integration Services (SSIS)

Discover SSIS basics, and learn how to populate a data warehouse using SSIS.

• 
  Overview of SSIS
• 
  New SSIS Architecture
• 
  SSIS Designer
• 
  Running SSIS Packages
• 
  Populating a Data Warehouse with SSIS

13. Set Up and Use Reporting Services

Learn to set up and use Reporting Services.

• 
  Reporting Services Overview
• 
  Development Environment
• 
  Report Manager
• 
  Sample Reports
• 
  Reporting from a Cube

14. Analysis Services Migration 2000 to 2005

Examine the differences between 2000 and 2005 Analysis Services as well as
strategies for moving to SSAS 2005.

• 
  Comparing 2000 to 2005
• 
  Migration vs. Rewrite

Agenda:

Lab 1: Installing Analysis Services and Designing a Multidimensional Analysis Solution

• Installing Analysis Services
• Data Warehouse Databases
• Dimension and Fact Tables
• Star and Snowflake Schemas

Lab 2: Creating a Multidimensional Analysis Solution

• Creating a Data Source
• Creating and Modifying a Data Source View
• Creating and Modifying a Cube

Lab 3: Defining Dimensions

• Configuring Dimensions
• Defining Relationships and Hierarchies
• Sorting and Grouping Dimension Attributes

Lab 4: Configuring Measures and Measure Groups

• Configuring Measures
• Defining Dimension Usage and Relationships
• Configuring Measure Group Storage

Lab 5: Querying a Cube

• Querying a Cube by Using MDX
• Creating a Calculated Member
• Defining a Named Set

Lab 6: Customizing a Cube

• Implementing a KPI
• Implementing an Action
• Implementing a Perspective
• Implementing a Translation

Lab 7: Deploying and Securing an Analysis Services Database

• Deploying an Analysis Services Database
• Securing an Analysis Services Database

Lab 8: Maintaining an Analysis Services Database

• Configuring Processing
• Implementing Logging and Monitoring
• Backing Up and Restoring an Analysis Services Database

Lab 9: Implementing Data Mining

• Creating a Data Mining Structure
• Adding a Data Mining Model
• Exploring Data Mining Models
• Validating Data Mining Models
• DMX Predictive Queries

Lab 10: Performance, Indexes, and Proactive Caching

• Creating Clustered and Non-Clustered Indexes
• Index Creation Fill Factors
• Database Engine Tuning Advisor
• SQL Profiler
• Implementation of Proactive Caching

Lab 11: Use Analysis Services with Microsoft Office System

• Integration with Microsoft Office
• Example application

Lab 12: SSIS/Populating a Data Warehouse

• SSIS Designer
• Running SSIS Packages
• Running DTS packages in SQL Server 2005
• Migration Wizard - Upgrade DTS packages to SSIS
• Extract, Transform, and Load a Data Warehouse

Lab 13: Set Up and Use Reporting Services (SSRS)

• Setting Up Reporting Services
• Using Report Manager
• Sample Reports
• Report Wizard
• Report Designer
• Reports Using Cubes

Audience:

Information technology (IT) professionals and developers who need to implement analysis solutions by using Microsoft SQL Server 2005 Analysis Services.

Objectives:

• How SQL Server Analysis Services can be used to implement analytical solutions
• Create Analysis Services cubes
• Create multidimensional analysis solutions with SQL Server Analysis Services
• Implement dimensions in an Analysis Services solution
• Implement measures and measure groups in an Analysis Services solution
• Query a multidimensional Analysis Services solution
• Customize an Analysis Services cube
• Deploy and secure an Analysis Services database
• Maintain a multidimensional Analysis Services solution
• Implement a data mining solution
• Utilize Key Performance Indicators (KPIs)
• Populate cubes using SSIS
• Create reports using Reporting Services and cubes
• Integration of Microsoft Office and Analysis Services
• Strategies for migrating from 2000 to 2005