Designing Business Intelligence Solutions with Microsoft SQL Server 2012



In this course, you will learn how to design, install, and maintain business intelligence (BI) infrastructures, including data warehouse, an extract, transform, and load (ETL), Analysis Services, Reporting Services, and SharePoint Server.

This course incorporates material from the Official Microsoft Learning Product M20467B: Designing Business Intelligence Solutions for Microsoft SQL Server 2012.

Audience 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 Prerequisites
  • Basic understanding of dimensional modeling (star schema) for data warehouses
  • Ability to create integration services packages that include control flows and data flows
  • Ability to create a basic multidimensional cube with Analysis Services
  • Ability to create a basic tabular model with PowerPivot and Analysis Services
  • Ability to create Reporting Services reports with Report Designer
  • Ability to implement authentication and permissions with the SQL Server database engine, Analysis Services, and Reporting Services
  • Familiarity with SharePoint Server and Microsoft Office applications, particularly Excel or the skills and knowledge from the following courses:
Learning Objectives
  • 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
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