Introduction
The MCTS: SQL Server 2005 Business Intelligence (BI) Boot
Camp is an instructor led course that focuses on Microsoft
SQL Server Integration Services, Analysis Services, and
Reporting Services. It is an intensive program designed for
IT professionals with solid fundamental SQL experience. The
Boot Camp hours are from 8:00 AM to 8:00 PM, Monday through
Friday.
This course discusses how to use the BI development tools to
create Analysis Services databases, OLAP cubes, Integration
Services packages, Reporting solutions and how to use the
SQL Server 2005 management and administrative tools to
manage Microsoft SQL Server 2005 BI solutions.
The boot camp provides students with the knowledge and the
skills that are required to take Microsoft Exam 70-445
(Microsoft SQL Server 2005 Business Intelligence –
Implementation and Maintenance). Students will receive a
Prometric Exam Voucher and a self-paced test prep software.
The test-prep software provides a realistic simulation of
the Microsoft certification exam to help prepare the student
to apply the knowledge taught in class for the exam.
Prerequisites:
·
Exposure to Data Import/Export Scenarios
·
Conceptual Understanding of OLAP Solutions
·
Exposure to Report Creation (Access, Crystal, Etc,…)
·
Experience with Windows Services
·
Experience with Service Accounts and Permissions
·
Experience with Navigating the Microsoft SQL Server
(SQL Queries, SQL Server Agent, SQL Systems Tables, SQL
Accounts)
Summary Outline:
SQL Server 2005 Integration Services:
·
Developing SQL Server 2005 Integration Services
Solutions
·
Control Flow
·
Data Flow
·
Implementing Logging
·
Debugging and Error Handling
·
Checkpoints and Transactions
·
Deploying Packages
·
Managing and Securing Packages
SQL Server 2005 Analysis Services:
·
Creating Multidimensional Solutions, Dimensions
·
Measures and Measure Groups
·
Querying Multidimensional Analysis Solutions
·
Customizing Cube Functionality
·
Deploying and Securing SQL Server 2005 Analysis
Services
·
Maintaining SQL Server 2005 Analysis Services
·
Intro to Data Mining
SQL Server 2005 Reporting Services:
·
Authoring Basic Reports
·
Enhancing Reports
·
Manipulating Data Sets
·
Report Models
·
Publishing and Executing Reports
·
Using Subscriptions to Distribute Reports
·
Administration of SQL Server 2005 Reporting Services
·
Programming SQL Server 2005 Reporting Services
Detailed Course Outline
Integration Services
Exam Topics covered
·
Configure algorithm parameters SSIS
·
Managing SSIS
·
Configure Server and database roles and permissions
·
Evening exam review using Transcender Test Prep.
Module 1: Introduction to SQL Server 2005 Integration
Services
This module introduces the role that Integration Services
plays in extracting, transforming, and loading data. This
module also describes the tools that you can use to build
and manage Integration Services solutions.
Lessons
·
Overview of Integration Services Solutions
·
Integration Services Tools
Lab 1: Using SQL Server Integration Services
Exercise 1: Using the Import and Export Wizard
Exercise 2: Running an Integration Services Package
After completing this module, students will be able to:
·
Describe Integration Services solutions.
·
Use Integration Services tools.
Module 2: Developing Integration Services Solutions
This module provides an overview of the development tasks
that are involved in creating an Integration Services
package. After completing this module, you will be able to
create a basic package.
Lessons
·
Creating an Integration Services Solution
·
Using Variables
·
Building and Running a Solution
Lab 2: Implementing an Integration Services Solution
Exercise 1: Creating an Integration Services Project
Exercise 2: Implementing a Package
Exercise 3: Building and Running an Integration Services
Project
After completing this module, students will be able to:
·
Create a SQL Server Integration Services solution.
·
Use variables.
·
Build and run a solution.
Module 3: Implementing Control Flow
This module introduces the tasks and precedence constraints
that you can use to implement control flow in an Integration
Services package.
Lessons
·
Control Flow Tasks
·
Control Flow Precedence Constraints
·
Control Flow Containers
Lab 3: Implementing Control Flow
Exercise 1: Creating a Simple Control Flow
Exercise 2: Configuring Precedence Constraints
Exercise 3: Using Containers
After completing this module, students will be able to:
·
Configure control flow tasks.
·
Configure control flow precedence constraints.
·
Configure control flow containers.
Module 4: Implementing Data Flow
This module describes the data flow sources,
transformations, and destinations that you can use to
implement a data flow task in an Integration Services
control flow. It also explains how to use data flow paths to
direct valid and invalid rows through the data flow.
Lessons
·
Data Flow Sources and Destinations
·
Data Flow Transformations
·
Data Flow Paths
Lab 4: Implementing Data Flows
Exercise 1: Transferring Data
Exercise 2: Implementing Transformations
Exercise 3: Using Data Viewers
Exercise 4: Configuring Error Output
After completing this module, students will be able to:
·
Implement data flow sources and destinations.
·
Implement data flow transformations.
·
Implement data flow paths.
Module 5: Implementing Logging
This module discusses how to use logging in an Integration
Services package, and explains how to configure and use
logging providers to generate information about a package’s
execution.
Lessons
·
Overview of Integration Services Logging
·
Implementing Logging
Lab 5: Implementing Logging
Exercise 1: Configuring Logging
Exercise 2: Implementing Custom Logging
After completing this module, students will be able to:
·
Describe Integration Services logging.
·
Implement logging.
Module 6: Debugging and Error Handling
This module describes how to debug Integration Services
packages by using the debugging tools in Business
Intelligence Development Studio. It then explains how to
implement error-handling logic in an Integration Services
package.
Lessons
·
Debugging a Package
·
Implementing Error Handling
Lab 6: Debugging and Error Handling
Exercise 1: Debugging a Package
Exercise 2: Implementing Error Handling
Exercise 3: Controlling Failure Behavior
After completing this module, students will be able to:
·
Debug a package.
·
Implement error handling.
Module 7: Implementing Checkpoints and Transactions
This module explains what checkpoints are and how to
implement them. It then discusses transactions, and
describes how you can implement transactional data access
logic in an Integration Services package.
Lessons
·
Implementing Checkpoints
·
Implementing Transactions
Lab 7: Implementing Checkpoints and Transactions
Exercise 1: Implementing Checkpoints in a Package
Exercise 2: Implementing Transactions in a Package
Exercise 3: Implementing a Native Transaction
After completing this module, students will be able to:
·
Implement checkpoints.
·
Implement transactions.
Module 8: Deploying Packages
This module discusses how to create Package Configurations
and how to deploy Integration Services packages to
production servers.
Lessons
·
Package Configurations
·
Deploying Packages
Lab 8: Deploying Integration Services Packages
Exercise 1: Creating a Package Configuration
Exercise 2: Preparing a Package for Deployment
Exercise 3: Deploying a Package
After completing this module, students will be able to:
·
Implement package configurations.
·
Deploy packages.
Module 9: Managing and Securing Packages
This module describes the management tasks that relate to
Integration Services packages and explains how to perform
those tasks by using the Integration Services management
tools. It also describes how to secure Integration Services
packages.
Lessons
·
Managing Packages
·
Securing Packages
Lab 9: Managing and Securing Packages
Exercise 1: Importing a Package
Exercise 2: Configuring and Executing a Package
Exercise 3: Scheduling a Package
Exercise 4: Securing a Package
After completing this module, students will be able to:
·
Manage packages.
·
Secure packages.
Analysis Services
Exam Topics Covered
·
Developing SSAS Solutions using BIDS
·
Implementing Data Mining using BIDS
·
Configure algorithm parameters SSAS
·
Managing SSAS
·
Configure Server and database roles and permissions
SSAS
·
Evening exam review using Transcender Test Prep.
Module 1: Introduction to Microsoft SQL Server 2005 Analysis
Services
This module introduces common analysis scenarios and
describes how Analysis Services provides a powerful platform
for multidimensional OLAP solutions and data mining
solutions. The module then describes the main considerations
for installing Analysis Services.
Lessons
·
Overview of Data Analysis Solutions
·
Overview of SQL Server 2005 Analysis Services
·
Installing SQL Server 2005 Analysis Services
After completing this module, students will be able to:
·
Describe data analysis solutions.
·
Describe the key features of Microsoft SQL Server
2005 Analysis Services.
·
Install SQL Server 2005 Analysis Services.
Module 2: Creating Multidimensional Analysis Solutions
This module introduces the development tools you can use to
create an Analysis Services multidimensional analysis
solution, and describes how to create data sources, data
source views, and cubes.
Lessons
·
Developing Analysis Services Solutions
·
Data Sources and Data Source Views
·
Creating a Cube
Lab 2: Creating a Multidimensional Analysis Solution
·
Exercise 1: Creating a Data Source
·
Exercise 2: Creating and Modifying a Data Source View
·
Exercise 3: Creating and Modifying a Cube
After completing this module, students will be able to:
·
Develop Analysis Services solutions.
·
Create a data source and a data source view.
·
Create a cube.
Module 3: Working with Dimensions
This module describes how to edit dimensions and to
configure dimensions, attributes, and hierarchies.
Lessons
·
Configuring Dimensions
·
Defining Hierarchies
·
Sorting and Grouping Attributes
Lab 3: Defining Dimensions
·
Exercise 1: Configuring Dimensions
·
Exercise 2: Defining Relationships and Hierarchies
·
Exercise 3: Sorting and Grouping Dimension Attributes
After completing this module, students will be able to:
·
Configure dimensions.
·
Define hierarchies.
·
Sort and group attributes.
Module 4: Working with Measures and Measure Groups
This module explains how to edit and configure measures and
measure groups.
Lessons
·
Working with Measures
·
Working with Measure Groups
Lab 4: Configuring Measures and Measure Groups
·
Exercise 1: Configuring Measures
·
Exercise 2: Defining Dimension Usage and
Relationships
·
Exercise 3: Configuring Measure Group Storage
After completing this module, students will be able to:
·
Work with measures.
·
Work with measure groups.
Module 5: Querying Multidimensional Analysis Solutions
This module introduces multidimensional expressions (MDX)
and describes how to implement calculated members and named
sets in an Analysis Services cube.
Lessons
·
MDX Fundamentals
·
Adding Calculations to a Cube
Lab 5: Querying a Cube
·
Exercise 1: Querying a Cube by Using MDX
·
Exercise 2: Creating a Calculated Member
·
Exercise 3: Defining a Named Set
After completing this module, students will be able to:
·
Describe Multidimensional Expression (MDX)
fundamentals
·
Add calculations to a cube
Module 6: Customizing Cube Functionality
This module explains how to customize a cube by implementing
key performance indicators (KPIs), actions, perspectives,
and translations.
Lessons
·
Implementing Key Performance Indicators
·
Implementing Actions
·
Implementing Perspectives
·
Implementing Translations
Lab 6: Customizing a Cube
·
Exercise 1: Implementing a KPI
·
Exercise 2: Implementing an Action
·
Exercise 3: Implementing a Perspective
·
Exercise 4: Implementing a Translation
After completing this module, students will be able to:
·
Implement Key Performance Indicators (KPIs)
·
Implement actions
·
Implement perspectives
·
Implement translations
Module 7: Deploying and Securing an Analysis Services
Database
This module describes how to deploy an Analysis Services
database to a production server, and how to implement
security in an Analysis Services multidimensional solution.
Lessons
·
Deploying an Analysis Services Database
·
Securing an Analysis Services Database
Lab 7: Deploying and Securing an Analysis Services Database
·
Exercise 1: Deploying an Analysis Services Database
·
Exercise 2: Securing an Analysis Services Database
After completing this module, students will be able to:
·
Deploy an Analysis Services database.
·
Secure an Analysis Services database.
Module 8: Maintaining a Multidimensional Solution
This module discusses the maintenance tasks associated with
an Analysis Services solution, and describes how
administrators can use the Analysis Services management
tools to perform them.
Lessons
·
Configuring Processing Settings
·
Logging, Monitoring, and Optimizing an Analysis
Services Solution
·
Backing Up and Restoring an Analysis Services
Database
Lab 8: Maintaining an Analysis Services Database
·
Exercise 1: Configuring Processing
·
Exercise 2: Implementing Logging and Monitoring
·
Exercise 3: Backing Up and Restoring an Analysis
Services Database
After completing this module, students will be able to:
·
Configure processing settings.
·
Log, monitor, and optimize an Analysis Services
solution.
·
Back up and restore an Analysis Services database.
Module 9: Introduction to Data Mining
This module introduces data mining, and describes how to
implement data mining structures and models. It then
explains how to validate data model accuracy.
Lessons
·
Overview of Data Mining
·
Creating a Data Mining Solution
·
Validating Data Mining Models
Lab 9: Implementing Data Mining
·
Exercise 1: Creating a Data Mining Structure
·
Exercise 2: Adding a Data Mining Model
·
Exercise 3: Exploring Data Mining Models
·
Exercise 4: Validating Data Mining Models
After completing this module, students will be able to:
·
Describe data mining.
·
Create a data mining solution.
·
Validate data mining models.
Reporting Services
Exam Topics Covered
·
Managing SSRS
·
Developing Reporting Solutions using SSRS
·
Evening exam review using Transcender Test Prep.
Module 1: Introduction to Microsoft SQL Server Reporting
Services
This module introduces the role that Reporting Services
plays in an organization’s reporting life cycle, the key
features offered by Reporting Services, and the components
that make up the Reporting Services architecture.
Lessons
·
Overview of SQL Server Reporting Services
·
Installing Reporting Services
·
Reporting Services Tools
Lab 1: Using Reporting Services Tools
Exercise 1: Exploring Report Designer
Exercise 2: Exploring Report Manager
After completing this module, students will be able to:
·
Describe the features of SQL Server Reporting
Services.
·
Install Reporting Services.
·
Describe the Reporting Services tools.
Module 2: Authoring Basic Reports
This module introduces the fundamentals of report authoring,
including configuring data sources and data sets, creating
tabular reports, summarizing data, and applying basic
formatting.
Lessons
·
Creating a Basic Table Report
·
Formatting Report Pages
·
Calculating Values
Lab 2: Creating a Simple Report
Exercise 1: Creating a Basic Table Report
Exercise 2: Formatting Report Pages
Exercise 3: Adding Calculated Values
After completing this module, students will be able to:
·
Create a basic table report.
·
Format report pages.
·
Calculate values for a report.
Module 3: Enhancing Basic Reports
This module introduces navigational controls and some
additional types of data regions, and discusses how to use
them to enhance a basic report.
Lessons
·
Interactive Navigation
·
Displaying Data
Lab 3: Enhancing a Report
Exercise 1: Using Dynamic Visibility
Exercise 2: Using Document Maps
Exercise 3: Initiating Actions
Exercise 4: Using a List Data Region
After completing this module, students will be able to:
·
Create reports with interactive navigation.
·
Display data in various formats.
Module 4: Manipulating Data Sets
This module explores data sets to a greater depth, including
the use of alternative data sources and interacting with a
data set through the use of parameters. Students learn how
to dynamically modify the data set underlying a data region
by allowing parameters to be sent to the underlying query,
as well as learn to use best practices to implement static
and dynamic parameter lists when interacting with queries
and stored procedures.
Lessons
·
Defining Report Data
·
Using Parameters and Filters
·
Using Parameter Lists
Lab 4: Manipulating Data Sets
Exercise 1: Using parameters to restrict query results
Exercise 2: Using parameters to filter report data
Exercise 3: Creating dynamic parameter lists
Exercise 4: Using parameters with a stored procedure
If Time Permits: Displaying all categories in a parameter
list
After completing this module, students will be able to:
·
Define report data.
·
Use parameters and filters.
·
Use parameter lists.
Module 5: Using Report Models
This module describes how to create a report model so that
business users can create their own reports without using
the full Report Designer development environment. Students
also learn how to use Report Builder to create a report from
a report model.
Lessons
·
Creating Report Models
·
Using Report Builder
Lab 5: Working with Report Models
Exercise 1: Creating a Report Model
Exercise 2: Using Report Builder to Create a Report
After completing this module, students will be able to:
·
Create Report Models.
·
Use Report Builder.
Module 6: Publishing and Executing Reports
This module explains the various options you can use to
publish reports to the report server and execute them.
Lessons
·
Publishing Reports
·
Executing Reports
·
Creating Cached Instances
·
Creating Snapshots and Report History
Lab 6: Publishing and Executing Reports
Exercise 1: Publishing Reports
Exercise 2: Executing a Report On Demand
Exercise 3: Configuring and Viewing a Cached Report
Exercise 4: Configuring and Viewing a Snapshot Report
After completing this module, students will be able to:
·
Publish reports.
·
Execute reports.
·
Create cached instances.
Create snapshots and report history.
Module 7: Using Subscriptions to Distribute Reports
This module describes how to implement subscriptions so that
you can distribute reports either automatically by e-mail or
by publishing reports to a shared folder.
Lessons
·
Introduction to Report Subscriptions
·
