20467B: Designing Business Intelligence Solutions With

20467B: Designing Business Intelligence Solutions with

Microsoft SQL Server 2012

Course Introduction4m
Course Introduction
Module 01 - Planning a BI Solution1h 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 Infrastructure49m
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 Warehouse2h 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 Solution1h 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 Models2h 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 Solution42m
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 Solution1h 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 Solution1h 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 Solution1h 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 Solution1h 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 Solution1h 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

Total Duration: 16h 1m