MOC 20467B: Designing Business Intelligence Solutions with Microsoft SQL Server 2012
Course Overview
This course provides students with the knowledge and skills to design business intelligence solutions with Microsoft SQL Server 2012. The course includes planning a BI solution, planning a SQL Server business intelligence infrastructure, designing a data warehouse, designing an ETL solution, designing analytical data models, planning a BI delivery solution, designing a reporting services solution, designing a Microsoft Excel-based reporting solution, planning a SharePoint
Server BI solution, monitoring and optimizing a BI solution, and operating a BI solution.
Course Introduction 4m
Course Introduction
Module 01 - Planning a BI Solution 1h 16m
Lesson 1: Elements of a BI Solution Overview of a BI Solution
Business Data Sources The Data Warehouse
Extract, Transform, and Load Processes Analytical Data Models
Reporting and Analysis
Lesson 2: The Microsoft BI Platform Windows Server
SQL Server SharePoint Server Office Applications
Upgrading to SQL Server 2012 SQL Server Appliances Lesson 3: Planning a BI Project BI Project Overview
Project Infrastructure Project Personnel Business Stakeholders Project Scope
Module 01 Review
Module 02 - Planning SQL Server Business Intelligence Infrastructure 49m Lesson 1: Considerations for BI Infrastructure
Infrastructure Planning in a BI Project System Sizing Considerations BI Workloads
Typical Server Topologies for a BI Solution Scaling Out a BI Solution
Planning for High Availability
Lesson 2: Planning Data Warehouse Hardware SQL Server Fast Track Reference Architectures Core-Balanced System Architecture
Demo - Calculating Maximum Consumption Rate Determining Processor and Memory Requirements Determining Storage Requirements
Considerations for Storage Hardware Module 02 Review
Module 03 - Designing a Data Warehouse 2h 20m
Lesson 1: Data Warehouse Design Overview Data Warehouse Design in a BI Project The Dimensional Model
The Data Warehouse Design Process Dimensional Modeling
Documenting Dimensional Models Lesson 2: Designing Dimension Tables Considerations for Dimension Keys Dimension Attributes and Hierarchies Unknown and None
Designing Slowly Changing Dimensions Time Dimension Tables
Self-Referencing Dimension Tables Junk Dimensions
Lesson 3: Designing Fact Tables Fact Table Columns
Types of Measure Types of Fact Table
Demo - Data Warehouse Design
Lesson 4: Designing a Data Warehouse Physical Implementation Data Warehouses I/O Activity
Consideration for Database Files Table Partitioning
Demo - Partitioning a Fact Table Considerations for Indexes Demo - Creating Indexes Data Compression
Demo - Implementing Data Compression Using Views to Abstract Base Tables Module 03 Review
Module 04 - Designing an ETL Solution 1h 33m
Lesson 1: ETL Overview ETL in a BI Project
Common ETL Data Flow Architectures Documenting High-Level Data Flows Creating Source To Target Mappings Demo - Utilizing Integration Services Lesson 2: Planning Data Extraction
Profiling Source Systems Demo - Data Profiling
Identifying New and Modified Rows Planning Extraction Windows
Lesson 3: Planning Data Transformation Where to Perform Transformations
Transact-SQL vs. Data Flow Transformations Handling Invalid Rows and Errors
Logging Audit Information Lesson 4: Planning Data Loads Minimizing Logging
Loading Indexed Tables Loading Partitioned Fact Tables
Demo - Loading a Partitioned Fact Table Module 04 Review
Module 05 - Designing Analytical Data Models 2h 13m
Lesson 1: Introduction to Analytical Data Models Analytical Model Design in a BI Project
BI Semantic Model Architecture An Overview of Analytical Data Models Data Model Capabilities
Considerations for Choosing an Analytical Data Model Lesson 2: Designing an Analytical Data Model
Creating an Initial Data Model Data Sources for Analytical Models
Considerations and Guidelines for Data Sources Considerations for Attributes
Demo - Creating a Multidimensional Data Model Demo - Creating a Tabular Data Model
Lesson 3: Designing Dimensions Considerations for Balanced Hierarchies Role-Playing Dimensions
Parent-Child Hierarchies
Lesson 4: Enhancing Data Models Custom Calculations
Key Performance Indicators Perspectives
Considerations for Measures Storage Module 05 Review
Module 06 - Planning a BI Delivery Solution 42m
Lesson 1: Considerations for Delivering BI Reporting and Analysis Design in Context Data Sources for BI
Reporting Tools
SharePoint Server as a BI Delivery Platform Lesson 2: Common Reporting Scenarios Formal Reports
Data Exploration and Analysis
Analytical Data Mashups Scorecards
Dashboards
Lesson 3: Choosing a Reporting Tool SQL Server Reporting Services Microsoft Excel
PerformancePoint Services
Guidelines for Reporting Tool Choice Module 06 Review
Module 07 - Designing a Reporting Services Solution 1h 31m
Lesson 1: Planning a Reporting Services Solution Reporting Services in a BI project
Identifying Report Requirements Reporting Services Modes Planning Report Delivery Planning Security
Planning Report Navigation Lesson 2: Designing Reports Designing Data Sets
Considerations for Printed Reports Considerations for Interactive Reports Considerations for Graphical Reports Considerations for Report Rendering Considerations for Data Model Sources Lesson 3: Planning Report Consistency Report Templates
Data Sources and Datasets Linked Reports
Self-Service Reporting Consistency
Demo - Utilizing Reporting Services in Your BI Solution Module 07 Review
Module 08 - Designing a Microsoft Excel-Based Reporting Solution 1h 9m Lesson 1: Using Excel for Data Analysis and Reporting
Microsoft Excel in a BI Project Data Sources for Excel Viewing Data in Excel
Interactive Data Analysis in Excel Lesson 2: PowerPivot for Excel Deploying PowerPivot for Excel
PowerPivot Workbooks vs. Tabular Analysis Services Databases Planning Considerations for PowerPivot
Lesson 3: Power View for Excel Deploying Power View for Excel Using Power View in Excel
Considerations for Power View in Excel
Demo - Utilizing Excel as a Presentation Layer for BI Module 08 Review
Module 09 - Planning a SharePoint Server BI Solution 1h 26m Lesson 1: Introduction to SharePoint Server as a BI Platform
SharePoint Server Design in a BI Project What Is SharePoint Server?
SharePoint Farm Topology Options Required SharePoint Services for BI Sites and Subsites for BI
Lesson 2: Planning Security for a SharePoint Server BI Solution SharePoint Authentication Modes
Configuring SharePoint Server Authentication Introduction to Kerberos
Planning Kerberos Configuration Demo - Configuring Kerberos
Lesson 3: Planning Reporting Services Configuration SharePoint Integrated Mode Requirements
Configuring Reporting Services
Planning Document Libraries for Reports Lesson 4: Planning PowerPivot Configuration Planning PowerPivot for SharePoint Deployment Planning and Managing Data Refresh
BISM Connections Monitoring PowerPivot
Lesson 5: Planning for PerformancePoint Services What is PerformancePoint Services?
Planning for PerformancePoint Services Dashboard Designer
PerformancePoint Data Sources
KPIs, Reports, Scorecards, and Dashboards Module 09 Review
Module 10 - Monitoring and Optimizing a BI Solution 1h 19m
Lesson 1: Overview of BI Monitoring
Introduction to Monitoring and Optimization What to Monitor
Creating a Performance Baseline
Lesson 2: Monitoring and Optimizing the Data Warehouse Considerations for Data Warehouse Performance
Monitoring Tools for the Data Warehouse
Using Resource Governor to Balance Resource Utilization Troubleshooting Data Warehouse Performance
Lesson 3: Monitoring and Optimizing Analysis Services Considerations for Analysis Services Performance Monitoring Tools for Analysis Services
Monitoring Analysis Services
Troubleshooting Analysis Server Queries
Lesson 4: Monitoring and Optimizing Reporting Services Considerations for Reporting Services Performance Monitoring Tools for Reporting Services
Using Caching and Snapshots to Optimize Performance Demo - Monitoring Your Business Intelligence Plan Module 10 Review
Module 11 - Operating a BI Solution 1h 38m Lesson 1: Overview of BI Operations
Planning for BI Operations Automating Operational Tasks
Considerations for Scheduling Operational Tasks Lesson 2: ETL Operations
Deploying and Configuring Packages Environments and Variables
Considerations for Package Execution Package Execution Reports
Backing Up the SSIS Catalog
Lesson 3: Data Warehouse Operations Managing Indexes
Maintaining Statistics
Managing Partitions in the Data Warehouse Backing Up the Data Warehouse
Demo - Using a Partial Backup Strategy Lesson 4: Analysis Services Operations Managing Partitions in a Data Model Processing Data Models
Backing Up an Analysis Services Database Lesson 5: Reporting Services Operations Managing Scheduled Tasks
Managing Encryption Keys
Backing Up Reporting Services Databases
Demo - Automating Operational Tasks for Your BI Solution Module 11 Review
Course Closure