Implementing a SQL Data Warehouse (M20767)



In this course, you will learn how to implement a data warehouse platform to support a business intelligence (BI) solution. You will discover how to create a data warehouse, how to implement extract, transform, and load (ETL) with SQL Server Integration Services (SSIS), and how to validate and cleanse data with Data Quality Services (DQS) and Master Data Services.

This course uses Microsoft SQL Server 2016 and incorporates material from the Official Microsoft Learning Product 20767: Implementing a SQL Data Warehouse.


  • Database professionals who need to fulfill a BI developer role focused on hands-on work by creating BI solutions, including data warehouse implementation, ETL and data cleansing
  • Database professionals responsible for implementing a data warehouse, developing SSIS packages for data ETL, enforcing data integrity using Microsoft Data Services and cleansing data using Data Quality Services


  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Working knowledge of relational databases.
  • Some experience with database design

Learning Objectives

  • Overview of data warehousing
  • Considerations for building a data warehouse
  • Design and implementation for a data warehouse
  • Columnstore indexes
  • Azure SQL Data Warehouse
  • ETL with SSIS
  • Implement control flow in an SSIS package
  • Debug and troubleshoot SSIS packages
  • Incremental ETL and modified data extraction
  • Microsoft DQS
  • Master Data Services concepts and implementation
  • Extend SSIS
  • Deploy and configure SSIS packages
  • BI tools with Azure SQL Data Warehouse

1.�Introduction to Data Warehousing

  • Overview of Data Warehousing
  • Considerations for a Data Warehouse Solution

2.�Planning Data Warehouse Infrastructure

  • Considerations for Building a Data Warehouse
  • Data Warehouse Reference Architectures and Appliances

3.�Designing and Implementing a Data Warehouse

  • Designing dimension tables
  • Designing fact tables
  • Physical Design for a Data Warehouse

4.�Columnstore Indexes

  • Introduction to Columnstore Indexes
  • Creating Columnstore Indexes
  • Working with Columnstore Indexes

5.�Implementing an Azure SQL Data Warehouse

  • Advantages of Azure SQL Data Warehouse
  • Implementing an Azure SQL Data Warehouse
  • Developing an Azure SQL Data Warehouse
  • Migrating to an Azure SQ Data Warehouse

6.�Creating an ETL Solution

  • ETL with SSIS
  • Exploring Source Data
  • Implementing Data Flow

7.�Implementing Control Flow in an SSIS Package

  • Introduction to Control Flow
  • Creating Dynamic Packages
  • Using Containers

8.�Debugging and Troubleshooting SSIS Packages

  • Debugging an SSIS Package
  • Logging SSIS Package Events
  • Implementing an Event Handler
  • Handling Errors in Data Flow

9.�Implementing a Data Extraction Solution

  • Introduction to Incremental ETL
  • Extracting Modified Data
  • Loading Modified Data
  • Temporal Tables

10.�Enforcing Data Quality

  • Introduction to Data Quality
  • Using Data Quality Services to Cleanse Data
  • Using Data Quality Services to Match Data

11.�Using Master Data Services

  • Master Data Services Concepts
  • Implementing a Master Data Services Model
  • Hierarchies and Collections
  • Creating a Master Data Hub

12.�Extending SQL Server Integration Services (SSIS)

  • Using Custom Components in SSIS
  • Using Scripting in SSIS

13.�Deploying and Configuring SSIS Packages

  • Overview of SSIS Deployment
  • Deploying SSIS Projects
  • Planning SSIS Package Execution

14.�Consuming Data in a Data Warehouse

  • Business Intelligence
  • Reporting
  • Data Analysis
  • Analyzing Data with Azure SQL Data Warehouse