Designing Business Intelligence Solutions with Microsoft SQL Server 2012 Course 20467A; 5 Days

Lincoln Land Community College Capital City Training Center 130 West Mason

Springfield, IL 62702 217-782-7436 www.llcc.edu/cctc

Designing Business Intelligence Solutions with Microsoft SQL Server 2012 Course 20467A; 5 Days

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.

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.

Students who attend this training can meet the prerequisites by attending the following courses, or obtaining equivalent knowledge and skills:

 10777A: Implementing a Data Warehouse with Microsoft SQL Server 2012

 10778A: Implementing Data Models and Reports with Microsoft SQL Server 2012 Course Outline

Module 1: Planning a BI Solution

This module 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 After completing this module, students will be able to:

 Describe elements of a BI Solution.

 Describe considerations for a BI project.

 Describe how Microsoft products can be used to implement a BI solution.

Module 2: Planning a BI Infrastructure

This module 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 After completing this module, students will be able to:

 Plan a data warehouse infrastructure

 Plan an ETL infrastructure

 Plan Analysis Services infrastructure

 Plan Reporting Services infrastructure Module 3: Designing a Data Warehouse

This module explains 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 Lab: Designing a Data Warehouse Physical Schema

 Exercise 1: Planning Database Storage

 Exercise 2: Designing Partitioning

 Exercise 3: Designing Indexes

 Exercise 4: Reviewing a Suggested Solution After completing this module, students will be able to:

 Select a data modeling approach.

 Design a physical data warehouse schema 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 After completing this module, students will be able to:

 Plan data extraction in an ETL solution.

 Plan data transformation in an ETL solution.

 Plan data loading in an ETL solution.

 Plan SSIS package deployment 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 Lab: Designing Data Models

 Exercise 1: Designing Dimension Attributes

 Exercise 2: Designing Data Model Aggregations and Storage

 Exercise 3: Reviewing a Suggested Solution After completing this module, students will be able to:

 Describe options for analytical models.

 Describe considerations for cube design.

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 After completing this module, students will be able to:

 Describe considerations for planning a BI delivery solution.

 Choose an appropriate technique for a given scenario.

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 After completing this module, students will be able to:

 Plan a reporting solution.

 Describe considerations for report data.

 Describe considerations for 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

After completing this module, students will be able to:

 Use Excel to analyze data.

 Use PowerPivot to analyze data.

 Use Power View to analyze data.

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 After completing this module, students will be able to:

 Describe key considerations for using SharePoint for BI

 Describe key considerations for security when using SharePoint for BI

 Plan the deployment and configuration of Reporting Services in SharePoint Integration Mode

 Plan the deployment and configuration of PowerPivot

 Plan PerformancePoint Services in a SharePoint site 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 After completing this module, students will be able to:

 Plan BI monitoring.

 Plan data warehouse monitoring and optimization.

 Plan Analysis Services monitoring and optimization.

 Plan Reporting Services monitoring and optimization

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

After completing this module, students will be able to:

 Plan BI operations.

 Plan ETL operations.

 Plan post-ETL tasks.

 Plan backup for a BI solution.