COURSEBIBOOTCAMP

Microsoft SQL Server Business Intelligence Bootcamp

5-Day (COURSE-BI-BOOTCAMP)

Description

Written and presented by industry experts, this intensive five-day course gives professional developers the information and hands-on experience they need to deliver state-of-the-art, integrated reporting and reporting solutions using Microsoft Business Intelligence.

Delivering the right information in the right format at the right time to all users in the organization—that is Microsoft’s Business Intelligence (BI) strategy and vision for enterprise information management. Microsoft’s integrated BI tools enable developers to create effective solutions that information workers use every day to find meaningful patterns in the vast sea of data they collect and to use that insight to quickly respond to changing business conditions.

On completion of this workshop attendees will be able to position different types of Business Intelligence solutions, from enterprise data warehousing to data marts to real-time operational reporting and analytics. Attendees will learn how to design and build powerful integrated solutions that provide users with rich reporting, analysis and visualization capabilities by targeting operational data from an existing line-of-business application using the Microsoft Business Intelligence platform.

Attendees will work through an extensive set of detailed and practical hands-on labs that reinforce the material presented.

For details on booking courses or seminars, please visit our booking page.

Audience

The presentations and labs have been designed specifically to give professional developers—those who work for an ISV, SI, or enterprise customer—the information they need to deliver integrated solutions on the Microsoft BI platform.

This course is also relevant and useful for those interested to understand and evaluate Microsoft’s Business Intelligence offerings.

NOTE: While this course is based on SQL Server 2008R2, it is useful for attendees working with SQL Server 2005, SQL Server 2008. Differences between the releases are identified.

Prerequisites

Attendees should have a basic understanding of SQL Server and relational database technology. Some development experience with a .NET language is useful but not essential.

Course Objectives

Through interactive presentations and concrete hands-on labs, attendees will learn:

How to use Microsoft SQL Server to build and manage data marts which provide a consolidated view of organizational data.
How to use dimensional modelling techniques to create data models in SQL Server for hosting consolidated BI information.
How to build consolidated data marts using SQL Server Integration Services to extract, transform, and load business-critical information into SQL Server databases from disparate sources such as business applications and corporate databases.
How to use SQL Server Reporting Services to develop predefined and ad hoc relational reports that simplify access to business-critical information.
How to use SQL Server Analysis Services to create Unified Dimensional Models data models that simplify analytical access to business-critical information stored in SQL Server, and how to publish and collaborate on findings using SQL Server Reporting Services.
How to use SQL Server Analysis Services to create data mining models that explore, uncover and predict patterns in a BI solution

Technologies

The workshop will empower developers to exploit the BI functionality in the following technologies:

SQL Server Database Services
SQL Server Integration Services
SQL Server Reporting Services
SQL Server Analysis Services (OLAP and Data Mining)
PowerPivot
Office Excel

While based on SQL Server 2008 R2 versions of SQL Server, this course is highly relevant for those working on earlier versions of SQL Server. Differences between versions are highlighted.

Day 1

Course Introduction

Module IS201 Overview of SQL Server Integration Services

Introduction to the Microsoft BI platform
Integration Services features overview
Working with SQL Server BI tooling
Creating SSIS Projects and Packages
LAB: Creating SSIS Projects and Packages

Module IS202 - Working with SSIS Control Flow

Introduction to Control Flow tasks and precedence constraints
Working with containers
Passing information between tasks using variables and expressions
LAB: Working with SSIS Control Flow

Module IS203 - Working with SSIS Data Flow

Introduction to Data Flow Data Sources, Destinations and Assistants
Transforming data
Debugging using data viewers and breakpoints
LAB: Working with SSIS Data Flow

Module IS204 - SSIS Configuration, Deployment, Scheduling and Logging

Project and package configurations
Deploying projects or packages
Scheduling package execution
Logging package activity
LAB: SSIS Configuration, Deployment, Scheduling and Logging

Day 2

Module IS301 - SSIS Case Study: Dimensional Modelling Core Concepts

Why implement dimensional models?
Dimension table core concepts
Fact table core concepts
Profiling incoming data
LAB: Dimensional Modelling Core Concepts

Module IS302 - Loading Dimension Tables

Issues with loading dimension tables
Implementing slowly changing dimensions
Implementing inferred members
Useful additional transformations
LAB: Loading Dimension Tables

Module IS303 - Loading Fact Tables

Issues with loading fact tables
Working with granularity and calculations
Useful additional transformations
LAB: Loading Fact Tables

Module IS304 - Designing for Restartability and Performance

Designing for restartability
Improving SSIS package performance
SSIS tasks vs T-SQL tasks
LAB: Designing for Restartability and Performance

Day 3

Module AS201: Introduction to SQL Server Analysis Services

Introduction to OLAP
Building Analysis Services cubes
LAB: Creating a cube

Module AS202: Designing Dimensions

Enhancing cube dimensions
Implementing dimension hierarchies
LAB: Enhancing cube dimensions

Module AS203: Calculations, KPIs, Actions and Processing

Basic MDX Concepts
Calculations
Key Performance Indicators
Actions
LAB: Calculations, KPIs and Actions
Cube processing concepts
Incremental fact and dimension processing
LAB: Cube processing
Deploying Cubes
LAB: Cube deployment
Cube Writeback
LAB: What-If Analysis Using Cube Writeback

Module AS204: Introduction to Analysis Services Data Mining

Introduction to Data Mining
Business applications of data mining
Data mining algorithm comparison
LAB: Implementing Data Mining

Day 4

Module RS201: Introducing Reporting Services

Reporting fundamentals
Introducing Reporting Services
Basic Report Design and Delivery
Deployment Scenerios
LAB: Designing Reports - Managed Reports

Module RS202: Common Report Elements and Interactivity

Report Project Items
Common Report Elements
Report Interactivity
LAB: Advanced Managed Reports - Enhancing the Report Design

Module RS301: Custom Logic and Reporting From Analysis Services

Extending Reports with Custom Logic
Reporting from Analysis Services
LAB: Advanced Managed Reports - Custom Logic
LAB: UDM Reporting with Reporting Services

Module RS302: Managing Report Execution

Optimizing Report Execution
Securing Report Server Items and Data
Delivering Reports with Subscriptions
LAB: Managing Report Execution

Day 5

Module RS303: End-user Reporting with Report Builder 3.0

Introducing Report Builder
Report Models and Report Builder 1.0
Report Builder 3.0
Publishing Shared Datasets and Report Parts
LAB: Report Builder - Self Service Reporting

Module RS304: End-user Reporting with Excel and Excel Services

Reporting from Excel
Visualizing Data
Publishing to Excel Services
LAB: UDM Reporting with Excel

Module RS203: Analysing Data Using PowerPivot

Introducing PowerPivot
Loading data into PowerPivot
Preparing PowerPivot data
Reporting from PowerPivot
LAB: Loading Data in PowerPivot

Module RS204: Enhancing PowerPivot Models Using DAX and Publishing to SharePoint

Introducing DAX
Creating Calculated Columns and Measures
Publishing to SharePoint
LAB: Enhancing PowerPivot Models with DAX

Module RS205: General Enhancements, Reports as Data Feeds, and Maps

General Reporting Enhancements
Using Reports as Data Sources
Using Map Data in Reports