BIBC - Business Intelligence BOOTCAMP

Duration

5 Days

Introduction

Elements of this syllabus are subject to change.

The BI bootcamp will focus on the SQL Server Integration Services, Analysis Services, and Reporting Services. It is an intensive program designed for attendees with solid fundamental SQL experience. The bootcamp will run from 8:00 am to 6:00 pm, Monday through Thursday with Test Prep on from 6:30 pm to 8:30 pm each evening.

This course is an intense 5-day deep-dive into all three technologies, focusing on the hands on application and understanding of the technologies. The evenings are spent addressing Exam 70-445: TS: Microsoft SQL Server 2005 Business Intelligence – Implementation and Maintenance. Throughout the discussion of each topic, we will reference particular topics or points of interest in relation to the exam, but the majority of the exam prep will be done in the evenings.

Each day (Monday through Friday) will be spent learning about the technologies and applying that knowledge in a hands on lab environment. The students will also be given white papers and/or webcasts to review each night if they feel so inclined.

Prerequisites

Before attending this course, students must have:

• 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)

Summarized Course Outline

SQL Server 2005 Integration Services:

• Developing SQL Server 2005 Integration Services
  Solutions
• Control Flow
• Data Flow
• Implementing Logging
• Debugging and Error Handlking
• 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

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

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
• Creating Report Subscriptions
• Managing Report Subscriptions

Lab 7: Implementing Subscriptions

• Exercise 1: Creating a Standard Subscription
• Exercise 2: Creating a Data-Driven Subscription

After completing this module, students will be able to:

• Describe report subscriptions.
• Create report subscriptions.
• Manage report subscriptions.

Module 8: Administering Reporting Services

This module discusses how to administer the Reporting Services server, how to monitor and optimize the performance of the report server, how to maintain the Reporting Services databases, and how to keep the system secure.

Lessons

• Server Administration
• Performance and Reliability Monitoring
• Administering Report Server Databases
• Security Administration

Lab 8: Administering Reporting Services

• Exercise 1: Using Reporting Services Configuration Manager
• Exercise 2: Securing a Reporting Services Site
• Exercise 3: Securing Items

After completing this module, students will be able to:

• Administer the reporting server.
• Monitor performance and reliability.
• Administer the Report Server databases.
• Administer security

top of page