20467 Designing Business Intelligence Solutions with Microsoft SQL
This five-day instructor-led course teaches students how to design and implement a BI infrastructure. The course discusses design, installation. the course discusses design, installation, and maintenance of a BI platform. This course maps to skills and knowledge measured by Microsoft Exam 70-467, and in conjunction with on-the-job experience, can help prepare you for the exam.
This course is intended for database professionals who need who plan, implement, and manage database solutions. Primary responsibilities include: Planning and implementing database infrastructure.
Planning and implementing consolidation strategies.
- Implementing SQL Server in on-premises, cloud, and hybrid IT scenarios.
Planning and implementing high availability solutions.
Planning and implementing disaster recovery solutions.
Before attending this course, students must have:
- A basic understanding of dimensional modeling (star schema) for data warehouses.
- 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.
- 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
Module 1: Planning a BI Solution
This modules discusses how to plan the components of a BI solution.
- Elements of a BI Solution
- Planning a BI Project
- The Microsoft BI Platform
Lab : Planning a BI Solution
- Exercise 1: Scoping a BI Solution.
- Exercise 2: Reviewing a Suggested Solution
Module 2: Planning a BI Infrastructure
This modules explains how to plan an appropriate BI infrastructure for a given set of BI services.
- Planning a Data Warehouse Infrastructure
- Planning an ETL Infrastructure
- Planning Analysis Services Infrastructure
- Planning Reporting Services Infrastructure
Lab : Planning a BI Infrastructure
- Exercise 1: Determining Required Servers
- Exercise 2: Determining Data Warehouse Server Hardware Requirements
- Exercise 3: Reviewing a Suggested Solution
Module 3: Designing a Data Warehouse
This module explain how to design a data warehouse for a given set of user requirements.
- Designing a Logical Schema
- Designing a Physical Schema
Lab : Designing a Data Warehouse Logical Schema
- Exercise 1: Identifying Required Dimensions
- Exercise 2: Identifying Measures and Hierarchies
- Exercise 3: Designing a Data Warehouse Schema
- Exercise 4: Reviewing a Suggested Solution
Module 4: Designing an ETL Solution
This module explains how to design an ETL solution for a data warehouse.
- Planning for Data Extraction
- Planning for Data Transformation
- Planning for Data Loading
- Planning for SSIS Package Deployment
Lab : Designing an ETL Solution
- Exercise 1: Reviewing Data Sources
- Exercise 2: Creating Source-to-Target Maps
- Exercise 3: Planning ETL Processes
- Exercise 4: Reviewing a Suggested Solution
Module 5: Designing Analytical Data Models
This module explains how to design analytical data models for specific BI scenarios.
- Options for Analytical Models
- Data Model Design Considerations
Lab : Comparing Analytical Data Models
- Exercise 1: Creating a Multidimensional Model
- Exercise 2: Creating a Tabular Model
Module 6: Planning a BI Delivery Solution
This module explains how to choose an appropriate delivery solution for a given scenario.
- Considerations for BI Delivery
- Choosing a Delivery Approach
Lab : Planning a BI Delivery Solution
- Exercise 1: Assessing Reporting Requirements
Module 7: Designing a Reporting Services Solution
This module explains how to design a Reporting Services solution.
- Designing Reports
- Considerations for Report Execution
- Planning a Reporting Solution
Lab : Designing a Reporting Services Solution
- Exercise 1: Planning a Reporting Services Solution
- Exercise 2: Planning a Report Delivery Environment
- Exercise 3: Configuring Report Execution
Module 8: Designing an Excel-Based Reporting Solution
This module explains how to design a reporting solution that uses Excel.
- Using Excel for Data Reporting and Analysis
- PowerPivot in Excel
- Power View in Excel
Lab : Designing an Excel Reporting Solution
- Exercise 1: Planning Excel Features
- Exercise 2: Analyzing a Cube with Excel
- Exercise 3: Using PowerPivot
- Exercise 4: Using Power View
Module 9: Planning a SharePoint Server BI Solution
This module explains how to design a SharePoint 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
Lab : Planning a SharePoint Server BI Solution
- Exercise 1: Planning SharePoint Server Security
- Exercise 2: Configuring Reporting Services in SharePoint Server
- Exercise 3: Managing PowerPivot Data Refresh
- Exercise 4: Reviewing PowerPivot Activity
- Exercise 5 (Optional) Creating a PerformancePoint Dashboard
Module 10: Monitoring and Optimizing a BI Solution
This module explains how to monitor and optimize a BI solution.
- Overview of BI Monitoring
- Monitoring and Optimizing the Data Warehouse
- Monitoring and Optimizing Analysis Services
- Monitoring and Optimizing Reporting Services
Lab : Monitoring and Optimizing a BI Solution
- Exercise 1: Monitoring and Optimizing a Data Warehouse
- Exercise 2: Troubleshooting Analysis Services Queries
- Exercise 3: Monitoring and Optimizing Reporting Services
Module 11: Planning BI Operations
This module explains how to plan management and maintenance operations for a BI solution.
- Overview of BI Operations
- Automating ETL Tasks
- Post ETL Tasks
- Planning a Backup Strategy for a BI Solution
Lab : Planning BI Operations
- Exercise 1: Planning ETL and Processing Tasks
- Exercise 2: Planning a Backup Strategy