Designing Business Intelligence Solutions with Microsoft SQL Server 2014

Prerequisites

In addition to their professional experience, students who attend this training should already have the following technical knowledge:

• A basic understanding of dimensional modeling (star schema) for data warehouses
• Basic server hardware knowledge
• 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 Outcomes

After completing this course, students will be able to:

• Plan a BI solution.
• Plan SQL Server BI infrastructure.
• Design a data warehouse.
• Design an extract, transform and load (ETL) solution.
• Design analytical data models.
• Plan a BI delivery solution.
• Design a Reporting Services solution.
• Design a Microsoft Excel-based reporting solution.
• Plan a SharePoint Server BI solution.
• Monitor and optimize a BI solution.
• Operate a BI solution.

Course Outline

Module 1: Planning a BI Solution

This module enables students to plan the components of a BI Solution.

Lessons

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

Lab : Planning BI Solutions

Module 2: Planning SQL Server Business Intelligence Infrastructure

This module describes how to use plan appropriate BI Infrastructure given a set of BI Parameters.

Lessons

• Considerations of BI Infrastructure
• Planning Data Warehouse Hardware

Lab : Planning BI Infrastructure

Module 3: Designing a Data Warehouse

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

Lessons

• Data Warehouse Design Overview
• Designing Dimension Tables
• Design FACT tables

Lab : Designing a Data Warehouse Logical Schema

Module 4: Designing an ETL solution

This lesson describes how to design an extract, transform and load (ETL) solution.

Lessons

• ETL Overview
• Planning for Data Extraction
• Planning for Data Transformations
• Planning for Data Loading

Lab : Designing an ETL Solution

Module 5: Designing Analytical Data Models

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

Lessons

• Introduction to Analytical data Models
• Designing Analytical Data Models

Lab : Designing Analytical Data Model

Lab : Designing Dimensions and Hierarchies

Module 6: Planning a BI Delivery Solution

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

Lessons

• Considerations for BI delivery
• Common Reporting Scenarios
• Choosing a Reporting Tool

Lab : Planning a BI Delivery Solution

Module 7: Designing a Reporting Services Solution

This module describes how to design a reporting services solution.

Lessons

• Planning a Reporting Solution
• Designing Reports
• Planning Report Consistency

Lab : Designing a Reporting Services Solution

Module 8: Designing an Excel Based reporting Solution

In this module students will learn how to design a reporting solution using Excel.

Lessons

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

Lab : Designing and Excel Reporting Solution

Module 9: Planning a SharePoint Server BI Solution

This module introduces the use of SharePoint Server as a 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 : Implementing a SharePoint Server BI Solution

Lab : Implementing PerformancePoint Services

Module 10: Monitoring and Optimizing a BI Solution

At the conclusion of this module you will be able to optimize and monitor a BI solution.

Lessons

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

Lab : Monitoring and Optimizing a BI Solution

Module 11: Operating a BI Solution

At the conclusion of this module you will be able to plan management and maintenance operations of a BI solution.

Lessons

• Overview of BI Operations
• ETL Operations
• Data Warehouse Operations
• Analysis Services Operations
• Reporting Services Operations

Lab : Operating a BI Solution