20467 Designing Business Intelligence Solutions with Microsoft SQL

Overview

Course Description

This five-day instructor-led course teaches students how to design and implement a BI infrastructure. The course discusses design, installation. the course discusses design, installation, and maintenance of a BI platform.  This course maps to skills and knowledge measured by Microsoft Exam 70-467, and in conjunction with on-the-job experience, can help prepare you for the exam.

Audience Profile

This course is intended for database professionals who need who plan, implement, and manage database solutions. Primary responsibilities include: Planning and implementing database infrastructure.

Planning and implementing consolidation strategies.

  • Implementing SQL Server in on-premises, cloud, and hybrid IT scenarios.

Planning and implementing high availability solutions.

Planning and implementing disaster recovery solutions.

Prerequisites

Before attending this course, students must have:

  • A basic understanding of dimensional modeling (star schema) for data warehouses.
  • The ability to create Integration Services packages that include control flows and data flows.
  • The ability to create a basic multidimensional cube with Analysis Services.
  • The ability to create a basic tabular model with PowerPivot and Analysis Services.
  • The ability to create Reporting Services reports with Report Designer.
  • The ability to implement authentication and permissions in the SQL Server database engine, Analysis Services, and Reporting Services.
  • Familiarity with SharePoint Server and Microsoft Office applications, particularly Excel.

Learning Objectives

  • Plan the components of a BI solution
  • Design a data warehouse solution
  • Design an ETL solution
  • Plan analytical data models
  • Plan a BI delivery solution
  • Design a Reporting Services solution
  • Design an Excel reporting solution
  • Plan a SharePoint Server BI solution
  • Monitor and optimize a BI solution
  • Plan for BI operations

 

Course Outline

Course Outline

Module 1: Planning a BI Solution

This modules discusses how to plan the components of a BI solution.

Lessons

  • Elements of a BI Solution
  • Planning a BI Project
  • The Microsoft BI Platform

Lab : Planning a BI Solution

  • Exercise 1: Scoping a BI Solution.
  • Exercise 2: Reviewing a Suggested Solution

Module 2: Planning a BI Infrastructure

This modules explains how to plan an appropriate BI infrastructure for a given set of BI services.

Lessons

  • Planning a Data Warehouse Infrastructure
  • Planning an ETL Infrastructure
  • Planning Analysis Services Infrastructure
  • Planning Reporting Services Infrastructure

Lab : Planning a BI Infrastructure

  • Exercise 1: Determining Required Servers
  • Exercise 2: Determining Data Warehouse Server Hardware Requirements
  • Exercise 3: Reviewing a Suggested Solution

Module 3: Designing a Data Warehouse

This module explain how to design a data warehouse for a given set of user requirements.

Lessons

  • Designing a Logical Schema
  • Designing a Physical Schema

Lab : Designing a Data Warehouse Logical Schema

  • Exercise 1: Identifying Required Dimensions
  • Exercise 2: Identifying Measures and Hierarchies
  • Exercise 3: Designing a Data Warehouse Schema
  • Exercise 4: Reviewing a Suggested Solution

Module 4: Designing an ETL Solution

This module explains how to design an ETL solution for a data warehouse.

Lessons

  • Planning for Data Extraction
  • Planning for Data Transformation
  • Planning for Data Loading
  • Planning for SSIS Package Deployment

Lab : Designing an ETL Solution

  • Exercise 1: Reviewing Data Sources
  • Exercise 2: Creating Source-to-Target Maps
  • Exercise 3: Planning ETL Processes
  • Exercise 4: Reviewing a Suggested Solution

Module 5: Designing Analytical Data Models

This module explains how to design analytical data models for specific BI scenarios.

Lessons

  • Options for Analytical Models
  • Data Model Design Considerations

Lab : Comparing Analytical Data Models

  • Exercise 1: Creating a Multidimensional Model
  • Exercise 2: Creating a Tabular Model

Module 6: Planning a BI Delivery Solution

This module explains how to choose an appropriate delivery solution for a given scenario.

Lessons

  • Considerations for BI Delivery
  • Choosing a Delivery Approach

Lab : Planning a BI Delivery Solution

  • Exercise 1: Assessing Reporting Requirements

Module 7: Designing a Reporting Services Solution

This module explains how to design a Reporting Services solution.

Lessons

  • Designing Reports
  • Considerations for Report Execution
  • Planning a Reporting Solution

Lab : Designing a Reporting Services Solution

  • Exercise 1: Planning a Reporting Services Solution
  • Exercise 2: Planning a Report Delivery Environment
  • Exercise 3: Configuring Report Execution

Module 8: Designing an Excel-Based Reporting Solution

This module explains how to design a reporting solution that uses Excel.

Lessons

  • Using Excel for Data Reporting and Analysis
  • PowerPivot in Excel
  • Power View in Excel

Lab : Designing an Excel Reporting Solution

  • Exercise 1: Planning Excel Features
  • Exercise 2: Analyzing a Cube with Excel
  • Exercise 3: Using PowerPivot
  • Exercise 4: Using Power View

Module 9: Planning a SharePoint Server BI Solution

This module explains how to design a SharePoint BI solution.

Lessons

  • Introduction to SharePoint Server as a BI Platform
  • Planning Security for a SharePoint Server BI Solution
  • Planning Reporting Services Configuration
  • Planning PowerPivot Configuration
  • Planning for PerformancePoint Services

Lab : Planning a SharePoint Server BI Solution

  • Exercise 1: Planning SharePoint Server Security
  • Exercise 2: Configuring Reporting Services in SharePoint Server
  • Exercise 3: Managing PowerPivot Data Refresh
  • Exercise 4: Reviewing PowerPivot Activity
  • Exercise 5 (Optional) Creating a PerformancePoint Dashboard

Module 10: Monitoring and Optimizing a BI Solution

This module explains how to monitor and optimize a BI solution.

Lessons

  • Overview of BI Monitoring
  • Monitoring and Optimizing the Data Warehouse
  • Monitoring and Optimizing Analysis Services
  • Monitoring and Optimizing Reporting Services

Lab : Monitoring and Optimizing a BI Solution

  • Exercise 1: Monitoring and Optimizing a Data Warehouse
  • Exercise 2: Troubleshooting Analysis Services Queries
  • Exercise 3: Monitoring and Optimizing Reporting Services

Module 11: Planning BI Operations

This module explains how to plan management and maintenance operations for a BI solution.

Lessons

  • Overview of BI Operations
  • Automating ETL Tasks
  • Post ETL Tasks
  • Planning a Backup Strategy for a BI Solution

Lab : Planning BI Operations

  • Exercise 1: Planning ETL and Processing Tasks
  • Exercise 2: Planning a Backup Strategy

 

Register for Course

Tuition
Available Registrations: Unlimited
The Tuition ticket is sold out. You can try another ticket or another date.

Cost

$2,950.00

Course Code

FRCMIC-63

Course Duration

5

Instructions Method

Instructor Led
REGISTER