Microsoft SQL Server 2012 (M20467B): Designing Business Intelligence Solutions

1. Planning a BI Solution

• Elements of a BI Solution
• The Microsoft BI Platform

2. Planning SQL Server Business Intelligence Infrastructure

• Considerations for BI Infrastructure
• Planning Data Warehouse Hardware

3. Designing a Data Warehouse

• Data Warehouse Design Overview
• Design Dimension Tables and Fact Tables
• Design a Data Warehouse Physical Implementation

4. Designing an ETL Solution

• ETL Overview
• Plan Data Extraction, Data Transformation, and Data Loads

5. Designing Analytical Data Models

• Introduction and Designing an Analytical Data Model
• Designing Dimensions
• Enhancing Data Models

6. Planning a BI Delivery Solution

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

7. Designing a Reporting Services Solution

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

8. Designing a Microsoft Excel-Based Reporting Solution

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

9. Planning a SharePoint Server BI Solution

• 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

10. Monitoring and Optimizing a BI Solution

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

11. Operating a BI Solution

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

• 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

Lab 1: Plan a BI Solution

• Scope a BI Solution
• Determine Software Requirements

Lab 2: Plan a BI Infrastructure

• Plan Server Topology
• Plan Data Warehouse Server Hardware

Lab 3: Design a Data Warehouse Logical Schema

• Identify Business Processes and Dimensions
• Design Dimension Models and Data Warehouse Tables

Lab 4: Design a Data Warehouse Physical Implementation

• Design File Storage
• Design Warehouse Data Structures

Lab 5: Design an ETL Solution

• Prepare for ETL Design
• Create Source-to-Target Documentation
• Use SSIS to load a Partitioned Fact Table

Lab 6: Compare Analytical Data Models

• Create a Multidimensional Data Model
• Create a Tabular Data Model

Lab 7: Design Dimensions and Hierarchies

• Create Dimensions in a Multidimensional Data Model
• Create Dimensions in a Tabular Data Model

Lab 8: Enhance Analytical Data Models

• Enhance a Multidimensional Data Model
• Enhance a Tabular Data Model

Lab 9: Plan a BI Delivery Solution

• Assess Reporting Requirements

Lab 10: Design a Reporting Services Solution

• Design a Reporting Environment
• Create Reusable Report Item
• Create Reports

Lab 11: Design an Excel Reporting Solution

• Plan Excel Features
• Analyze a Cube with Excel
• Create a PowerPivot Workbook
• Use Power View in Excel

Lab 12: Implement a SharePoint Server BI Solution

• Create a SharePoint Server Site for BI
• Configure Reporting Services in SharePoint Server
• Configure PowerPivot for SharePoint

Lab 13: Implement a PerformancePoint Service

Lab 14: Monitoring and Optimizing a BI Solution

• Monitoring and Optimizing a Data Warehouse, Analysis Services, and Reporting Services

Lab 15: Operating a BI Solution

• Configure SQL Server Integration Services (SSIS)
• Automate Data Warehouse Load Tasks
• Test Automated Operations

IT professionals such as BI architects or database professionals who need to design BI solutions and are responsible for the overall design of the BI infrastructure, including how it relates to other data systems